Re: [sqlite] SQLite version 3.8.6 coming soon
"big stone" wrote... hi Jose, The SQL request with a "in()" that is improved by 5x in the latest beta is of form : select * from a , b where a.field1 in (b.column1, b.column2, b.column3, b.column4, 'fixed value') Hi Big Stone, This is what I am running with an ATTACHed DB as client... BEGIN; INSERT OR REPLACE INTO LSOpenProjects SELECT * FROM client.LSOpenProjects as A WHERE id IN ( SELECT id from LSOpenProjects WHERE login != 'user1' AND Date != A.Date ); END; It has not shown any speed improvement, but maybe the slowdown is caused by the INSERT. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
On Wed, 6 Aug 2014 20:56:24 -0400 Richard Hipp wrote: > > http://www.perfectyourenglish.com/vocabulary/backward-backwards.htm > > > > Two countries divided by a common tongue. > > > Except, I speak Southern English, not British English. And I can > promise you that we southerners prefer to add the "s" to the end of > "backwards". I'm not sure PerfectYourEnglish necessarily has all > their facts straight. Oh, I'm sure you're right on both counts. You know what form you prefer, and jrandomsite.com shouldn't be assumed to be an authority. (When it comes to English usage in particular, there's a lot more pretended and presumptive authority on the web than actual.) I was just pointing out that the with-or-without-s is question (at least) regional, and extends to words other than backwards. You may recognize the lines Kiss today goodbye And point me toward tomorrow If ever you happen to be practicing that in a chorus, don't be too surprised if it takes more than a few tries before everyone understands there's no "s" (as written) in second line. You might could spend the better part of an hour on it. Don't ask me how I know. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
hi Jose, The SQL request with a "in()" that is improved by 5x in the latest beta is of form : select * from a , b where a.field1 in (b.column1, b.column2, b.column3, b.column4, 'fixed value') ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
FWIW, the Zumero test suite is fairly abusive and it passes all test cases with 3.8.6 beta. -- E On Thu, Aug 7, 2014 at 11:59 AM, Richard Hipp wrote: > On Thu, Aug 7, 2014 at 10:01 AM, E.Pasma wrote: > > > I have a case where a primary key index is no longer used where it was > > used before. > > > > Thank you for the test case! > > This problem should now be addressed on trunk and in the pre-release > snapshots. Please retry using the latest and let me know if you continue > to see problems or if you see anything new. Thanks. > > -- > 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] SQLite version 3.8.6 coming soon
On Thu, Aug 7, 2014 at 10:01 AM, E.Pasma wrote: > I have a case where a primary key index is no longer used where it was > used before. > Thank you for the test case! This problem should now be addressed on trunk and in the pre-release snapshots. Please retry using the latest and let me know if you continue to see problems or if you see anything new. Thanks. -- 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] SQLite version 3.8.6 coming soon
Richard Hipp wrote: > On Wed, Aug 6, 2014 at 8:33 PM, James K. Lowden > wrote: > >> On Wed, 6 Aug 2014 07:40:43 -0400 >> Richard Hipp wrote: >> >> > > >> > > PS: backward compatibility, no s, no? >> > > >> > >> > A google search shows that you see it both ways - with and without the >> > "s". I've always used the "s". >> >> http://www.perfectyourenglish.com/vocabulary/backward-backwards.htm >> >> Two countries divided by a common tongue. >> >> > Except, I speak Southern English, not British English. And I can promise > you that we southerners prefer to add the "s" to the end of "backwards". > I'm not sure PerfectYourEnglish necessarily has all their facts straight. Agreed. I speak North-Eastern English, and "backwards compatibility" sounds right to me. One shouldn't necessarily believe everything one reads on the internet (or, as it used to be, "in books"). -- Will ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
"Richard Hipp" wrote... 32-bit and 64-bit DLLs are the latest 3.8.6 beta are now available on the download page. http://www.sqlite.org/download.html All works great. No visible speed increase for WHERE .. IN () as reported by someone, but nonetheless, all is well. Thanks. On Tue, Aug 5, 2014 at 11:11 PM, jose isaias cabrera < jic...@cinops.xerox.com> wrote: "Richard Hipp" wrote... Version 3.8.6 was originally scheduled for September. But the change log ( http://www.sqlite.org/draft/releaselog/current.html) is getting rather long and there are a number of important bug fixes. So we might try to get 3.8.6 out the door sooner rather than later. Please test! Recent amalgamations are available on the download page ( http://www.sqlite.org/download.html). Please compile your applications use the latest tip of trunk and let us know if you encounter any problems. Please review the change log and speak up if you see anything amiss. The status board (http://www.sqlite.org/checklists/3080600/index) for the 3.8.6 release is now on-line. The usual rule applies: When the check-list goes all-green, we will cut the release. No check-list items have been marked off yet, but we might start marking them off later this week, or early next week. Your feedback is greatly appreciated. Are you planning in creating temporary binaries for Windows? I use the DLLs for our application. ___ 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] SQLite version 3.8.6 coming soon
Op 6 aug 2014, om 02:57 heeft Richard Hipp het volgende geschreven: Version 3.8.6 was originally scheduled for September. But the change log ( http://www.sqlite.org/draft/releaselog/current.html) is getting rather long and there are a number of important bug fixes. So we might try to get 3.8.6 out the door sooner rather than later. Please test! Recent amalgamations are available on the download page ( http://www.sqlite.org/download.html). Please compile your applications use the latest tip of trunk and let us know if you encounter any problems. Please review the change log and speak up if you see anything amiss. The status board (http://www.sqlite.org/checklists/3080600/index) for the 3.8.6 release is now on-line. The usual rule applies: When the check-list goes all-green, we will cut the release. No check-list items have been marked off yet, but we might start marking them off later this week, or early next week. Your feedback is greatly appreciated. -- D. Richard Hipp d...@sqlite.org Hello, I have a case where a primary key index is no longer used where it was used before. Below is a script to reproduce this and the ourput in the new and old SQLite versions respectively. Tthis is a minimal case, leaving out anything further makes the problem disappear. Thus it is strange that: - the problem starts occuring only wher GROUP BY is present - the problem occurs only when there are at least two joins - idem only when the join condition involves BETWEEN Hope that this contributes to the final release. Thanks, E.Pasma .version CREATE TABLE node (node INTEGER PRIMARY KEY, minchild INTEGER, maxchild INTEGER, status CHAR NOT NULL) ; explain query plan SELECT s2.status FROMnode s0 JOINnode s1 ON s1.node BETWEEN s0.minchild AND s0.maxchild JOINnode s2 ON s2.node BETWEEN s1.minchild AND s1.maxchild WHERE s0.node = 0 --root node GROUP BY s2.status ; SQLite 3.8.6 2014-08-06 14:36:53 1202e9771f6d699dbf5268368108a3527d06d799 0|0|0|SEARCH TABLE node AS s0 USING INTEGER PRIMARY KEY (rowid=?) 0|1|1|SCAN TABLE node AS s1 0|2|2|SEARCH TABLE node AS s2 USING INTEGER PRIMARY KEY (rowid>? AND rowid 0|0|0|USE TEMP B-TREE FOR GROUP BY SQLite 3.8.5 2014-06-02 11:26:33 9f18b303cd1bc5779d82669884f802c7889b4947 0|0|0|SEARCH TABLE node AS s0 USING INTEGER PRIMARY KEY (rowid=?) 0|1|1|SEARCH TABLE node AS s1 USING INTEGER PRIMARY KEY (rowid>? AND rowid0|2|2|SEARCH TABLE node AS s2 USING INTEGER PRIMARY KEY (rowid>? AND rowid 0|0|0|USE TEMP B-TREE FOR GROUP BY ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
ok, output comparison Size is different : - before28 488 559 - now with beta 28 711 111 ==> delta = 222 552 ... which is my number of ouput lines The change in Newline Output policy (from CR to CR+LF) did generate a suspens. WinMerge says output is identical otherwise. So my 5x improvement seems not the result of a bug. I'm eager to see the speed-up that will show-up in your more general tests, section 26. of http://www.sqlite.org/checklists/3080600/index Regards, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
On Wed, Aug 6, 2014 at 6:56 PM, Richard Hipp wrote: > On Wed, Aug 6, 2014 at 8:33 PM, James K. Lowden > wrote: > > > On Wed, 6 Aug 2014 07:40:43 -0400 > > Richard Hipp wrote: > > > > > > > > > > PS: backward compatibility, no s, no? > > > > > > > > > > A google search shows that you see it both ways - with and without the > > > "s". I've always used the "s". > > > > http://www.perfectyourenglish.com/vocabulary/backward-backwards.htm > > > > Two countries divided by a common tongue. > > > > > Except, I speak Southern English, not British English. And I can promise > you that we southerners prefer to add the "s" to the end of "backwards". > I'm not sure PerfectYourEnglish necessarily has all their facts straight. > While it is debatable as to whether Texas is part of the south, I too used backwards until an old retired school teacher, who was substituting one day, scared me out of using it anymore. -- Scott Robison ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
On Wed, Aug 6, 2014 at 8:33 PM, James K. Lowden wrote: > On Wed, 6 Aug 2014 07:40:43 -0400 > Richard Hipp wrote: > > > > > > > PS: backward compatibility, no s, no? > > > > > > > A google search shows that you see it both ways - with and without the > > "s". I've always used the "s". > > http://www.perfectyourenglish.com/vocabulary/backward-backwards.htm > > Two countries divided by a common tongue. > > Except, I speak Southern English, not British English. And I can promise you that we southerners prefer to add the "s" to the end of "backwards". I'm not sure PerfectYourEnglish necessarily has all their facts straight. -- 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] SQLite version 3.8.6 coming soon
On Wed, 6 Aug 2014 07:40:43 -0400 Richard Hipp wrote: > > > > PS: backward compatibility, no s, no? > > > > A google search shows that you see it both ways - with and without the > "s". I've always used the "s". http://www.perfectyourenglish.com/vocabulary/backward-backwards.htm Two countries divided by a common tongue. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
On Wed, Aug 6, 2014 at 5:35 PM, big stone wrote: > Hello Sqlite team, > > My specific need relies on a rather complex combination of "in", and with > the new beta I go from 50 seconds down to 10 seconds. > (a 5x improvement) > > ==> Is that sort of gain possible with the recent "in" omtimisation, Yes. > or shall look deeper if result is still right ? > Yes. That is the purpose of beta testing, after all. We may have overlooked something. -- 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] SQLite version 3.8.6 coming soon
Hello Sqlite team, My specific need relies on a rather complex combination of "in", and with the new beta I go from 50 seconds down to 10 seconds. (a 5x improvement) ==> Is that sort of gain possible with the recent "in" omtimisation, or shall look deeper if result is still right ? Regards, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
"Richard Hipp" wrote... 32-bit and 64-bit DLLs are the latest 3.8.6 beta are now available on the download page. http://www.sqlite.org/download.html Thank you very much, Dr. Hipp. josé On Tue, Aug 5, 2014 at 11:11 PM, jose isaias cabrera < jic...@cinops.xerox.com> wrote: "Richard Hipp" wrote... Version 3.8.6 was originally scheduled for September. But the change log ( http://www.sqlite.org/draft/releaselog/current.html) is getting rather long and there are a number of important bug fixes. So we might try to get 3.8.6 out the door sooner rather than later. Please test! Recent amalgamations are available on the download page ( http://www.sqlite.org/download.html). Please compile your applications use the latest tip of trunk and let us know if you encounter any problems. Please review the change log and speak up if you see anything amiss. The status board (http://www.sqlite.org/checklists/3080600/index) for the 3.8.6 release is now on-line. The usual rule applies: When the check-list goes all-green, we will cut the release. No check-list items have been marked off yet, but we might start marking them off later this week, or early next week. Your feedback is greatly appreciated. Are you planning in creating temporary binaries for Windows? I use the DLLs for our application. ___ 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] SQLite version 3.8.6 coming soon
32-bit and 64-bit DLLs are the latest 3.8.6 beta are now available on the download page. http://www.sqlite.org/download.html On Tue, Aug 5, 2014 at 11:11 PM, jose isaias cabrera < jic...@cinops.xerox.com> wrote: > > "Richard Hipp" wrote... > > > Version 3.8.6 was originally scheduled for September. But the change log >> ( >> http://www.sqlite.org/draft/releaselog/current.html) is getting rather >> long >> and there are a number of important bug fixes. So we might try to get >> 3.8.6 out the door sooner rather than later. >> >> Please test! Recent amalgamations are available on the download page ( >> http://www.sqlite.org/download.html). Please compile your applications >> use >> the latest tip of trunk and let us know if you encounter any problems. >> >> Please review the change log and speak up if you see anything amiss. >> >> The status board (http://www.sqlite.org/checklists/3080600/index) for the >> 3.8.6 release is now on-line. The usual rule applies: When the >> check-list >> goes all-green, we will cut the release. No check-list items have been >> marked off yet, but we might start marking them off later this week, or >> early next week. >> >> Your feedback is greatly appreciated. >> > > Are you planning in creating temporary binaries for Windows? I use the > DLLs for our application. > > ___ > 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] SQLite version 3.8.6 coming soon
On Wed, Aug 6, 2014 at 3:26 AM, Dominique Devienne wrote: > > PS: backward compatibility, no s, no? > A google search shows that you see it both ways - with and without the "s". I've always used the "s". -- 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] SQLite version 3.8.6 coming soon
On Wed, Aug 6, 2014 at 6:34 AM, Tony Papadimitriou wrote:
> Unless I'm misusing it, I believe the shell readfile() function reads a
> file as text, not binary. I haven't checked writefile() for the same
> problem on writing.
>
> This is how I use it, is this not correct?
>
> create table t1(a blob);
> insert into t1 values(readfile('binfile'));
> select * from t1;
>
> I always see text and I expect to see one of the following cases:
> 1. a hex sequence regardless of file type
> 2. text (if the file was text), or a hex sequence if it was binary.
>
> Instead, I see text when dealing with text files, and truncated text (up
> to the first zero) when dealing with binary.
>
> Am I misusing it?
>
To see hex output, use
SELECT quote(a) FROM t1;
The shell always prints BLOBs as just the bytes within the blob, not
translated to hex. It has always done this. For 14 years. Example:
CREATE TABLE t1(a BLOB);
INSERT INTO t1(a) VALUES(x'78797a7a79');
SELECT a FROM t1;
The above prints: xyzzy
--
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] SQLite version 3.8.6 coming soon
Unless I'm misusing it, I believe the shell readfile() function reads a file
as text, not binary. I haven't checked writefile() for the same problem on
writing.
This is how I use it, is this not correct?
create table t1(a blob);
insert into t1 values(readfile('binfile'));
select * from t1;
I always see text and I expect to see one of the following cases:
1. a hex sequence regardless of file type
2. text (if the file was text), or a hex sequence if it was binary.
Instead, I see text when dealing with text files, and truncated text (up to
the first zero) when dealing with binary.
Am I misusing it?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: 06 August 2014 01:58 > To: General Discussion of SQLite Database; sqlite-dev > Subject: [sqlite] SQLite version 3.8.6 coming soon > > Version 3.8.6 was originally scheduled for September. But the change log ( > http://www.sqlite.org/draft/releaselog/current.html) is getting rather long > and there are a number of important bug fixes. So we might try to get > 3.8.6 out the door sooner rather than later. > > Please test! Recent amalgamations are available on the download page ( > http://www.sqlite.org/download.html). Please compile your applications > use > the latest tip of trunk and let us know if you encounter any problems. > The amalgamation I've just downloaded (201408060029) doesn't appear to have the changes I did with Jan to make it compile under vxWorks. Regards Andy Ling ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
src/func.c: The comment on top refers to sqliteRegisterBuildinFunctions() but the actual call seems to have been renamed to sqliteRegisterGlobalFunctions() ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
On Wed, Aug 6, 2014 at 2:57 AM, Richard Hipp wrote: > > Please review the change log and speak up if you see anything amiss. The new doc mentioned by http://www.sqlite.org/draft/lang_expr.html#hexint may have a 1-char typo: However, for backwards compatibility, the "Ox" hexadecimal integer notation notice the "Ox" (capital letter o), instead of "0x" (zero digit). Confused me on the first read. Hope this helps. --DD PS: backward compatibility, no s, no? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
"Richard Hipp" wrote... Version 3.8.6 was originally scheduled for September. But the change log ( http://www.sqlite.org/draft/releaselog/current.html) is getting rather long and there are a number of important bug fixes. So we might try to get 3.8.6 out the door sooner rather than later. Please test! Recent amalgamations are available on the download page ( http://www.sqlite.org/download.html). Please compile your applications use the latest tip of trunk and let us know if you encounter any problems. Please review the change log and speak up if you see anything amiss. The status board (http://www.sqlite.org/checklists/3080600/index) for the 3.8.6 release is now on-line. The usual rule applies: When the check-list goes all-green, we will cut the release. No check-list items have been marked off yet, but we might start marking them off later this week, or early next week. Your feedback is greatly appreciated. Are you planning in creating temporary binaries for Windows? I use the DLLs for our application. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
