Re: [sqlite] Foreign Key errors
On Tue, May 6, 2014 at 11:17 PM, Richard Hipp d...@sqlite.org wrote: On Tue, May 6, 2014 at 5:12 PM, Peter Haworth p...@lcsql.com wrote: It seems that foreign key errors on columns where the foreign key definition has a constraint name don't include the constraint name in the error message. This is using sqlite version 3.8.3.1. Is this under the control of a compile switch or PRAGMA or am I stuck with the way it is? [...] It is theoretically possible to keep track of which constraints are failing so that the particular constraint can be identified in the error message. But that woudl require more memory and CPU cycles. I agree with Peter, Petite Abeille, and Stephan, I'd like to see which FOREIGN KEY constraint(s) fails, similarly to how one sees now which CHECK constraints fails. I'd even argue that anyone using FKs wants this behavior by default, when #pragma foreign_key is on, but yet another pragma to enable naming which FK failed would be fine too. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] group_concat(distinct) with empty strings
Hinrichsen, John wrote:
Are the results below expected?
sqlite SELECT (SELECT GROUP_CONCAT(DISTINCT COALESCE(a,'')) FROM z) IS NULL;
1
You don't need DISTINCT:
sqlite select typeof(group_concat(''));
null
The documentation says:
| The group_concat() function returns a string which is the
| concatenation of all non-NULL values of X.
So this is a bug.
(AFAICS groupConcatStep() does not bother to do anything for empty
strings, so those are handled as if they had been NULL.)
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LSM Leaks memory
On 05/07/2014 04:51 AM, sql...@charles.derkarl.org wrote: I In a more complex program, lsm seems to leak memory to no bounds, causing my application. Are bug reports against LSM even helpful? I think they are. Thanks for the report. Now fixed here: http://www.sqlite.org/src4/info/8a39847dafa3047ba5d6107f0032c6b39d0ef104 Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow when query max(col) on empty set?
On 2014年5月7日 GMT+08:00AM3:16:35, Clemens Ladisch clem...@ladisch.de wrote: Woody Wu wrote: The following query statement executed very slow, it took 15 secs on my ARM device, 1. select max(time) from mytable where time and id1 = k1 and id2 = n. However, if I replace k1with another value that can be found in the table and keep everything unchanged, like below, 2. select max(time) from mytable where where time 9 and id1 = k2 and id2 = n. This query run very well, it only took less than 1 second on the same system. Could anyone explain this to me? The database can help with the explanation. What is the output of EXPLAIN QUERY PLAN for both queries? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The 'explain query plan' gives same result for the first and the second query: 0|0|0|SEARCH TAB mp USING COVERING INDEX sqlite_auto_index_mp_1 (ntimereq?) (~1 rows) BTW: I dont understand what the (~1 rows) mean. -Woody Wu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WITHOUT ROWID option
What is the best way to know if a table has been created with the WITHOUT ROWID option? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID option
SELECT instr(upper(sql),'WITHOUT ROWID')1 FROM sqlite_master WHERE type='table' AND tbl_name='YourTableName' Returns 1 for tables made without rowid, 0 for the rest. On 2014/05/07 15:00, Marco Bambini wrote: What is the best way to know if a table has been created with the WITHOUT ROWID option? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ 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
Re: [sqlite] WITHOUT ROWID option
On 5/7/2014 9:40 AM, RSmith wrote: SELECT instr(upper(sql),'WITHOUT ROWID')1 FROM sqlite_master WHERE type='table' AND tbl_name='YourTableName' Returns 1 for tables made without rowid, 0 for the rest. CREATE TABLE t(x text default 'WITHOUT ROWID'); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID option
Apologies, my answer reads more like a How to than a What is the best way type answer, so to just elaborate on the brevity - SQLite stores no special pointer or memory or setting or even file value anywhere that can give you any clue apart from the actual words Without RowID which are found only in the Schema of the Table and mostly only at the end of it (barring added comments etc). Hence my quick-draw solution hereunder is not so much the best way as it is the only way. Further to this, it is conceivable that a comment within the table definition might contain the words WITHOUT and ROWID together, so a more robust solution would probably be to ensure it follows the final right-brace. Hope that answers the question more directly. On 2014/05/07 15:40, RSmith wrote: SELECT instr(upper(sql),'WITHOUT ROWID')1 FROM sqlite_master WHERE type='table' AND tbl_name='YourTableName' Returns 1 for tables made without rowid, 0 for the rest. On 2014/05/07 15:00, Marco Bambini wrote: What is the best way to know if a table has been created with the WITHOUT ROWID option? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID option
...or indeed this malicious-but-valid table-schema design would prove problematic. Other that might cause similar headaches are: CREATE TABLE t(x text // )WITHOUT ROWID; ); or CREATE TABLE t(x text); // )WITHOUT ROWID; etc. It would require a rather convoluted check to be very sure but if you do not expect maliciously designed table schemas, this should not be a problem - and if you do, I suggest checking in your code after getting the sql schema and doing some minor parsing. On 2014/05/07 15:51, Igor Tandetnik wrote: On 5/7/2014 9:40 AM, RSmith wrote: SELECT instr(upper(sql),'WITHOUT ROWID')1 FROM sqlite_master WHERE type='table' AND tbl_name='YourTableName' Returns 1 for tables made without rowid, 0 for the rest. CREATE TABLE t(x text default 'WITHOUT ROWID'); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID option
So, is there an official recommended way? or that check should require a manual sql parsing? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 15:51, Igor Tandetnik i...@tandetnik.org wrote: On 5/7/2014 9:40 AM, RSmith wrote: SELECT instr(upper(sql),'WITHOUT ROWID')1 FROM sqlite_master WHERE type='table' AND tbl_name='YourTableName' Returns 1 for tables made without rowid, 0 for the rest. CREATE TABLE t(x text default 'WITHOUT ROWID'); -- Igor Tandetnik ___ 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
Re: [sqlite] WITHOUT ROWID option
On Wed, May 7, 2014 at 9:00 AM, Marco Bambini ma...@sqlabs.net wrote: What is the best way to know if a table has been created with the WITHOUT ROWID option? (1) You could send SELECT rowid FROM table to sqlite3_prepare() and see if it returns an error. This might fail on a table like CREATE TABLE xyz(rowid TEXT PRIMARY KEY)WITHOUT ROWID. (2) Run both PRAGMA index_list(table) and SELECT name FROM sqlite_master WHERE tbl_name='table'. If the PRAGMA mentions an sqlite_autoindex_table_1 which is not mentioned by the SELECT, then you have a WITHOUT ROWID table. This approach is more complex, but never fails, afaik. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ 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
Re: [sqlite] Very slow when query max(col) on empty set?
On 7 May 2014, at 1:29pm, Woody Wu narkewo...@gmail.com wrote: The 'explain query plan' gives same result for the first and the second query: 0|0|0|SEARCH TAB mp USING COVERING INDEX sqlite_auto_index_mp_1 (ntimereq?) (~1 rows) BTW: I dont understand what the (~1 rows) mean. Literally about 1 row. This is SQLite's estimate of how many rows the 'mp' table has. You can let SQLite make a better estimate by using the 'ANALYZE' command. It doesn't matter for this particular SELECT but for complicated SELECT commands which involve searching more than one table, knowing how long each table is and how it's indexed can help SQLite choose a good search strategy. And now back to someone else who might be able to help you understand your original problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID option
Thanks a lot Richard, I really appreciate. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 16:31, Richard Hipp d...@sqlite.org wrote: On Wed, May 7, 2014 at 9:00 AM, Marco Bambini ma...@sqlabs.net wrote: What is the best way to know if a table has been created with the WITHOUT ROWID option? (1) You could send SELECT rowid FROM table to sqlite3_prepare() and see if it returns an error. This might fail on a table like CREATE TABLE xyz(rowid TEXT PRIMARY KEY)WITHOUT ROWID. (2) Run both PRAGMA index_list(table) and SELECT name FROM sqlite_master WHERE tbl_name='table'. If the PRAGMA mentions an sqlite_autoindex_table_1 which is not mentioned by the SELECT, then you have a WITHOUT ROWID table. This approach is more complex, but never fails, afaik. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID option
On 7 May 2014, at 3:31pm, Richard Hipp d...@sqlite.org wrote: (2) Run both PRAGMA index_list(table) and SELECT name FROM sqlite_master WHERE tbl_name='table'. If the PRAGMA mentions an sqlite_autoindex_table_1 which is not mentioned by the SELECT, then you have a WITHOUT ROWID table. This approach is more complex, but never fails, afaik. Might it be possible in SQLite4 to deduce this information from the output of PRAGMA table_info(table-name) somehow ? Perhaps the ROWID field of a table might have its own particular indication, and if you don't see any rows marked like that you could deduce that the table had no ROWID column. I'm sure there are better ways the dev team could think up. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID option
On Wed, May 7, 2014 at 4:57 PM, Simon Slavin slav...@bigfraud.org wrote:
somehow ? Perhaps the ROWID field of a table might have its own
particular indication, and if you don't see any rows marked like that you
could deduce that the table had no ROWID column. I'm sure there are better
ways the
This isn't efficient, but it should work without corner cases: (pseudocode):
function hasRowId(tablename) {
prepare SELECT 1 FROM tablename; // if this fails, tablename likely does
not exist. else...
prepare SELECT rowid FROM tablename; // if this fails, rowid missing
return true only if the second PREPARE succeeds.
}
--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do. -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a single file version of System.Data.SQLite?
Thank you for the suggestion. There are 2 issues: 1: I tried: Assembly SampleAssembly = Assembly.Load(System.Data.SQLite, Version=1.0.92.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139) The assembly loads, but I couldn't figure out how to use it. None of the SQLite classes are in SampleAssembly, just a handful of things that have nothing to do with SQLite. Please forgive my .NET ignorance; I don't have a lot of experience with it. 2: This is all for naught anyway because when I transfer my program to the network the assembly won't load at all (Unverifiable code failed policy check). I'm not the only one with this problem: http://www.codeproject.com/Questions/428056/Exception-Details-System-IO-FileLoadException-Unve So, it looks like I'm stuck using the GAC and having to install SQLite in every computer that runs my software. Thanks anyway... -Bill -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Tuesday, May 06, 2014 5:56 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Is there a single file version of System.Data.SQLite? Drago, William @ MWG - NARDAEAST wrote: Is there a way to use the System.Data.SQLite.dll mixed-mode assembly outside of the GAC? Or is there a 32 bit only single file version of SQLite? Sure, you should be able to load the mixed-mode assembly from an arbitrary location using the LoadFrom() method, as seen here: http://msdn.microsoft.com/en- us/library/1009fa28%28v=vs.110%29.aspx Alternatively, if the mixed-mode assembly is located in the same directory as the application binary itself, it may be possible to use the Load() method, as seen here: http://msdn.microsoft.com/en- us/library/ky3942xh%28v=vs.110%29.aspx -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Could not open registry key
All, I tried running the installer in sqlite-netFx20-binary-bundle-Win32-2005-1.0.92.0.zip and received the following error: Installer.exe: #047 @ 2014.05.07T16:29:59.4744965: TraceOps.ShowMessage: could not open registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\.NETFramework\v2.0.50727\AssemblyFoldersEx Anyone have any idea why this is happening? Thanks, -- Bill Drago Senior Engineer L3 Communications / Narda Microwave Easthttp://www.nardamicrowave.com/ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / william.dr...@l-3com.com CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] group_concat(distinct) with empty strings
http://www.sqlite.org/src/info/0deac8737545a020d344be96fff16660a7977ab8 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a single file version of System.Data.SQLite?
Drago, William @ MWG - NARDAEAST wrote: The assembly loads, but I couldn't figure out how to use it. Yeah, using types in an assembly via reflection is always a bit tricky. None of the SQLite classes are in SampleAssembly, just a handful of things that have nothing to do with SQLite. Please forgive my .NET ignorance; I don't have a lot of experience with it. The classes are there; however, you would need to look them up manually (e.g. via Type.GetType, etc). 2: This is all for naught anyway because when I transfer my program to the network the assembly won't load at all (Unverifiable code failed policy check). I'm not the only one with this problem: The solution #4 at the URL you included seems to be a method to work around that particular issue. So, it looks like I'm stuck using the GAC and having to install SQLite in every computer that runs my software. I think that using the native library pre-loading feature along with setting some of the supported environment variables (or setting them via the System.Data.SQLite.dll.config file) could also be made to work properly: https://system.data.sqlite.org/index.html/artifact?ci=trunkfilename=Doc/Ext ra/Provider/environment.html Largely, it depends on the specifics of your environment, which I'm not really familiar with. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could not open registry key
Drago, William @ MWG - NARDAEAST wrote: I tried running the installer in sqlite-netFx20-binary-bundle-Win32-2005-1.0.92.0.zip and received the following error: Installer.exe: #047 @ 2014.05.07T16:29:59.4744965: TraceOps.ShowMessage: could not open registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\.NETFramework\v2.0.50727\AssemblyFolde rsEx Anyone have any idea why this is happening? Yes, that tool requires elevated administrator rights. However, that tool was not really designed to be run manually. Typically, that tool is only called by the setup in order to install the System.Data.SQLite assemblies into the GAC and setup the design-time components for Visual Studio. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could not open registry key
You almost certainly need to be running with full admin permissions. If it's an MSI in the ZIP, just being logged into an admin account won't be sufficient on Windiws 7+ Open a command prompt by right-clicking and selecting Run as administrator and run the MSI from there. Sent from Samsung Galaxy Note Original message From: Drago, William @ MWG - NARDAEAST william.dr...@l-3com.com Date: 07/05/2014 17:45 (GMT+00:00) To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: [sqlite] Could not open registry key All, I tried running the installer in sqlite-netFx20-binary-bundle-Win32-2005-1.0.92.0.zip and received the following error: Installer.exe: #047 @ 2014.05.07T16:29:59.4744965: TraceOps.ShowMessage: could not open registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\.NETFramework\v2.0.50727\AssemblyFoldersEx Anyone have any idea why this is happening? Thanks, -- Bill Drago Senior Engineer L3 Communications / Narda Microwave Easthttp://www.nardamicrowave.com/ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / william.dr...@l-3com.com CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. ___ 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
Re: [sqlite] Very slow when query max(col) on empty set?
Simon Slavin wrote: On 7 May 2014, at 1:29pm, Woody Wu narkewo...@gmail.com wrote: The 'explain query plan' gives same result for the first and the second query: 0|0|0|SEARCH TAB mp USING COVERING INDEX sqlite_auto_index_mp_1 (ntimereq?) (~1 rows) BTW: I dont understand what the (~1 rows) mean. Literally about 1 row. This is SQLite's estimate of how many rows the 'mp' table has. Actually, the estimate of how many rows will match this search. SQLite assumes that such a comparison is useful for reducing the number of result rows. In this case, the estimate is quite wrong. You can let SQLite make a better estimate by using the 'ANALYZE' command. Yes. However, even if a better estimate were to show that this index is not very useful, there would exist, at the moment, no better index (with id1 or id2 as the first indexed column). Regards, Clemens ___ 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
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 4:51 PM, Hinrichsen, John jhinrich...@c10p.comwrote: $ 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 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? -- D. Richard Hipp d...@sqlite.org ___ 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 d...@sqlite.org 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] More LSM leak
This leak cursor leak can be consistently reproduced by my test program, but it doesn't occur every time you create and delete the cursor. The files you'll need are: http://www.derkarl.org/~charles/lsm/smaller.trace.bz2 http://www.derkarl.org/~charles/lsm/runlsm.cpp (The latter of which has changed since the last time I provided it to this list) $ cat smaller.trace | valgrind --leak-check=yes ~/a.out lsm ==24046== Memcheck, a memory error detector ==24046== Copyright (C) 2002-2011, and GNU GPL'd, by Julian Seward et al. ==24046== Using Valgrind-3.7.0 and LibVEX; rerun with -h for copyright info ==24046== Command: /home/charles/a.out lsm ==24046== ==24046== ==24046== HEAP SUMMARY: ==24046== in use at exit: 39,507 bytes in 24 blocks ==24046== total heap usage: 4,406,026 allocs, 4,406,002 frees, 149,648,738 bytes allocated ==24046== ==24046== 8 bytes in 1 blocks are definitely lost in loss record 1 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40AF95: lsmMallocZero (lsm_mem.c:50) ==24046==by 0x40B000: lsmMallocZeroRc (lsm_mem.c:69) ==24046==by 0x40F5DC: multiCursorAddAll.isra.21 (lsm_sorted.c:2387) ==24046==by 0x40F64C: multiCursorInit (lsm_sorted.c:2400) ==24046==by 0x411C6E: lsmMCursorNew (lsm_sorted.c:2495) ==24046==by 0x40A52F: lsm_csr_open (lsm_main.c:774) ==24046==by 0x405539: main (runlsm.cpp:255) ==24046== ==24046== 9 bytes in 1 blocks are possibly lost in loss record 2 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40B038: lsmReallocOrFree (lsm_mem.c:79) ==24046==by 0x40E501: sortedBlobSet (lsm_sorted.c:373) ==24046==by 0x40EB90: multiCursorCacheKey (lsm_sorted.c:2690) ==24046==by 0x4130E4: lsmMCursorSeek (lsm_sorted.c:3077) ==24046==by 0x405414: main (runlsm.cpp:242) ==24046== ==24046== 24 bytes in 1 blocks are possibly lost in loss record 3 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40AF95: lsmMallocZero (lsm_mem.c:50) ==24046==by 0x41A5AD: lsmPosixOsMutexNew (lsm_unix.c:654) ==24046==by 0x40D59D: lsmDbDatabaseConnect (lsm_shared.c:465) ==24046==by 0x409465: lsm_open (lsm_main.c:188) ==24046==by 0x404B63: main (runlsm.cpp:146) ==24046== ==24046== 24 bytes in 1 blocks are possibly lost in loss record 4 of 22 ==24046==at 0x4C28CCE: realloc (vg_replace_malloc.c:632) ==24046==by 0x41A64E: lsmPosixOsRealloc (lsm_unix.c:499) ==24046==by 0x41AEAE: lsmPosixOsShmMap (lsm_unix.c:400) ==24046==by 0x40BAF7: lsmShmCacheChunks (lsm_shared.c:1688) ==24046==by 0x416F5C: treeShmChunkRc (lsm_tree.c:318) ==24046==by 0x4185AD: lsmTreeInit (lsm_tree.c:1127) ==24046==by 0x449277: lsmLogRecover (lsm_log.c:972) ==24046==by 0x40D7AF: lsmDbDatabaseConnect (lsm_shared.c:365) ==24046==by 0x409465: lsm_open (lsm_main.c:188) ==24046==by 0x404B63: main (runlsm.cpp:146) ==24046== ==24046== 40 bytes in 1 blocks are possibly lost in loss record 5 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40AF95: lsmMallocZero (lsm_mem.c:50) ==24046==by 0x40B000: lsmMallocZeroRc (lsm_mem.c:69) ==24046==by 0x40E14E: multiCursorAllocTree (lsm_sorted.c:2680) ==24046==by 0x41308C: lsmMCursorSeek (lsm_sorted.c:3066) ==24046==by 0x405414: main (runlsm.cpp:242) ==24046== ==24046== 48 bytes in 2 blocks are possibly lost in loss record 6 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40AF95: lsmMallocZero (lsm_mem.c:50) ==24046==by 0x40B000: lsmMallocZeroRc (lsm_mem.c:69) ==24046==by 0x445142: lsmFsOpen (lsm_file.c:660) ==24046==by 0x40D67F: lsmDbDatabaseConnect (lsm_shared.c:506) ==24046==by 0x409465: lsm_open (lsm_main.c:188) ==24046==by 0x404B63: main (runlsm.cpp:146) ==24046== ==24046== 56 bytes in 1 blocks are possibly lost in loss record 7 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40B038: lsmReallocOrFree (lsm_mem.c:79) ==24046==by 0x40E501: sortedBlobSet (lsm_sorted.c:373) ==24046==by 0x413FB2: lsmMCursorValue (lsm_sorted.c:3309) ==24046==by 0x405008: main (runlsm.cpp:205) ==24046== ==24046== 64 bytes in 1 blocks are possibly lost in loss record 8 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x41A8E2: lsmPosixOsOpen (lsm_unix.c:81) ==24046==by 0x40D852: lsmDbDatabaseConnect (lsm_shared.c:412) ==24046==by 0x409465: lsm_open (lsm_main.c:188) ==24046==by 0x404B63: main (runlsm.cpp:146) ==24046==
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 6:58 PM, Hinrichsen, John jhinrich...@c10p.comwrote: On Wed, May 7, 2014 at 5:21 PM, Richard Hipp d...@sqlite.org 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
Re: [sqlite] Foreign Key errors
I'd vote for having this as a future enhancement under the control of a pragma or some other way of making it optional. Some of my tables have more than 1 foreign key and without the constraint name I have to write application code to pre-check for foreign key errors since I can't translate the error into a user friendly message. Thanks for considering this. Pete -- View this message in context: http://sqlite.1065341.n5.nabble.com/Foreign-Key-errors-tp75473p75513.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
