Databases
StackOverflow2013
Postsdbo
POStrange SQLite behavior: Not returning results on simple queries
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStrange SQLite behavior: Not returning results on simple queries
    primarykey
    data
    text
    <p>Ok, so I have a basic table called "ledger", it contains fields of various types, integers, varchar, etc.</p> <p>In my program, I used to use a query with no "from" predicate to collect all of the rows, which of course works fine. But... I changed my code to allow selecting one row at a time using "where acctno = x" (where X is the account number I want to select at the time).</p> <p>I thought this must be a bug in the client library for my programming language, so I tested it in the SQLite command-line client - and it still doesn't work!</p> <p>I am relatively new to SQLite, but I have been using Oracle, MS SQL Server, etc. for years and never seen this type of issue before.</p> <p>Other things I can tell you: * Queries using other integer fields also don't work * Queries on char fields work * Querying it as a string (with the account number on quotes) still doesn't work. (I thought maybe the numbers were stored as a string inadvertently). * Accessing rows by rowid works fine - which is why I can edit the database with GUI tools with no noticeable problem.</p> <p>Examples:</p> <p>Query with no WHERE (works fine):<br></p> <pre> 1|0|0|JPY|8|Paid-In Capital|C|X|0|X|0|0||||0|0|0| 0|0|0|JPY|11|Root Account|P|X|0|X|0|0|SYSTEM|20121209|150000|0|0|0| 3|0|0|JPY|13|Mitsubishi Bank Futsuu|A|X|0|X|0|0|SYSTEM|20121209|150000|0|0|0| 4|0|0|JPY|14|Japan Post Bank|A|X|0|X|0|0|SYSTEM|20121209|150000|0|0|0| ... </pre> <p>Query with WHERE clause: (no results)<br></p> <pre> sqlite> select * from ledger where acctno=1; sqlite> </pre> <p>putting quotes around the 1 above changes nothing.</p> <p>Interestingly enough, "select * from ledger where acctno > 1" returns results! However since it returns ALL results, it's not terrible useful.</p> <p>I'm sure someone will ask about the table structure, so here goes:<br></p> <pre> sqlite> .schema ledger CREATE TABLE "LEDGER" ( "ACCTNO" integer(10,0) NOT NULL, "drbal" integer(20,0) NOT NULL, "crbal" integer(20,0) NOT NULL, "CURRKEY" char(3,0) NOT NULL, "TEXTKEY" integer(10,0), "TEXT" VARCHAR(64,0), "ACCTYPECD" CHAR(1,0) NOT NULL, "ACCSTCD" CHAR(1,0), "PACCTNO" number(10,0) NOT NULL, "CATCD" number(10,0), "TRANSNO" number(10,0) NOT NULL, "extrefno" number(10,0), "UPDATEUSER" VARCHAR(32,0), "UPDATEDATE" text(8,0), "UPDATETIME" TEXT(6,0), "PAYEECD" number(10,0) NOT NULL, "drbal2" number(10,0) NOT NULL, "crbal2" number(10,0) NOT NULL, "delind" boolean, PRIMARY KEY("ACCTNO"), CONSTRAINT "fk_curr" FOREIGN KEY ("CURRKEY") REFERENCES "CURRENCY" ("CUR RKEY") ON DELETE RESTRICT ON UPDATE CASCADE ); </pre> <p>The strangest thing is that I have other similar tables where this works fine!</p> <p><pre> sqlite> select * from journalhdr where transno=13; 13|Test transaction ATM Withdrawel 20130213|20130223||20130223|| </p> <p>TransNo in that table is also integer (10,0) NOT NULL - this is what makes me thing it is something to do with the values.</p> <p>Another clue is that the sort order seems to be based on ascii, not numeric:</p> <pre>sqlite> select * from ledger order by acctno; 0|0|0|JPY|11|Root Account|P|X|0|X|0|0|SYSTEM|20121209|150000|0|0|0| 1|0|0|JPY|8|Paid-In Capital|C|X|0|X|0|0||||0|0|0| 10|0|0|USD|20|Sallie Mae|L|X|0|X|0|0|SYSTEM|20121209|153900|0|0|0| 21|0|0|USD|21|Skrill|A|X|0|X|0|0|SYSTEM|20121209|154000|0|0|0| 22|0|0|USD|22|AES|L|X|0|X|0|0|SYSTEM|20121209|154200|0|0|0| 23|0|0|JPY|23|Marui|L|X|0|X|0|0|SYSTEM|20121209|154400|0|0|0| 24|0|0|JPY|24|Amex JP|L|X|0|X|0|0|SYSTEM|20121209|154500|0|0|0| 3|0|0|JPY|13|Mitsubishi Bank Futsuu|A|X|0|X|0|0|SYSTEM|20121209|150000|0|0|0| </pre> <p>Of course the sort order on journalhdr (where the select works properly) is numeric.</p> <p>Solved! (sort-of) The data can be fixed like this:</p> sqlite> update ledger set acctno = 23 where rowid = 13; sqlite> select * from ledger where acctno = 25; 25|0|0|JPY|0|Test|L|X|0|X|0|0|SYSTEM|20130224|132500|0|0|0| </pre> <p>Still, if it was stored as strings, then that leave a few questions: 1. Why couldn't I select it as a string using the quotes? 2. How did it get stored as a string since it is a valid integer? 3. How would you go about detecting this problem normally besides noticing bizzarre symptoms?</p> <p>Although the data would normally be entered by my program, some of it was created by hand using Navicat, so I assume the problem must lie there.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNoah
    1. USNoah
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COSolved! (sort-of)
      singulars
      1. POStrange SQLite behavior: Not returning results on simple queries
      1. USNoah
    2. CODue to SQLite's [type affinity](http://www.sqlite.org/datatype3.html#affinity), it should not be possible to have strings that look like a number in an `integer` column.
      singulars
      1. POStrange SQLite behavior: Not returning results on simple queries
      1. USCL.
    3. CO@CL.: but it is possible - and my example demonstrates it.
      singulars
      1. POStrange SQLite behavior: Not returning results on simple queries
      1. USmvp
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload