Re: [sqlite] Order of columns in group by statement affects query performance
AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints in the virtual table description). If you have an index that covers the GROUP BY clause in field order, then aggregate functions need store only the current value; if not, then you need an ephemeral table to hold the aggregate values. In more detail: IF you have an index that covers the GROUP BY clause in field order, then retrieving rows in index order guarantees that all rows belonging to the same group will be retrieved in one block AND makes the output rows come out sorted too. This allows SQLite to keep current aggregate values in registers. IF you do not have an index that covers the GROUP BY clause, then rows will be retrieved in some deterministic order other than by group. You need to retrieve and update the current aggregate values for the group each row is in. This forces SQLite to keep current aggregate values in an ephemeral table. IF you have an index that covers the GROUP BY clause in any other order, then you still have the guarantee that all rows belonging to the same group will be retrieved together, but the result rows will be ordered in index order and not GROUP BY order. This would probably require code paths presumably shared by GROUP BY and ORDER BY processing to be split. -Ursprüngliche Nachricht- Von: James K. Lowden [mailto:jklow...@schemamania.org] Gesendet: Donnerstag, 25. April 2013 01:55 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Order of columns in group by statement affects query performance On Wed, 24 Apr 2013 17:46:00 +0100 Simon Slavin slav...@bigfraud.org wrote: On 24 Apr 2013, at 5:14pm, Igor Tandetnik i...@tandetnik.org wrote: Note though that the query doesn't have an ORDER BY clause. It doesn't request rows in any particular order. SQLite could, in principle, reorder columns in GROUP BY to take advantage of the index. I suppose the optimizer just happens to miss this particular opportunity. But the GROUP BY clause has an order: Query 1: SELECT A,B,count(*) from tableTest group by A,B Query 2: SELECT A,B,count(*) from tableTest group by B,A The order in which the columns appear syntactically in the GROUP BY clause is meaningless in SQL. Igor is correct that the query processor could use any index beginning with B,A or A,B, should it so choose. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Port SQLite to VxWorks 6.8
Hi All, First a very general question: Does anyone have experiences with porting SQLite to VxWorks? Preferably to Version 6.8 but not much too older versions could be also interesting for me. The documentation and the directives like #if OS_VXWORKS in the source suggest it would be possible easily and strait forward. Basically it should be a DKM but I tried also as an RTP which is mentioned in the documents but I was not successful either of them. Second question if I manage to port and build and load sqlite3.c I would like to test if it works. So to port shell.c would be also useful for this test. Does anybody ported shell.c? Or how did you test your sqlite3.c port on VxWorks? Thanks in advance. Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Select WHERE IN List ordering
Hi, I have the following query: SELECT id FROM pointslocation WHERE id IN (1,7,3,4,5,2,6) What 's returned is 1,2,3,4,5,6,7. How do I retain the order in the IN list? jb ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing in a blob
On 4/23/2013 3:17 PM, James K. Lowden wrote: On Tue, 23 Apr 2013 10:28:35 -0400 Richard Hipp drh-czdrofg0bjidnm+yrof...@public.gmane.org wrote: In summary: No, a bare SQLite blob does not provide file-system semantics. But you can write a wrapper library around SQLite that does provide file-system semantics for large blobs, and doing so would have many advantages and be a worth-while project, I think. A better and simpler answer IMO would be FUSE (http://fuse.sourceforge.net/) backed by SQLite. That gives you true file semantics, not that that's exactly a step forward. ;-) --jkl And on Windows one can use Dokan: http://dokan-dev.net/en/download/ Or this commercial offering: Callback file system (I'm not affiliated with them) http://www.eldos.com/cbfs/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: Support for aarch64
On 4/23/2013 3:22 PM, James K. Lowden wrote: On Tue, 23 Apr 2013 09:26:20 -0400 Richard Hipp drh-czdrofg0bjidnm+yrof...@public.gmane.org wrote: I really dislike changing autoconf versions since any autoconf upgrade results in a massive change in the generated configure script, which is annoying to audit before each release, and which results in exceedingly large and uninstructive diffs between successive versions. Why keep autoconf output in the source code repository? I'm not suggesting you should or shouldn't upgrade the version of autoconf you happen to be using. I find upgrading it to be a burden, too, even without the added burden of effects on the repository. But having maintained a smaller project for a similar number of years, I've never been tempted to archive configure scripts. --jkl Maybe it's simply to guard from generating different ./configure files on each machine depending on what autotools were installed (I have very basic knowledge there so I could be wrong). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting Sqlite to MQX Operating system
Hi Geoff I am currently working on porting of SQLITE to MQX RTOS in MPC5125 Platform. I am trying to rewrite the sqlite_os_init() for MQX and I have taken Win32 VFS implementation as the reference. But I am not able to map all of the Win32 file system calls with that of MQX functions. Please advise me on the list of file system functions that can be implemented as part of VFS for MQX. Regards, Arun GeoffW wrote: hello I thought I had better update this and confess to my sins. Good job no one is reading this thread as it is an embarassingly stupid mistake :rules: I cant believe I did this and then took so long to spot it. int sqlite3_os_init(void) { static sqlite3_vfs mqxVfs = { 1, /* iVersion */ 0, /* szOsFile -ARRRGGGH */ MAX_PATH, /* mxPathname */ etc I started creating the ported file from osWin.c, I had quite a struggle getting it to compile initially using CodeWarrior, so I was making several temporary hacks to allow it to compile, which I then revisited later to correct. I had replaced the sizeof(winFile) with a zero, and then forgot to change it to sizeof(mqxFile). This caused a fun crash down in the bowels of the paging code. Once I corrected this one liner it fixed the crash and sqlite is basically up and running now. I still have a fair bit of work left to implement some interface functions which are just dummy stubs at present, but I am making progress now. Regards Geoff -- View this message in context: http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p35333159.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
Re: [sqlite] Porting Sqlite to MQX Operating system
Hi Geoff I am currently working on porting of SQLITE to MQX RTOS in MPC5125 Platform. I am trying to rewrite the sqlite_os_init() for MQX and I have taken Win32 VFS implementation as the reference. But I am not able to map all of the Win32 file system calls with that of MQX functions. Please advise me on the list of file system functions that can be implemented as part of VFS for MQX. Regards, Arun GeoffW wrote: hello I thought I had better update this and confess to my sins. Good job no one is reading this thread as it is an embarassingly stupid mistake :rules: I cant believe I did this and then took so long to spot it. int sqlite3_os_init(void) { static sqlite3_vfs mqxVfs = { 1, /* iVersion */ 0, /* szOsFile -ARRRGGGH */ MAX_PATH, /* mxPathname */ etc I started creating the ported file from osWin.c, I had quite a struggle getting it to compile initially using CodeWarrior, so I was making several temporary hacks to allow it to compile, which I then revisited later to correct. I had replaced the sizeof(winFile) with a zero, and then forgot to change it to sizeof(mqxFile). This caused a fun crash down in the bowels of the paging code. Once I corrected this one liner it fixed the crash and sqlite is basically up and running now. I still have a fair bit of work left to implement some interface functions which are just dummy stubs at present, but I am making progress now. Regards Geoff -- View this message in context: http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p35333161.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
Re: [sqlite] Porting Sqlite to MQX Operating system
On Thu, Apr 25, 2013 at 7:38 AM, arun.pradeep arun.prad...@hcl.com wrote: Hi Geoff I am currently working on porting of SQLITE to MQX RTOS in MPC5125 Platform. I am trying to rewrite the sqlite_os_init() for MQX and I have taken Win32 VFS implementation as the reference. Have you considered using the (much simpler) test_demovfs.c VFS as a reference implementation? But I am not able to map all of the Win32 file system calls with that of MQX functions. Please advise me on the list of file system functions that can be implemented as part of VFS for MQX. Regards, Arun GeoffW wrote: hello I thought I had better update this and confess to my sins. Good job no one is reading this thread as it is an embarassingly stupid mistake :rules: I cant believe I did this and then took so long to spot it. int sqlite3_os_init(void) { static sqlite3_vfs mqxVfs = { 1, /* iVersion */ 0, /* szOsFile -ARRRGGGH */ MAX_PATH, /* mxPathname */ etc I started creating the ported file from osWin.c, I had quite a struggle getting it to compile initially using CodeWarrior, so I was making several temporary hacks to allow it to compile, which I then revisited later to correct. I had replaced the sizeof(winFile) with a zero, and then forgot to change it to sizeof(mqxFile). This caused a fun crash down in the bowels of the paging code. Once I corrected this one liner it fixed the crash and sqlite is basically up and running now. I still have a fair bit of work left to implement some interface functions which are just dummy stubs at present, but I am making progress now. Regards Geoff -- View this message in context: http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p35333159.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 -- 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] Select WHERE IN List ordering
On 24 Apr 2013, at 10:09pm, Gary Baranzini cona...@gmail.com wrote: I have the following query: SELECT id FROM pointslocation WHERE id IN (1,7,3,4,5,2,6) What 's returned is 1,2,3,4,5,6,7. How do I retain the order in the IN list? There's no simple way to do that in SQL. The numbers in the brackets is a set: an unordered collection. A number is either in the list or not, the list has no inherent order. If I wanted to select all rows from pointslocation in a particular order I'd probably make another column in pointslocation with the ordinals in. If I wanted to select just a few records in that order I'd make another TABLE (possibly using CREATE TEMPORARY TABLE) which had the ordinals in ... pl_id sel_order 1 1 7 2 3 3 4 4 5 5 2 6 6 7 Then I'd use a SELECT JOIN to pull up the pointslocation rows in that order. Possibly something like SELECT pointslocation.* FROM myTable JOIN pointslocation ON pointslocation.id=myTable.pl_id ORDER BY myTable.sel_order but I can't test the above here so don't assume it's definitely going to work. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select WHERE IN List ordering
On Wed, Apr 24, 2013 at 5:09 PM, Gary Baranzini cona...@gmail.com wrote: Hi, I have the following query: SELECT id FROM pointslocation WHERE id IN (1,7,3,4,5,2,6) What 's returned is 1,2,3,4,5,6,7. The output order for rows in a SELECT statement is undefined in SQL if you do not use an ORDER BY clause. The current SQLite implementation gives the order you show above, but this might change from one release to the next, so you should not depend on it. How do I retain the order in the IN list? You would need to provide an ORDER BY clause that somehow coerces the output into the order you desire. That will be tricky in this case. Probably you will need an auxiliary table. Perhaps something like the following: (Warning - untested code) CREATE TABLE sortorder(x,y); INSERT INTO sortorder VALUES(1,1),(7,2),(3,3),(4,4),(5,5),(2,6),(6,7); SELECT id FROM pointslocation JOIN sortorder ON id=x WHERE id IN (1,7,2,3,5,2,6) ORDER BY y; jb __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] Select WHERE IN List ordering
Gary Baranzini wrote: SELECT id FROM pointslocation WHERE id IN (1,7,3,4,5,2,6) How do I retain the order in the IN list? If you don't want to create a (temporary) table for the ordering, you can also do the mapping from id to the order in the query itself: SELECT id FROM pointslocation WHERE id IN (1,7,3,4,5,2,6) ORDER BY CASE id WHEN 1 THEN 1 WHEN 7 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 2 THEN 6 WHEN 6 THEN 7 END Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing in a blob
On Tue, Apr 23, 2013 at 6:17 PM, James K. Lowden jklow...@schemamania.orgwrote: On Tue, 23 Apr 2013 10:28:35 -0400 Richard Hipp d...@sqlite.org wrote: In summary: No, a bare SQLite blob does not provide file-system semantics. But you can write a wrapper library around SQLite that does provide file-system semantics for large blobs, and doing so would have many advantages and be a worth-while project, I think. A better and simpler answer IMO would be FUSE (http://fuse.sourceforge.net/) backed by SQLite. That gives you true file semantics, not that that's exactly a step forward. ;-) The Guardian Project has been doing some great work recently on libsqlfs, which provides POSIX style file access using an SQLite database. It can either be used as a standalone library or as a FUSE module: https://github.com/guardianproject/libsqlfs Cheers, Stephen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select WHERE IN List ordering
On 25 Apr 2013, at 1:58pm, Clemens Ladisch clem...@ladisch.de wrote: SELECT id FROM pointslocation WHERE id IN (1,7,3,4,5,2,6) ORDER BY CASE id WHEN 1 THEN 1 WHEN 7 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 2 THEN 6 WHEN 6 THEN 7 END Clever. Could generate that clause in code. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: Support for aarch64
On Wed, 24 Apr 2013 19:17:32 -0700 Dimiter 'malkia' Stanev mal...@gmail.com wrote: Why keep autoconf output in the source code repository? Maybe it's simply to guard from generating different ./configure files on each machine depending on what autotools were installed (I have very basic knowledge there so I could be wrong). The configure script is typically generated by a build machine that has autotools installed, and included in a distribution tarball. The user unpacks the tarball and runs the configure script. He doesn't need the autotools and he doesn't generate the scirpt. Anyone building from the Fossil repository should IMO be prepared to run autogen co., and to have other ancillary tools ready as well. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: Support for aarch64
On Thu, Apr 25, 2013 at 10:22 AM, James K. Lowden jklow...@schemamania.orgwrote: On Wed, 24 Apr 2013 19:17:32 -0700 Dimiter 'malkia' Stanev mal...@gmail.com wrote: Why keep autoconf output in the source code repository? Maybe it's simply to guard from generating different ./configure files on each machine depending on what autotools were installed (I have very basic knowledge there so I could be wrong). The configure script is typically generated by a build machine that has autotools installed, and included in a distribution tarball. The user unpacks the tarball and runs the configure script. He doesn't need the autotools and he doesn't generate the scirpt. Anyone building from the Fossil repository should IMO be prepared to run autogen co., and to have other ancillary tools ready as well. If that is so, then there is no harm in including ./configure from an older autoconf in the repo, since anybody who wants to build for an unusual platform that the older autoconf did not support can simply rerun autoconf. Right? -- 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] Order of columns in group by statement affects query performance
On Thu, 25 Apr 2013 10:29:34 +0200 Hick Gunter h...@scigames.at wrote: AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints in the virtual table description). That might be so, in some limited sense. It's obviously false in general because they mean different things and have different effects. If you have an index that covers the GROUP BY clause in field order, then aggregate functions need store only the current value; if not, then you need an ephemeral table to hold the aggregate values. Nonsense. The query parser sees GROUP BY A,B. The optimizer sees an index ordered B,A. By permuting the order of the columns in the GROUP BY clause, it finds a match for the index and uses it. Yes, the problem is O(n^2), where n is the number of columns in the GROUP BY, but n is always small; even 7 columns could be checked in less than 50 iterations. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] pager.c does not compile with SQLITE_OMIT_WAL
The current SQLite Fossil snapshot does not compile with SQLITE_OMIT_WAL defined. Reason: The Pager-pWal element is compiled out in pager.c here: http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=691-694 but still accessed here: http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=2876 http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=5243 http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=5333 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of columns in group by statement affects query performance
2013/4/25 James K. Lowden jklow...@schemamania.org Nonsense. The query parser sees GROUP BY A,B. The optimizer sees an index ordered B,A. By permuting the order of the columns in the GROUP BY clause, it finds a match for the index and uses it. Yes, the problem is O(n^2), where n is the number of columns in the GROUP BY, but n is always small; even 7 columns could be checked in less than 50 iterations. I believe its O(n!), but still doable for small n. I don't know the inner workings of the query optimizer but mabye instead of asking/check for a index of every permutation of the columns in the group by, it could just check if an index exists which covers all columns (even the sorting order doesn't matter). (the virtual table api needs an addition for that to work) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of columns in group by statement affects query performance
On Thu, Apr 25, 2013 at 10:29:34AM +0200, Hick Gunter scratched on the wall: AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints in the virtual table description). They're not the same clause, they don't do the same thing. Now, it is true that most database systems implement the first step of a GROUP BY by sorting the query using semantics that are similar to ORDER BY. That way all of the rows in a related group are next to each other, and they're easier to process. I assume SQLite does the same thing. It is, however, as they say, an implementation detail. IF you have an index that covers the GROUP BY clause in any other order, then you still have the guarantee that all rows belonging to the same group will be retrieved together, but the result rows will be ordered in index order and not GROUP BY order. Except there is no such thing as GROUP BY order. SQL Golden Rule: If there is no ORDER BY, the rows have no order. According to SQL, neither the groups, nor the rows within a group (as they are fed into aggregates) have a defined order. Any query that makes assumptions about the ordered result of a GROUP BY is broken. Use the out-of-order index. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of columns in group by statement affects query performance
On Thu, Apr 25, 2013 at 05:08:04PM +0200, Daniel Winter scratched on the wall: 2013/4/25 James K. Lowden jklow...@schemamania.org Nonsense. The query parser sees GROUP BY A,B. The optimizer sees an index ordered B,A. By permuting the order of the columns in the GROUP BY clause, it finds a match for the index and uses it. Yes, the problem is O(n^2), where n is the number of columns in the GROUP BY, but n is always small; even 7 columns could be checked in less than 50 iterations. I believe its O(n!), but still doable for small n. I don't know the inner workings of the query optimizer but mabye instead of asking/check for a index of every permutation of the columns in the group by, it could just check if an index exists which covers all columns (even the sorting order doesn't matter). (the virtual table api needs an addition for that to work) Permutations are O(N!), but that's not really what you want. Given a set of GROUP BY terms you want, generally, the index with the most terms in any initial order. You don't need a full match for the index to be a win. For example, GROUP BY A,B,C,D,E is likely to get a performance boost from an index on (A,D,B) and, *in general*, that should be a bigger win than an index on (B,C). Of course, since this is a query optimizer, there are always edge cases... For example, if there is an index over (E) that has 99% unique values, it is likely a better choice than (A,D,B)... it depends on the distribution of the index. Similarly, if any GROUP BY term maps to a unique index... boom, you're done. As with most things having to do with query optimization, the problem quickly explodes. On the other hand, SQLite must already have assumptions about index costs (with or without ANALYZE), so at least there's an existing set of weights and assumptions to work from. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of columns in group by statement affects query performance
On 25 Apr 2013, at 4:23pm, Jay A. Kreibich j...@kreibi.ch wrote: Except there is no such thing as GROUP BY order. SQL Golden Rule: If there is no ORDER BY, the rows have no order. According to SQL, neither the groups, nor the rows within a group (as they are fed into aggregates) have a defined order. Any query that makes assumptions about the ordered result of a GROUP BY is broken. Use the out-of-order index. GROUP BY on multiple columns means that the values in all those columns have to be the same for the rows to be included in the same GROUP. It says nothing about the order those groups should appear in in the results of the SELECT. Okay. So adding this to what went by upthread, I was wrong. Column order in the GROUP BY clause doesn't matter. Therefore the upthread comment that GROUP BY A,B means exactly the same as GROUP BY B,A is correct. So if there's an index which features those columns in any order it can be used, not matter what order the columns appear in in the GROUP BY clause. So it was perfectly reasonable for the OP to wonder why an index was used for one order but not another. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite4: consistent BUS ERROR on dropping index
Is this the right list for sqlite4 bugs? I have sqlite4 build fde11ff78c433d66 and a largish database. When I drop one of the indices on it through the sqlite4 command line tool, the tool dies with a BUS ERROR. For this database file it reproduces 100% of the time for me. I have the gdb backtrace below. I can share the database but it's quite large so I'd rather not upload it unless someone thinks they can actually make use of it. 1.7G database.db 347M database.db-log 2.0G database.db-shm $ gdb $(which sqlite4) --args sqlite4 databasedb/database.db drop index idx_followers_main; create index idx_followers_main on followers(hash, next_token, count) This GDB was configured as x86_64-apple-darwin...Reading symbols for shared libraries .. done (gdb) run Starting program: /Users/dking/sync/sqlite4/sqlite4 databasedb/database.db drop\ index\ idx_followers_main\;\ create\ index\ idx_followers_main\ on\ followers\(hash,\ next_token,\ count\) Reading symbols for shared libraries + done Program received signal EXC_BAD_ACCESS, Could not access memory. Reason: 10 at address: 0x0001f7231004 treeShmalloc (pDb=0x10088, bAlign=1052536, nByte=40, pRc=0x1f7231000) at lsm_tree.c:683 683 lsm_tree.c: No such file or directory. in lsm_tree.c (gdb) bt #0 treeShmalloc (pDb=0x10088, bAlign=1052536, nByte=40, pRc=0x1f7231000) at lsm_tree.c:683 #1 0x00010004b7fe in treeShmallocZero [inlined] () at lsm_tree.c:712 #2 0x00010004b7fe in newTreeNode [inlined] () at :723 #3 0x00010004b7fe in treeInsert (pDb=0x7fff5fbfe150, pCsr=0x100100f78, iLeftPtr=1606410576, iTreeKey=1606410576, iRightPtr=2147450820, iSlot=3) at lsm_tree.c:978 #4 0x00010004b741 in treeInsert (pDb=0x7fff5fbfe1b0, pCsr=0x7fff5fbfe260, iLeftPtr=1606410672, iTreeKey=1606410672, iRightPtr=2147450740, iSlot=3) at lsm_tree.c:939 #5 0x00010004b741 in treeInsert (pDb=0x7fff5fbfe210, pCsr=0x7fff5fbfe260, iLeftPtr=1606410768, iTreeKey=1606410768, iRightPtr=2147450688, iSlot=3) at lsm_tree.c:939 #6 0x00010004c115 in treeInsertLeaf [inlined] () at :1052 #7 0x00010004c115 in treeInsertEntry (pDb=0x7fff5fbfe3e0, flags=1606411232, pKey=0x7fff5fbfe3e0, nKey=1606411232, pVal=0x15, nVal=1606411232) at lsm_tree.c:1557 #8 0x00010003bb9f in doWriteOp (pDb=0x10088, bDeleteRange=1606411312, pKey=0x100102a98, nKey=16, pVal=0x7fff5fbfe430, nVal=1606411312) at lsm_main.c:696 #9 0x000100031914 in kvlsmDelete (pKVCursor=0x1001032a0) at kvlsm.c:297 #10 0x000100030474 in sqlite4KVCursorDelete (p=0x1001032a0) at kv.c:202 #11 0x000192a5 in sqlite4VdbeExec (p=0x10088) at vdbe.c:3933 #12 0x000100064b9a in sqlite4Step [inlined] () at :383 #13 0x000100064b9a in sqlite4_step (pStmt=0x1001039f0) at vdbeapi.c:444 #14 0x0001322d in shell_exec () at ctype.h:175 #15 0x00011c32 in main (argc=3, argv=0x7fff5fbfe918) at shell.c:2892 (gdb) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0
It seems that in version 1.0.81.0 and earlier the database file was released immediately after all SQLiteConnection objects were disposed even if some dependent SQLiteCommand and/or SQLiteDataReader objects were not yet disposed or closed. However starting version 1.0.82.0 the file remains locked. Consider this code: var file = Path.GetTempFileName(); using (var connection = new SQLiteConnection(Data Source= + file)) { Console.WriteLine(connection.GetType().Assembly.FullName); connection.Open(); var command = connection.CreateCommand(); command.CommandText = CREATE TABLE t(a); command.ExecuteNonQuery(); } // the following line succeeds in v1.0.81.0 and earlier but // FAILS on v1.0.82.0 and later with file still in use error File.Delete(file); Was this change made by design? Thanks, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0
As of release 1.0.82.0, the object disposal semantics were changed to keep the underlying connection around until all associated System.Data.SQLite objects have been properly disposed. Levi Haskell wrote: var file = Path.GetTempFileName(); using (var connection = new SQLiteConnection(Data Source= + file)) { Console.WriteLine(connection.GetType().Assembly.FullName); connection.Open(); var command = connection.CreateCommand(); command.CommandText = CREATE TABLE t(a); command.ExecuteNonQuery(); } // the following line succeeds in v1.0.81.0 and earlier but // FAILS on v1.0.82.0 and later with file still in use error File.Delete(file); In the above example, adding command.Dispose(); just prior to the end of the using block should allow the file to be deleted. Was this change made by design? Yes. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Programming API vs console
Simon, On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin slav...@bigfraud.org wrote: On 25 Apr 2013, at 3:28am, Igor Korot ikoro...@gmail.com wrote: Changed. No difference at all. Record is still does not show up. My guess is that you are opening different files in the shell and your app. This is usually caused by a default file path not being what you think it is. Use one program to insert a new row in the table. Use a simple SELECT in both programs to see if you can retrieve this new row it. Nope. It is one file. Besides the next time I am running the application at start I am reading this table. Record is not present. Thank you. Simon. ___ 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] Programming API vs console
Am 2013-04-25 21:25, schrieb Igor Korot: Simon, On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin slav...@bigfraud.org wrote: On 25 Apr 2013, at 3:28am, Igor Korot ikoro...@gmail.com wrote: Changed. No difference at all. Record is still does not show up. My guess is that you are opening different files in the shell and your app. This is usually caused by a default file path not being what you think it is. Use one program to insert a new row in the table. Use a simple SELECT in both programs to see if you can retrieve this new row it. Nope. It is one file. Besides the next time I am running the application at start I am reading this table. Record is not present. Is this by chance on Windows 7, and do you probably Run in /Program Files/ ? Marcus Thank you. Simon. ___ 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] Programming API vs console
Hi, On Thu, Apr 25, 2013 at 12:31 PM, mgr...@medcom-online.de wrote: Am 2013-04-25 21:25, schrieb Igor Korot: Simon, On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin slav...@bigfraud.org wrote: On 25 Apr 2013, at 3:28am, Igor Korot ikoro...@gmail.com wrote: Changed. No difference at all. Record is still does not show up. My guess is that you are opening different files in the shell and your app. This is usually caused by a default file path not being what you think it is. Use one program to insert a new row in the table. Use a simple SELECT in both programs to see if you can retrieve this new row it. Nope. It is one file. Besides the next time I am running the application at start I am reading this table. Record is not present. Is this by chance on Windows 7, and do you probably Run in /Program Files/ ? Yes, it is Windows 7 64-bit, but no I don't run from C:\Progra~ ;-) Thank you. Marcus Thank you. Simon. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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-**usershttp://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-**usershttp://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] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0
Thank you Joe, What was the motivation for this change (it seems to be quite inconvenient in my case)? - Levi - Original Message - From: sql...@mistachkin.com To: sqlite-users@sqlite.org Cc: Levi Haskell (BLOOMBERG/ 731 LEXIN) At: Apr 25 2013 15:07:54 As of release 1.0.82.0, the object disposal semantics were changed to keep the underlying connection around until all associated System.Data.SQLite objects have been properly disposed. Levi Haskell wrote: var file = Path.GetTempFileName(); using (var connection = new SQLiteConnection(Data Source= + file)) { Console.WriteLine(connection.GetType().Assembly.FullName); connection.Open(); var command = connection.CreateCommand(); command.CommandText = CREATE TABLE t(a); command.ExecuteNonQuery(); } // the following line succeeds in v1.0.81.0 and earlier but // FAILS on v1.0.82.0 and later with file still in use error File.Delete(file); In the above example, adding command.Dispose(); just prior to the end of the using block should allow the file to be deleted. Was this change made by design? Yes. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Programming API vs console
Am 2013-04-25 21:43, schrieb Igor Korot: Hi, On Thu, Apr 25, 2013 at 12:31 PM, mgr...@medcom-online.de wrote: Am 2013-04-25 21:25, schrieb Igor Korot: Simon, On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin slav...@bigfraud.org wrote: On 25 Apr 2013, at 3:28am, Igor Korot ikoro...@gmail.com wrote: Changed. No difference at all. Record is still does not show up. My guess is that you are opening different files in the shell and your app. This is usually caused by a default file path not being what you think it is. Use one program to insert a new row in the table. Use a simple SELECT in both programs to see if you can retrieve this new row it. Nope. It is one file. Besides the next time I am running the application at start I am reading this table. Record is not present. Is this by chance on Windows 7, and do you probably Run in /Program Files/ ? Yes, it is Windows 7 64-bit, but no I don't run from C:\Progra~ ;-) ..and also you don't store there nor in ProgramData? Well..Then at least it is not related to UAC or Virtualization.. ..:-) Thank you. Marcus Thank you. Simon. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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-**usershttp://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-**usershttp://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] Programming API vs console
Hi, On Thu, Apr 25, 2013 at 1:09 PM, mgr...@medcom-online.de wrote: Am 2013-04-25 21:43, schrieb Igor Korot: Hi, On Thu, Apr 25, 2013 at 12:31 PM, mgr...@medcom-online.de wrote: Am 2013-04-25 21:25, schrieb Igor Korot: Simon, On Wed, Apr 24, 2013 at 7:34 PM, Simon Slavin slav...@bigfraud.org wrote: On 25 Apr 2013, at 3:28am, Igor Korot ikoro...@gmail.com wrote: Changed. No difference at all. Record is still does not show up. My guess is that you are opening different files in the shell and your app. This is usually caused by a default file path not being what you think it is. Use one program to insert a new row in the table. Use a simple SELECT in both programs to see if you can retrieve this new row it. Nope. It is one file. Besides the next time I am running the application at start I am reading this table. Record is not present. Is this by chance on Windows 7, and do you probably Run in /Program Files/ ? Yes, it is Windows 7 64-bit, but no I don't run from C:\Progra~ ;-) ..and also you don't store there nor in ProgramData? Nope. Thank you. Well..Then at least it is not related to UAC or Virtualization.. ..:-) Thank you. Marcus Thank you. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-usershttp://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-usershttp://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-usershttp://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-usershttp://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-usershttp://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-usershttp://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-**usershttp://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-**usershttp://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] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0
Levi Haskell wrote: What was the motivation for this change (it seems to be quite inconvenient in my case)? The previous method used to deal with the non-deterministic finalization order imposed by the CLR did not work reliably in all circumstances, did not follow best-practices for the IDisposable interface, and relied heavily upon internal semantics of the native SQLite core library. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0
Is this what the INTEROP_LEGACY_CLOSE compile-time option refers to? *Use the legacy connection closing algorithm when built with the INTEROP_LEGACY_CLOSE compile-time option. Thanks, - Levi - Original Message - From: sql...@mistachkin.com To: sqlite-users@sqlite.org Cc: Levi Haskell (BLOOMBERG/ 731 LEXIN) At: Apr 25 2013 16:38:27 Levi Haskell wrote: gt; gt; What was the motivation for this change (it seems to be quite inconvenient gt; in my case)? gt; The previous method used to deal with the non-deterministic finalization order imposed by the CLR did not work reliably in all circumstances, did not follow best-practices for the IDisposable interface, and relied heavily upon internal semantics of the native SQLite core library. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0
Levi Haskell wrote: Is this what the INTEROP_LEGACY_CLOSE compile-time option refers to? Yes. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Programming API vs console
On Tue, Apr 23, 2013 at 8:47 PM, Igor Korot ikoro...@gmail.com wrote: query = wxString::Format(...); if( ( result = sqlite3_prepare_v2( m_handle, query, -1, stmt, 0 ) ) != SQLITE_OK ) It's been a while since I've worked with wxWidgets, but when I did, wxString didn't support an implicit conversion like you're using here. You need to do something like this for your sqlite_prepare_v2 call: sqlite3_prepare_v2(m_handle, (const char*)query.mb_str(wxConvUTF8), -1, stmt, 0); Though, I suppose if I'm right, this should have failed in some other way much sooner. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: Support for aarch64
On Thu, 25 Apr 2013 10:30:31 -0400 Richard Hipp d...@sqlite.org wrote: The configure script is typically generated by a build machine that has autotools installed, and included in a distribution tarball. The user unpacks the tarball and runs the configure script. He doesn't need the autotools and he doesn't generate the scirpt. Anyone building from the Fossil repository should IMO be prepared to run autogen co., and to have other ancillary tools ready as well. If that is so, then there is no harm in including ./configure from an older autoconf in the repo, since anybody who wants to build for an unusual platform that the older autoconf did not support can simply rerun autoconf. Right? Only if you find it convenient for your purposes. Your expressed concern was that upgrading autoconf brings ugly changes to the source code repository. I only asked why you bother to archive configure, because, if you don't, that particular problem goes away. On the FreeTDS project, we just archive every released tarball on the FTP server. Version 0.1 from 1998, a mere 26 KB, is still available just in case someone wants to run a featureless version on a Pentium II. I agree that upgrading autoconf is a nuisance; it's far from my favorite software. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users