On 7/25/16, Tim Gustafson <t...@ucsc.edu> wrote: > I'm using SQLite as the back-end database for Drupal. One of the > Drupal modules I use is called Webform, and Webform has a table that > looks like this: > > CREATE TABLE "webform_submissions" ( > sid INTEGER NULL DEFAULT '',
If the column is INTEGER, the default value shouldn't be a string. This is especially true of the INTEGER PRIMARY KEY column. > nid INTEGER NOT NULL DEFAULT '0', > uid INTEGER NOT NULL DEFAULT '0', > is_draft INTEGER NOT NULL DEFAULT '0', > submitted INTEGER NOT NULL DEFAULT '0', > remote_addr VARCHAR(128) NULL DEFAULT 'NULL', You do recognize that 'NULL' is a 4-character string, not a NULL, right? > serial INTEGER NOT NULL CHECK (serial>= 0), > completed INTEGER NOT NULL DEFAULT 0, > modified INTEGER NOT NULL DEFAULT 0, > highest_valid_page INTEGER NOT NULL DEFAULT 0, > PRIMARY KEY (sid) > ); > CREATE UNIQUE INDEX webform_submissions_sid_nid ON webform_submissions > (sid, nid); Any index whose left-most column is the PRIMARY KEY will be useless. > CREATE INDEX webform_submissions_nid_uid_sid ON webform_submissions > (nid, uid, sid); You should omit the INTEGER PRIMARY KEY from the end of an index. As a general rule, indexes should not include the PRIMARY KEY. (There are probably exceptions to this, but they are obscure, and so for now you should assume that there are no expections and just follow the rule.) > CREATE INDEX webform_submissions_nid_sid ON webform_submissions (nid, sid); After you have removed the PRIMARY KEY column from this index and from the previous index, this index will be a prefix of the previous. That means this index is redundant should be omitted. > > When querying against that table, I'm getting different results > depending on whether I quote numeric values or not: > > sqlite> SELECT min(SID) FROM webform_submissions webform_submissions > WHERE (nid = '4') AND (sid > '342'); It does appear that SQLite might be handling the sid>'342' term incorrectly here. I'll have to check into it closely and carefully parse out the rules to see when SQLite is suppose to be automatically converting the '342' into 342. If it is a bug, we'll fix it. In the meantime, if you will construct your indexes correctly (that is to say, without trying to index PRIMARY KEY columns) the problem will go away. Also, if you will use strings when you mean strings and numbers when you mean numbers, the problem will also go away then. Both of these situations, while technically allowed, are considered bad style and the error only occurs if you do them both at once, which explains why we have never noticed this before. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
