[sqlite] Fwd: odd schema resulting from creating a table out of a join with a subselect
Hello all, The schemas generated by the last two joins below are not what I was expecting. Could someone point me to documentation on how the schema generation works when using a subselect in a join? This was tested on 3.7.17, 3.9.2, and 3.11.0, and the behavior is consistent across these three sqlite versions. CREATE TABLE C(A); CREATE TABLE D(A); CREATE TABLE join_two_tables AS SELECT C.A FROM C INNER JOIN D ON D.A=C.A; .schema join_two_tables CREATE VIEW G AS SELECT * FROM D; CREATE TABLE join_table_with_view AS SELECT C.A FROM C INNER JOIN G ON G.A=C.A; .schema join_table_with_view CREATE TABLE join_table_with_subselect AS SELECT C.A FROM C INNER JOIN (SELECT * FROM D) D ON D.A=C.A; .schema join_table_with_subselect Output: CREATE TABLE join_two_tables(A); CREATE TABLE join_table_with_view("C.A"); CREATE TABLE join_table_with_subselect("C.A"); Best regards, John Hinrichsen -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction.
[sqlite] Fwd: odd schema resulting from creating a table out of a join with a subselect
Hello all, The schemas generated by the last two joins below are not what I was expecting. Could someone point me to documentation on how the schema generation works when using a subselect in a join? This was tested on 3.7.17, 3.9.2, and 3.11.0, and the behavior is consistent across these three sqlite versions. CREATE TABLE C(A); CREATE TABLE D(A); CREATE TABLE join_two_tables AS SELECT C.A FROM C INNER JOIN D ON D.A=C.A; .schema join_two_tables CREATE VIEW G AS SELECT * FROM D; CREATE TABLE join_table_with_view AS SELECT C.A FROM C INNER JOIN G ON G.A=C.A; .schema join_table_with_view CREATE TABLE join_table_with_subselect AS SELECT C.A FROM C INNER JOIN (SELECT * FROM D) D ON D.A=C.A; .schema join_table_with_subselect Output: CREATE TABLE join_two_tables(A); CREATE TABLE join_table_with_view("C.A"); CREATE TABLE join_table_with_subselect("C.A"); Best regards, John Hinrichsen -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction.
[sqlite] fts5 module does not build from the 3.8.11 release's source tarball
Update: I don't have a problem compiling under centos 7 (gcc 4.8.3), but with centos 6 (gcc 4.4.7) I do get this error. fts5_main.c:30: error: redefinition of typedef 'Fts5Global' fts5Int.h:83: note: previous declaration of 'Fts5Global' was here Unfortunately, I still have to support centos 6. On Mon, Jul 27, 2015 at 4:16 PM, Hinrichsen, John wrote: > This was the error I got: > > fts5_main.c:30: error: redefinition of typedef 'Fts5Global' > > > On Mon, Jul 27, 2015 at 4:00 PM, Dan Kennedy > wrote: > >> On 07/28/2015 02:55 AM, Hinrichsen, John wrote: >> >>> Hi, >>> >>> I was not able to get the fts5 module to build from the versioned source >>> tarball for this release ( >>> http://www.sqlite.org/2015/sqlite-src-3081100.zip >>> ). >>> >> >> Which step failed? >> >> >> >> >>> I was able to 'make fts5.c' following the instructions that reference the >>> "trunk" tarball. >>> >>> Regards, >>> John Hinrichsen >>> >>> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction.
[sqlite] fts5 module does not build from the 3.8.11 release's source tarball
This was the error I got: fts5_main.c:30: error: redefinition of typedef 'Fts5Global' On Mon, Jul 27, 2015 at 4:00 PM, Dan Kennedy wrote: > On 07/28/2015 02:55 AM, Hinrichsen, John wrote: > >> Hi, >> >> I was not able to get the fts5 module to build from the versioned source >> tarball for this release ( >> http://www.sqlite.org/2015/sqlite-src-3081100.zip >> ). >> > > Which step failed? > > > > >> I was able to 'make fts5.c' following the instructions that reference the >> "trunk" tarball. >> >> Regards, >> John Hinrichsen >> >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction.
[sqlite] fts5 module does not build from the 3.8.11 release's source tarball
Hi, I was not able to get the fts5 module to build from the versioned source tarball for this release (http://www.sqlite.org/2015/sqlite-src-3081100.zip ). I was able to 'make fts5.c' following the instructions that reference the "trunk" tarball. Regards, John Hinrichsen -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction.
Re: [sqlite] sqlite bug report
In this example, bad data is returned. There is no assert. valgrind does not complain either. Is there an ETA on when 3.8.7.2 will be released? On Thu, Nov 13, 2014 at 1:12 PM, Richard Hipp wrote: > This is https://www.sqlite.org/src/info/094d39a4c95ee4 which has been > fixed > in trunk and will be fixed in 3.8.7.2. > > On Thu, Nov 13, 2014 at 1:05 PM, Hinrichsen, John > wrote: > > > The following SQL produces an incorrect result with sqlite-3.8.7.1: > > > > CREATE TABLE A( > > symbol TEXT, > > type TEXT > > ); > > INSERT INTO A VALUES('ABCDEFG','chars'); > > INSERT INTO A VALUES('1234567890','num'); > > CREATE TABLE B( > > chars TEXT, > > num TEXT > > ); > > > > CREATE TABLE IF NOT EXISTS C AS > > SELECT A.symbol AS symbol,A.type, > > CASE A.type > > WHEN 'chars' THEN A.symbol > > WHEN 'num' THEN B.chars > > ELSE NULL > > END AS chars > > FROM A LEFT OUTER JOIN B ON A.type='num' AND B.num=A.symbol; > > > > SELECT * FROM C; > > > > with 3.8.7.1: > > > > sqlite> SELECT * FROM C; > > ABCDEFG|chars|ABCDEFG > > 1234567890|num|1234567 > > > > with 3.8.6: > > > > sqlite> SELECT * FROM C; > > ABCDEFG|chars|ABCDEFG > > 1234567890|num| > > > > -- > > > > This message contains confidential information and is intended only for > the > > individual named. If you are not the named addressee, you should not > > disseminate, distribute, alter or copy this e-mail. Please notify the > > sender immediately by e-mail if you have received this e-mail by mistake > > and delete this e-mail from your system. E-mail transmissions cannot be > > guaranteed to be secure or without error as information could be > > intercepted, corrupted, lost, destroyed, or arrive late or incomplete. > The > > sender, therefore, does not accept liability for any errors or omissions > in > > the contents of this message which arise during or as a result of e-mail > > transmission. If verification is required, please request a hard-copy > > version. This message is provided for information purposes and should not > > be construed as a solicitation or offer to buy or sell any securities or > > related financial instruments in any jurisdiction. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite bug report
The following SQL produces an incorrect result with sqlite-3.8.7.1: CREATE TABLE A( symbol TEXT, type TEXT ); INSERT INTO A VALUES('ABCDEFG','chars'); INSERT INTO A VALUES('1234567890','num'); CREATE TABLE B( chars TEXT, num TEXT ); CREATE TABLE IF NOT EXISTS C AS SELECT A.symbol AS symbol,A.type, CASE A.type WHEN 'chars' THEN A.symbol WHEN 'num' THEN B.chars ELSE NULL END AS chars FROM A LEFT OUTER JOIN B ON A.type='num' AND B.num=A.symbol; SELECT * FROM C; with 3.8.7.1: sqlite> SELECT * FROM C; ABCDEFG|chars|ABCDEFG 1234567890|num|1234567 with 3.8.6: sqlite> SELECT * FROM C; ABCDEFG|chars|ABCDEFG 1234567890|num| -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column affinity and the query planner's use of indices
According to the documentation, when creating a table "AS SELECT ...", the "affinity of comparison operands" rules are applied; what I am suggesting is that these rules be extended when used with "CREATE TABLE ... AS SELECT ..." http://www.sqlite.org/datatype3.html#expraff 3.2 Affinity Of Comparison Operands SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the affinity of the operands. Operand affinity is determined by the following rules: - The affinity of the right-hand operand of an IN or NOT IN operator is NONE if the operand is a list and is the same as the affinity of the result set expression if the operand is a SELECT. - An expression that is a simple reference to a column value has the same affinity as the column. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions for the purpose of determining affinity. - An expression of the form "CAST(*expr* AS *type*)" has an affinity that is the same as a column with a declared type of "*type*". - Otherwise, an expression has NONE affinity. On Tue, Jul 8, 2014 at 7:31 PM, Simon Slavin wrote: > > On 8 Jul 2014, at 11:11pm, Hinrichsen, John wrote: > > > This > > applies when creating a table using a SELECT where a column is the result > > of an expression (such as min, max, or sum) or within a CTE (in the > example > > provided, where the expression can obviously only produce integers.) > > > > [snip] > > > > If SQLite (optionally?) permitted us to avoid writing these casts, by > > automatically deducing the correct column affinity, it would correctly > make > > use of indices created, which would benefit everyone. > > Okay. So the problem is with sub-SELECT which produces values without > affinities. What you want is for MIN() and MAX() to have the same affinity > as the value they choose, and for SUM() to have an affinity of REAL. > Thanks for the explanation. > > So now I'm interested to know whether functions created with > sqlite3_create_function() return a value with an affinity or just a value. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column affinity and the query planner's use of indices
In most cases it should be possible for SQLite to perform type inference, i.e. deduce the type of an expression, rather than assign the column affinity to None for all columns that are produced by expressions. This applies when creating a table using a SELECT where a column is the result of an expression (such as min, max, or sum) or within a CTE (in the example provided, where the expression can obviously only produce integers.) Because expressions can be anywhere and everywhere, I have to perform a cast whenever a column is produced by an expression. The cast is not a one-off, do it once and forget about it kind of thing. In SQLite, a cast must wrap every expression if indices are to be used consistently. In my original post I note that the cast does work--but: -- it's redundant (the cast is a possible source of bugs if the column type is later changed) -- it's unintuitive (why can't the database figure out the right column affinity?) -- it's specific to SQLite (other DBs do assign the correct column type automatically) -- the code works anyway without the casts--which can be very misleading--but performs significantly worse, because it may stubbornly refuse to use an index that has been provided If SQLite (optionally?) permitted us to avoid writing these casts, by automatically deducing the correct column affinity, it would correctly make use of indices created, which would benefit everyone. It would also simplify the writing of queries, remove redundant code, and behave like SQL as understood by other DBs. An alternative might be to make SQLite consistently use indices regardless of column affinity. On Tue, Jul 8, 2014 at 1:47 PM, Simon Slavin wrote: > > On 8 Jul 2014, at 6:16pm, Hinrichsen, John wrote: > > >>> It would be more intuitive: why should aggregate functions like min(), > >>> max(), and sum() return column data stripped of the original column > >>> affinity? > > Can you talk us through the original problem again ? > > Are you talking about the affinity of the column x, or the affinity of the > result of these functions. And how is this a problem ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column affinity and the query planner's use of indices
Hi Richard, Your concern about breaking existing code makes a lot of sense. Would you consider a PRAGMA or compile-time directive to enable keeping column affinity where possible? I tested postgres, and found that columns produced by aggregate functions retain the column affinity of the input columns. Most users of sqlite have experience with other sql databases, so it's fair to assume that a lot of sql for sqlite is losing column affinity unintentionally in cases like this. On Tue, Jul 8, 2014 at 11:09 AM, Richard Hipp wrote: > On Tue, Jul 8, 2014 at 11:01 AM, Hinrichsen, John > wrote: > > > Hi, > > > > Would you consider changing the column affinity determination rules > > > Probably not. There are over a half million apps (literally) in > circulation that use the existing rules. Changing the rules would break > some fraction of those half-million apps, which would likely be very > annoying to the developers. > > > > > > so that > > expressions would, at least in easy-to-deduce cases, automatically assign > > the appropriate column affinity? > > > > Making this change would improve the performance of some queries (see my > > original email.) > > > > It would be more intuitive: why should aggregate functions like min(), > > max(), and sum() return column data stripped of the original column > > affinity? > > > > > > On Fri, May 23, 2014 at 2:21 PM, Hinrichsen, John > > wrote: > > > > > At table creation time, when column types are not declared explicitly, > or > > > are produced by an expression, column affinity defaults to NONE, with > the > > > result that indexes added afterwards often go unused in joins because > of > > a > > > column affinity mismatch. > > > > > > Adding casts around the expressions is an effective way to enforce > column > > > affinities, at the expense of redundant code. Column types that could > be > > > declared in just one place, or simply inferred, have to be repeatedly > > > re-declared via casts. I assume that this is a result of sqlite's > > manifest > > > typing. > > > > > > Would you consider changing the column affinity determination rules > such > > > that expressions could, at least in easy-to-deduce cases, automatically > > > assign the appropriate column affinity? > > > > > > My colleague Ivan, who diagnosed the issue, put the following repro > > > together to illustrate the problem. He uses the syntax "a JOIN b ON > b.x > > > IN (a.x)" and compares its query plan and performance to that of using > > > the standard join syntax. He also requests the query planner to > > > specifically use the index within the context of the normal join > syntax, > > > which the query planner rejects. > > > > > > $ sqlite3 > > > SQLite version 3.8.4.3 2014-04-03 16:53:12 > > > Enter ".help" for usage hints. > > > Connected to a transient in-memory database. > > > Use ".open FILENAME" to reopen on a persistent database. > > > sqlite> > > > sqlite> CREATE TEMP TABLE z AS > > >...> WITH RECURSIVE cnt(x) AS ( > > >...> VALUES(1) UNION ALL SELECT x+1 FROM cnt > > >...> WHERE x < 1e6 > > >...> ) SELECT x FROM cnt; > > > sqlite> > > > sqlite> CREATE TABLE a(x INTEGER NOT NULL PRIMARY KEY); > > > sqlite> INSERT INTO a SELECT x FROM temp.z; > > > sqlite> > > > sqlite> CREATE TABLE b AS SELECT x FROM temp.z; > > > sqlite> CREATE UNIQUE INDEX b_idx_1 ON b(x); > > > sqlite> > > > sqlite> ANALYZE; > > > sqlite> > > > sqlite> PRAGMA table_info(a); > > > cid nametypenotnull dflt_value pk > > > -- -- -- -- -- -- > > > 0 x INTEGER 1 1 > > > sqlite> PRAGMA table_info(b); > > > cid nametypenotnull dflt_value pk > > > -- -- -- -- -- -- > > > 0 x 0 0 > > > sqlite> PRAGMA index_info(b_idx_1); > > > seqno cid name > > > -- -- -- > > > 0 0 x > > > sqlite> EXPLAIN QUERY PLAN > > >...> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123; > > > selectidorde
Re: [sqlite] column affinity and the query planner's use of indices
Hi, Would you consider changing the column affinity determination rules so that expressions would, at least in easy-to-deduce cases, automatically assign the appropriate column affinity? Making this change would improve the performance of some queries (see my original email.) It would be more intuitive: why should aggregate functions like min(), max(), and sum() return column data stripped of the original column affinity? On Fri, May 23, 2014 at 2:21 PM, Hinrichsen, John wrote: > At table creation time, when column types are not declared explicitly, or > are produced by an expression, column affinity defaults to NONE, with the > result that indexes added afterwards often go unused in joins because of a > column affinity mismatch. > > Adding casts around the expressions is an effective way to enforce column > affinities, at the expense of redundant code. Column types that could be > declared in just one place, or simply inferred, have to be repeatedly > re-declared via casts. I assume that this is a result of sqlite's manifest > typing. > > Would you consider changing the column affinity determination rules such > that expressions could, at least in easy-to-deduce cases, automatically > assign the appropriate column affinity? > > My colleague Ivan, who diagnosed the issue, put the following repro > together to illustrate the problem. He uses the syntax "a JOIN b ON b.x > IN (a.x)" and compares its query plan and performance to that of using > the standard join syntax. He also requests the query planner to > specifically use the index within the context of the normal join syntax, > which the query planner rejects. > > $ sqlite3 > SQLite version 3.8.4.3 2014-04-03 16:53:12 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> > sqlite> CREATE TEMP TABLE z AS >...> WITH RECURSIVE cnt(x) AS ( >...> VALUES(1) UNION ALL SELECT x+1 FROM cnt >...> WHERE x < 1e6 >...> ) SELECT x FROM cnt; > sqlite> > sqlite> CREATE TABLE a(x INTEGER NOT NULL PRIMARY KEY); > sqlite> INSERT INTO a SELECT x FROM temp.z; > sqlite> > sqlite> CREATE TABLE b AS SELECT x FROM temp.z; > sqlite> CREATE UNIQUE INDEX b_idx_1 ON b(x); > sqlite> > sqlite> ANALYZE; > sqlite> > sqlite> PRAGMA table_info(a); > cid nametypenotnull dflt_value pk > -- -- -- -- -- -- > 0 x INTEGER 1 1 > sqlite> PRAGMA table_info(b); > cid nametypenotnull dflt_value pk > -- -- -- -- -- -- > 0 x 0 0 > sqlite> PRAGMA index_info(b_idx_1); > seqno cid name > -- -- -- > 0 0 x > sqlite> EXPLAIN QUERY PLAN >...> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123; > selectidorder fromdetail > -- -- -- > -- > 0 0 0 SEARCH TABLE a USING INTEGER PRIMARY > KEY (rowid=?) > 0 1 1 SCAN TABLE b > sqlite> EXPLAIN QUERY PLAN >...> SELECT * FROM a JOIN b INDEXED BY b_idx_1 ON a.x = b.x AND a.x = > 123; > Error: no query solution > > sqlite> EXPLAIN QUERY PLAN >...> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123; > selectidorder fromdetail > -- -- -- > -- > 0 0 0 SEARCH TABLE a USING INTEGER PRIMARY > KEY (rowid=?) > 0 1 1 SEARCH TABLE b USING COVERING INDEX > b_idx_1 (x=?) > 0 0 0 EXECUTE LIST SUBQUERY 1 > sqlite> .timer on > sqlite> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123; > x x > -- -- > 123 123 > Run Time: real 0.157 user 0.155976 sys 0.00 > > sqlite> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123; > x x > -- -- > 123 123 > Run Time: real 0.000 user 0.00 sys 0.00 > sqlite> > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.
Re: [sqlite] affinity critical problem with 3.8.5 - IN single value optimisation
This is a nasty bug; I do not see any follow-up regarding a fix. On Thu, Jun 26, 2014 at 9:17 AM, Guillaume Fougnies wrote: > Hi, > > It seems there's a problem with 3.8.5 and its affinity behavior. > It's quite critical. > > --- CUT --- > sqlite> CREATE TABLE T (v text); > sqlite> insert into T values('1'); > sqlite> insert into T values('2'); > sqlite> select v from T where v=1; > 1 > sqlite> select v from T where v='1'; > 1 > sqlite> select v from T where v IN(1); > sqlite> select v from T where v IN('1'); > 1 > sqlite> select v from T where v IN(1,2); > 1 > 2 > sqlite> select v from T where v IN('1','2'); > 1 > 2 > --- /CUT --- > > > It must be linked to this change: > > "Render expressions of the form "x IN (?)" (with a single value in the > list on the right-hand side of the IN operator) as if they where "x==?", > Similarly optimize "x NOT IN (?)"" > > Best regards, > -- > Guillaume FOUGNIES > Eulerian Technologies > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] column affinity and the query planner's use of indices
At table creation time, when column types are not declared explicitly, or are produced by an expression, column affinity defaults to NONE, with the result that indexes added afterwards often go unused in joins because of a column affinity mismatch. Adding casts around the expressions is an effective way to enforce column affinities, at the expense of redundant code. Column types that could be declared in just one place, or simply inferred, have to be repeatedly re-declared via casts. I assume that this is a result of sqlite's manifest typing. Would you consider changing the column affinity determination rules such that expressions could, at least in easy-to-deduce cases, automatically assign the appropriate column affinity? My colleague Ivan, who diagnosed the issue, put the following repro together to illustrate the problem. He uses the syntax "a JOIN b ON b.x IN (a.x)" and compares its query plan and performance to that of using the standard join syntax. He also requests the query planner to specifically use the index within the context of the normal join syntax, which the query planner rejects. $ sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> sqlite> CREATE TEMP TABLE z AS ...> WITH RECURSIVE cnt(x) AS ( ...> VALUES(1) UNION ALL SELECT x+1 FROM cnt ...> WHERE x < 1e6 ...> ) SELECT x FROM cnt; sqlite> sqlite> CREATE TABLE a(x INTEGER NOT NULL PRIMARY KEY); sqlite> INSERT INTO a SELECT x FROM temp.z; sqlite> sqlite> CREATE TABLE b AS SELECT x FROM temp.z; sqlite> CREATE UNIQUE INDEX b_idx_1 ON b(x); sqlite> sqlite> ANALYZE; sqlite> sqlite> PRAGMA table_info(a); cid nametypenotnull dflt_value pk -- -- -- -- -- -- 0 x INTEGER 1 1 sqlite> PRAGMA table_info(b); cid nametypenotnull dflt_value pk -- -- -- -- -- -- 0 x 0 0 sqlite> PRAGMA index_info(b_idx_1); seqno cid name -- -- -- 0 0 x sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123; selectidorder fromdetail -- -- -- -- 0 0 0 SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?) 0 1 1 SCAN TABLE b sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM a JOIN b INDEXED BY b_idx_1 ON a.x = b.x AND a.x = 123; Error: no query solution sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123; selectidorder fromdetail -- -- -- -- 0 0 0 SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?) 0 1 1 SEARCH TABLE b USING COVERING INDEX b_idx_1 (x=?) 0 0 0 EXECUTE LIST SUBQUERY 1 sqlite> .timer on sqlite> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123; x x -- -- 123 123 Run Time: real 0.157 user 0.155976 sys 0.00 sqlite> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123; x x -- -- 123 123 Run Time: real 0.000 user 0.00 sys 0.00 sqlite> -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition
These are all good points. Have you considered implementing hash joins for tables that join on columns that are not indexed? Typical hash joins (using the equality operator) can be performed in O(N) time without indexes. Because hash joins evaluate each row just once, they might also permit us to make calls to scalar functions more efficiently within the context of the join. On Wed, May 7, 2014 at 8:30 PM, Richard Hipp wrote: > On Wed, May 7, 2014 at 6:58 PM, Hinrichsen, John >wrote: > > > On Wed, May 7, 2014 at 5:21 PM, Richard Hipp wrote: > > > > > > > > Do you have a database file where the 3.8.4.3 query plan really is > > slower? > > > Can you please run ANALYZE on that database and send us the content of > > the > > > "sqlite_stat1" table? > > > > > > > > It is true that if we add the analyze, the query does use the automatic > > covering index. The analyze wasn't necessary with sqlite-3.7.17. > > > > The query planner in 3.7.17 was not nearly as clever as the 3.8.0+ query > planner. It got the right answer given wrong information by dumb luck. > See http://www.sqlite.org/queryplanner-ng.html and especially > http://www.sqlite.org/queryplanner-ng.html#howtofix for further > information. > > Also, it is generally considered good practice to create sufficient indices > to avoid having to use an automatic index. Using an automatic index will > make a two-way join O(NlogN). That's better than the O(N*N) that would > occur without the automatic index, but you could have O(logN) if an > appropriate persistent index is available. I know that there may arise > cases where the query is sufficiently infrequent and the size of the > necessary index is sufficiently large, that you may want to deliberately > make use of a transient automatic index. But those cases are rare. SQLite > comes with instrumentation (specifically the SQLITE_STMTSTATUS_AUTOINDEX > verb for sqlite3_stmt_status() - > http://www.sqlite.org/c3ref/stmt_status.html) that can be used to detect > when automatic indices are used and alert the developer through a back > channel to this fact so that she can fix the problem with an appropriate > CREATE INDEX. In other words, SQLite provides you with the tools to help > you detect and eliminate the use of automatic indices. Just saying > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition
On Wed, May 7, 2014 at 5:21 PM, Richard Hipp wrote: > > Do you have a database file where the 3.8.4.3 query plan really is slower? > Can you please run ANALYZE on that database and send us the content of the > "sqlite_stat1" table? > > It is true that if we add the analyze, the query does use the automatic covering index. The analyze wasn't necessary with sqlite-3.7.17. The following will demonstrate the performance regression: CREATE TABLE x AS WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 5 ) SELECT 1 AS a, n AS b FROM t; CREATE TABLE y AS SELECT b FROM x; CREATE INDEX ix ON x(a); SELECT COUNT(*) FROM (SELECT * FROM x INNER JOIN y ON x.b=y.b WHERE x.a=1); Although you can't execute the first statement under sqlite-3.7.17, you can save the db after creating it. -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition
$ sqlite3 SQLite version 3.7.17 2013-05-20 00:56:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE x AS SELECT 1 AS a, 1 AS b; sqlite> CREATE INDEX ix ON x (a); sqlite> CREATE TABLE y AS SELECT 1 AS b; sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b; 0|0|0|SCAN TABLE x (~100 rows) 0|1|1|SEARCH TABLE y USING AUTOMATIC COVERING INDEX (b=?) (~7 rows) sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b WHERE x.a = 1; 0|0|0|SEARCH TABLE x USING INDEX ix (a=?) (~10 rows) 0|1|1|SEARCH TABLE y USING AUTOMATIC COVERING INDEX (b=?) (~7 rows) sqlite> $ sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE x AS SELECT 1 AS a, 1 AS b; sqlite> CREATE INDEX ix ON x (a); sqlite> CREATE TABLE y AS SELECT 1 AS b; sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b; 0|0|0|SCAN TABLE x 0|1|1|SEARCH TABLE y USING AUTOMATIC COVERING INDEX (b=?) sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b WHERE x.a = 1; 0|0|0|SEARCH TABLE x USING INDEX ix (a=?) 0|1|1|SCAN TABLE y sqlite> -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] group_concat(distinct) with empty strings
Are the results below expected? $ sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE z AS SELECT NULL AS a; sqlite> SELECT (SELECT DISTINCT COALESCE(a,'') FROM z) IS NULL; 0 sqlite> SELECT (SELECT GROUP_CONCAT(DISTINCT COALESCE(a,'')) FROM z) IS NULL; 1 sqlite> SELECT (SELECT GROUP_CONCAT(DISTINCT COALESCE(a,' ')) FROM z) IS NULL; 0 sqlite> This problem looks similar to: http://sqlite.1065341.n5.nabble.com/group-concat-and-empty-strings-td62226.html -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] problem with INSERT after ALTER TABLE ... ADD COLUMN ... DEFAULT ... performed on source table
Default non-NULL values copied from a column that was added using "ALTER TABLE ... ADD COLUMN ... DEFAULT ..." are inserted into another table as NULLs when copied using "INSERT INTO ... SELECT * FROM ..." However, the same values are propagated correctly when "CREATE TABLE ... AS SELECT * FROM ..." is executed. See example below: $ sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .header on sqlite> .mode column sqlite> CREATE TABLE a(a); sqlite> INSERT INTO a VALUES(1); sqlite> sqlite> ALTER TABLE a ADD COLUMN b DEFAULT 2; sqlite> sqlite> CREATE TABLE b AS SELECT * FROM a; sqlite> INSERT INTO bSELECT * FROM a; sqlite> sqlite> SELECT * FROM a; a b -- -- 1 2 sqlite> SELECT * FROM b; a b -- -- 1 2 1 sqlite> -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1
That was a fast turn-around. Thank you for addressing this issue so quickly! -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1
"SELECT 'ABCDEF' AS A,'A' AS B,'1_JK' AS C,0.0 AS D " "UNION ALL SELECT 'ABCDEF','B','1_JK',0.0 " "UNION ALL SELECT 'ABCDEF','A','2_JK',0.0 " "UNION ALL SELECT 'ABCDEF','B','2_JK',0.0 " , NULL, NULL, NULL); assert(res == SQLITE_OK); res = sqlite3_exec(db, "CREATE VIRTUAL TABLE t1 USING test(" "'CREATE TABLE x(C1 TEXT, C2 TEXT, PRIMARY KEY (C1,C2))'," "'SELECT DISTINCT A,B FROM (SELECT A,B FROM t0 GROUP BY A,B) ORDER BY A,B')", NULL, NULL, NULL); assert(res == SQLITE_OK); res = sqlite3_exec(db, "CREATE VIRTUAL TABLE t2 USING test(" "'CREATE TABLE x(C1 TEXT, C2 TEXT, PRIMARY KEY (C0,C2))'," "'SELECT DISTINCT A,B FROM (SELECT A,B FROM t9 GROUP BY A,B) ORDER BY A,B')", NULL, NULL, NULL); assert(res == SQLITE_OK); printf("Joining virtual tables:\n"); execute_statement_with_result(db, "SELECT L.C1, L.C2 FROM t1 L JOIN t2 R ON L.C1=R.C1 AND L.C2=R.C2"); res = sqlite3_exec(db, "CREATE TABLE t3 AS SELECT * FROM t1", NULL, NULL, NULL); assert(res == SQLITE_OK); res = sqlite3_exec(db, "CREATE TABLE t4 AS SELECT * FROM t2", NULL, NULL, NULL); assert(res == SQLITE_OK); printf("Joining nonvirtual tables based on virtual tables:\n"); execute_statement_with_result(db, "SELECT L.C1, L.C2 FROM t3 L JOIN t4 R ON L.C1=R.C1 AND L.C2=R.C2"); sqlite3_close(db); s_db = db = NULL; return 0; } On Wed, Apr 2, 2014 at 7:53 PM, Donald Griggs wrote: > Attachments can't appear on this list. You can use a shared file service > and post a link, or for smallish amounts of text use something like > pastbin.com. > > > On Wed, Apr 2, 2014 at 6:42 PM, Andy Goth wrote: > > > On 4/2/2014 4:52 PM, Hinrichsen, John wrote: > > > >> sqlite 3.8.4.1 can return an incorrect result when joining two virtual > >> tables that are themselves based on underlying sqlite tables. > >> > >> This problem does not happen with sqlite 3.8.3.1 or earlier. > >> > >> Please see the attached repro. > >> > > > > Attachment appears to be missing. > > > > -- > > Andy Goth | > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1
sqlite 3.8.4.1 can return an incorrect result when joining two virtual tables that are themselves based on underlying sqlite tables. This problem does not happen with sqlite 3.8.3.1 or earlier. Please see the attached repro. -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users