[sqlite] Third test of json and index expressions, now it works
On Sun, 13 Sep 2015 10:46:21 +0200 Clemens Ladisch wrote: > James K. Lowden wrote: > > select a, r from ( > > SELECT a, random() as r FROM a > > ) as R > > WHERE r <> r; > > > > will yield zero rows, every time. > > $ sqlite3 > SQLite version 3.8.12 2015-09-12 19:50:58 > ... > sqlite> create table a(a); > sqlite> insert into a values (1),(2),(3); > sqlite> select a, r from (SELECT a, random() as r FROM a) as R > sqlite> WHERE r <> r; > 1|-909199267849538533 > 2|8366540922678358399 > 3|-6124149463908475628 Oh, Jimminy Crickets. That's Just Wrong. Thank you for the correction viz SQLite. I hope we can look forward to seeing it fixed. R.r doesn't refer, semantically, to the RANDOM function. It refers to the product of RANDOM. Apparently, deep in the bowels of SQLite, the query evaluation logic doesn't recognize -- and deal correctly with -- nondeterministic functions. Afaik there are only 2: DATE(now) and RANDOM. And they've both caused problems. Deterministic functions are idempotent. No matter how often you call, say, ABS(x), you get the same result for the same x, every time The execution evaluation can invoke ABS as often as it likes; the worst that will happen is a little inefficiency. When it comes to nondeterministic functions, though, it matters a great deal how often they're called, else you get nonsensical answers like R.r not being equal to itself. --jkl
[sqlite] Third test of json and index expressions, now it works
Hello ! Thank you for all feedbacks to this problem ! I originally gave a wrong/difficult example, the original one that other gave here use the equality operator instead of inequality. ___test-random.sql create table a(a); insert into a values (1),(2),(3); select 'direct select =' as ds, a, random() as r FROM a WHERE r = r; select 'indirect select =' as ds, a, r from (SELECT a, random() as r FROM a) as R WHERE r = r; select 'direct select <>' as ds, a, random() as r FROM a WHERE r <> r; select 'indirect select <>' as ds, a, r from (SELECT a, random() as r FROM a) as R WHERE r <> r; ___ ___sqlite3 < test-random.sql direct select <>|1|-192231578641646654 direct select <>|2|-4389394818887506060 direct select <>|3|-863257711086374254 indirect select <>|1|6957843808066646337 indirect select <>|2|-7871367914388137813 indirect select <>|3|3858147129752439584 ___ Given the explanations so far I still say that there is a bug in handling "columns aliases/wrapped queries" that should be addressed in sqlite. Cheers ! > Sun Sep 13 2015 10:46:21 am CEST CEST from "Clemens Ladisch" > Subject: Re: [sqlite] Third test of json and index >expressions, now it works > > James K. Lowden wrote: > >>select a, r from ( >> SELECT a, random() as r FROM a >> ) as R >> WHERE r <> r; >> >> will yield zero rows, every time. >> > $ sqlite3 > SQLite version 3.8.12 2015-09-12 19:50:58 > ... > sqlite> create table a(a); > sqlite> insert into a values (1),(2),(3); > sqlite> select a, r from (SELECT a, random() as r FROM a) as R WHERE r <> >r; > 1|-909199267849538533 > 2|8366540922678358399 > 3|-6124149463908475628 > > It happens to work only without a FROM clause (where SQLite uses > a coroutine instead of a plain table scan): > > sqlite> select r, r from (select random() as r from a); > -52277736304787193|5169235301562468199 > 563040897996752099|-5125387114614101287 > 8645211790850467701|5445986856138557805 > sqlite> select r, r from (select random() as r); > -283842963402875928|-283842963402875928 > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Third test of json and index expressions, now it works
Hello ! Thank you for the good example and explanation ! Cheers ! > Sun Sep 13 2015 2:06:14 am CEST CEST from "James K. Lowden" > Subject: Re: [sqlite] Third test of json and >index expressions, now it works > > On Wed, 09 Sep 2015 09:56:12 +0200 > "Domingo Alvarez Duarte" wrote: > > >>With your knowledge could you give your view about how evaluation of >> calculated/function columns should be done to have correct results. > > >>CREATE TABLE a(b); > > >>SELECT a, random() as r FROM a WHERE r <> r; >> > That's a syntax error, because there is no column a.r. > > Once you reorganize it to make it syntactically valid, the answer > becomes clear: > > select a, r from ( > SELECT a, random() as r FROM a > ) as R > WHERE r <> r; > > will yield zero rows, every time. Whatever value RANDOM produces goes > in the "r" column and any value, including "r", is always equal to > itself. > > --jkl > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Third test of json and index expressions, now it works
James K. Lowden wrote: > select a, r from ( > SELECT a, random() as r FROM a > ) as R > WHERE r <> r; > > will yield zero rows, every time. $ sqlite3 SQLite version 3.8.12 2015-09-12 19:50:58 ... sqlite> create table a(a); sqlite> insert into a values (1),(2),(3); sqlite> select a, r from (SELECT a, random() as r FROM a) as R WHERE r <> r; 1|-909199267849538533 2|8366540922678358399 3|-6124149463908475628 It happens to work only without a FROM clause (where SQLite uses a coroutine instead of a plain table scan): sqlite> select r, r from (select random() as r from a); -52277736304787193|5169235301562468199 563040897996752099|-5125387114614101287 8645211790850467701|5445986856138557805 sqlite> select r, r from (select random() as r); -283842963402875928|-283842963402875928 Regards, Clemens
[sqlite] Third test of json and index expressions, now it works
On Wed, 09 Sep 2015 09:56:12 +0200 "Domingo Alvarez Duarte" wrote: > With your knowledge could you give your view about how evaluation of > calculated/function columns should be done to have correct results. ... > CREATE TABLE a(b); ... > SELECT a, random() as r FROM a WHERE r <> r; That's a syntax error, because there is no column a.r. Once you reorganize it to make it syntactically valid, the answer becomes clear: select a, r from ( SELECT a, random() as r FROM a ) as R WHERE r <> r; will yield zero rows, every time. Whatever value RANDOM produces goes in the "r" column and any value, including "r", is always equal to itself. --jkl
[sqlite] Third test of json and index expressions, now it works
Hello ! This fix the example I gave on the other email it was "r <> r" but it's more easy to see the problem with "r = r". Nice explanation ! With your knowledge could you give your view about how evaluation of calculated/function columns should be done to have correct results. Like another example given on other thread: _ CREATE TABLE a(b); INSERT INTO a(b) VALUES(1),(2),(3); SELECT a, random() as r FROM a WHERE r = r; _
[sqlite] Third test of json and index expressions, now it works
On 9 Sep 2015, at 8:56am, Domingo Alvarez Duarte wrote: > SELECT a, random() as r FROM a WHERE r <> r; This is not valid SQL. You won't find any official SQL standard saying that 'r' can be referred to in the WHERE clause. You may find implementations of SQL where which will accept that command, and what those do is entirely up to whoever wrote them. If they have some sort of 'strict' option and you turn it on, they should instead produce an error message. Simon.
[sqlite] Third test of json and index expressions, now it works
Hello ! Nice explanation ! With your knowledge could you give your view about how evaluation of calculated/function columns should be done to have correct results. Like another example given on other thread: _ CREATE TABLE a(b); INSERT INTO a(b) VALUES(1),(2),(3); SELECT a, random() as r FROM a WHERE r <> r; _ > Wed Sep 09 2015 4:34:48 am CEST CEST from "James K. Lowden" > Subject: Re: [sqlite] Third test of json and >index expressions, now it works > > On Sat, 5 Sep 2015 09:07:11 -0700 > Darko Volaric wrote: > > >>I'm asking why the SQL standard restricts the use of aliases in this >> way and what the benefit of this restriction is. >> > Rationales in SQL are hard to come by. The language was promulgated by > a private firm, and the standard evolved under the aegis of what was, > for all intents and purposes, a private club. Rationales for most > warts in the language boil down to "because we say so". > > Nonetheless, there is a good reason! > > There are no aliases in SQL, Horatio. > > In every SQL database, column names are unique and unambiguous. If you > know the name of a table and a column, you've identified it. A query > can name *new* columns, but it can't create aliases for existing > ones. Consider, > > >>SELECT a+b AS x FROM t1 WHERE x=99; >> > Here, "x" is a new column, the product of the SELECT. By the rules of > SQL, it's *not* a macro for a+b, and it's not an alias. It's the name > of the column formed by computing a+b, a new column of a new table. > > Now consider, > > SELECT x from ( > SELECT a+b AS x FROM t1 > ) as TEETH_GNASHER > WHERE x=99; > > The outer query does not refer to t1, and thus not to "a" nor "b". It > sees only the new table, with its sole column, "x". And, although it's > a bit verbose, it also satisfies the sacred DRY criterion. > > Is that good? The "no aliases" rule has one thing going for it: it's > consistent. It's easy to understand and remember, and it reduces > opportunities for ambiguity. SQL is a logic-based language, and > ambiguity in logic is anathema because it's too easy to form > syntactically valid constructions that produce incorrect (and > unintended) results. > > Nearly every SQL programmer uses some other programming language as the > "real" language for his application. There's a temptation to make > informal, sometimes unwitting assumptions about the rules of SQL drawn > from that other language. The best way to understand any language > though, including SQL, is on its own terms. So double-quotes denote > identifiers, single-quotes strings, "||" contatenation, and there are > no aliases. It's not easy to slough off unwarranted associations with > other languages, but once that's done, SQL is impossible to > misconstrue. > > --jkl > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Third test of json and index expressions, now it works
On Sat, 5 Sep 2015 09:07:11 -0700 Darko Volaric wrote: > I'm asking why the SQL standard restricts the use of aliases in this > way and what the benefit of this restriction is. Rationales in SQL are hard to come by. The language was promulgated by a private firm, and the standard evolved under the aegis of what was, for all intents and purposes, a private club. Rationales for most warts in the language boil down to "because we say so". Nonetheless, there is a good reason! There are no aliases in SQL, Horatio. In every SQL database, column names are unique and unambiguous. If you know the name of a table and a column, you've identified it. A query can name *new* columns, but it can't create aliases for existing ones. Consider, > SELECT a+b AS x FROM t1 WHERE x=99; Here, "x" is a new column, the product of the SELECT. By the rules of SQL, it's *not* a macro for a+b, and it's not an alias. It's the name of the column formed by computing a+b, a new column of a new table. Now consider, SELECT x from ( SELECT a+b AS x FROM t1 ) as TEETH_GNASHER WHERE x=99; The outer query does not refer to t1, and thus not to "a" nor "b". It sees only the new table, with its sole column, "x". And, although it's a bit verbose, it also satisfies the sacred DRY criterion. Is that good? The "no aliases" rule has one thing going for it: it's consistent. It's easy to understand and remember, and it reduces opportunities for ambiguity. SQL is a logic-based language, and ambiguity in logic is anathema because it's too easy to form syntactically valid constructions that produce incorrect (and unintended) results. Nearly every SQL programmer uses some other programming language as the "real" language for his application. There's a temptation to make informal, sometimes unwitting assumptions about the rules of SQL drawn from that other language. The best way to understand any language though, including SQL, is on its own terms. So double-quotes denote identifiers, single-quotes strings, "||" contatenation, and there are no aliases. It's not easy to slough off unwarranted associations with other languages, but once that's done, SQL is impossible to misconstrue. --jkl
[sqlite] Third test of json and index expressions, now it works
op 06-09-2015 22:42 schreef Scott Robison op scott at casaderobison.com: > On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma wrote: > >> >> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven: >> >>> >>> One, the argument wasn't whether or not column expressions were called >>> multiple times. My understanding of the debate was that using aliases in >>> place of their definitions made code more understandable and more easily >>> maintained than gratuitous repetition of the definitions. >>> >>> Two, you used a different query. Looking at my query again (fleshed out >>> since I'm on a computer vs my phone): >>> >>> sqlite> create table c(a); >>> sqlite> insert into c values(1); >>> sqlite> insert into c values(2); >>> sqlite> insert into c values(3); >>> sqlite> insert into c values(4); >>> sqlite> select * from (select a as b from c) where b > 2; >>> 3 >>> 4 >>> sqlite> explain query plan select * from (select a as b from c) where b > >>> 2; >>> 0|0|0|SCAN TABLE c >>> >>> sqlite> select * from (select abs(a-a-a) as b from c) where b > 2; >>> 3 >>> 4 >>> sqlite> explain query plan select * from (select abs(a-a-a) as b from c) >>> where b > 2; >>> 0|0|0|SCAN TABLE c >>> >>> Clearly, this gives you an opportunity to replace definitions with >>> aliases, >>> since the aliases in the inner query become the column names of the outer >>> query. >>> >>> Your example doesn't do the same thing at all (ignoring the difference >>> between your condition of > 0 and mine of > 2). Your query is made of >>> scalar subqueries and you will never get more than a single row back. >>> >>> The following two queries are not the same: >>> >>> select 1 from (select (select a from c) as b) where b > 0; >>> >>> select 1 from (select a as b from c) where b > 0; >>> >>> In any case, this is a perfectly good option to query building that don't >>> require non-standard behavior (as far as I can tell) while still giving >>> the >>> benefit of DRY (don't repeat yourself) as someone noted previously. It's >>> more verbose. It may not be as intuitive. But it works. >>> >>> I am not a SQL guru. If I am wrong about my alternative invoking only >>> standard behavior, my apologies. >>> >>> >> Yes. the debate is about column aliases. >> Still the OP also wished to not call column expressions multiple times. >> And I had to change your query to make that appear in the query plan. >> But here is an example very close to yours where b is an alias for >> random(). >> I changed thea original where clause (b>2) to b <> b abd I hope it is >> obvious >> that the function is caled multiple times for each row. >> >> create table c(a); >> insert into c values(1); >> insert into c values(2); >> insert into c values(3); >> insert into c values(4); >> select * from (select random() as b from c) where b <> b; >> 232218896271007264 >> -1043354911054439855 >> 5116834959932449572 >> 7115658816317887453 >> > > Interesting query. So it would seem then that a temp table would be > required for something like this to avoid calling random three times per > row. I would have expected *this* version to return no rows. I guess this > is why we test software. :) > A temp table is not required. Another feature of subqueries (apart from sticking to standard use of aliases) is that you can make them behave as temp tables. Just by adding some "hint" for the optimizer such as LIMIT 99 or -1. select * from (select random() as b from c limit -1) where b <> b; Much easier than a temp table but not guaranteed to ever remain to work.
[sqlite] Third test of json and index expressions, now it works
Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven: > > One, the argument wasn't whether or not column expressions were called > multiple times. My understanding of the debate was that using > aliases in > place of their definitions made code more understandable and more > easily > maintained than gratuitous repetition of the definitions. > > Two, you used a different query. Looking at my query again (fleshed > out > since I'm on a computer vs my phone): > > sqlite> create table c(a); > sqlite> insert into c values(1); > sqlite> insert into c values(2); > sqlite> insert into c values(3); > sqlite> insert into c values(4); > sqlite> select * from (select a as b from c) where b > 2; > 3 > 4 > sqlite> explain query plan select * from (select a as b from c) > where b > 2; > 0|0|0|SCAN TABLE c > > sqlite> select * from (select abs(a-a-a) as b from c) where b > 2; > 3 > 4 > sqlite> explain query plan select * from (select abs(a-a-a) as b > from c) > where b > 2; > 0|0|0|SCAN TABLE c > > Clearly, this gives you an opportunity to replace definitions with > aliases, > since the aliases in the inner query become the column names of the > outer > query. > > Your example doesn't do the same thing at all (ignoring the difference > between your condition of > 0 and mine of > 2). Your query is made of > scalar subqueries and you will never get more than a single row back. > > The following two queries are not the same: > > select 1 from (select (select a from c) as b) where b > 0; > > select 1 from (select a as b from c) where b > 0; > > In any case, this is a perfectly good option to query building that > don't > require non-standard behavior (as far as I can tell) while still > giving the > benefit of DRY (don't repeat yourself) as someone noted previously. > It's > more verbose. It may not be as intuitive. But it works. > > I am not a SQL guru. If I am wrong about my alternative invoking only > standard behavior, my apologies. > Yes. the debate is about column aliases. Still the OP also wished to not call column expressions multiple times. And I had to change your query to make that appear in the query plan. But here is an example very close to yours where b is an alias for random(). I changed thea original where clause (b>2) to b <> b abd I hope it is obvious that the function is caled multiple times for each row. create table c(a); insert into c values(1); insert into c values(2); insert into c values(3); insert into c values(4); select * from (select random() as b from c) where b <> b; 232218896271007264 -1043354911054439855 5116834959932449572 7115658816317887453 By the way I really like your coding style and it had no impact on speed thanks to "subquery flattening" by the optimizer.
[sqlite] Third test of json and index expressions, now it works
On Sun, Sep 6, 2015 at 3:47 PM, pasma10 wrote: > op 06-09-2015 22:42 schreef Scott Robison op scott at casaderobison.com: > > > On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma wrote: > > > >> > >> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven: > >> > >>> > >>> One, the argument wasn't whether or not column expressions were called > >>> multiple times. My understanding of the debate was that using aliases > in > >>> place of their definitions made code more understandable and more > easily > >>> maintained than gratuitous repetition of the definitions. > >>> > >>> Two, you used a different query. Looking at my query again (fleshed out > >>> since I'm on a computer vs my phone): > >>> > >>> sqlite> create table c(a); > >>> sqlite> insert into c values(1); > >>> sqlite> insert into c values(2); > >>> sqlite> insert into c values(3); > >>> sqlite> insert into c values(4); > >>> sqlite> select * from (select a as b from c) where b > 2; > >>> 3 > >>> 4 > >>> sqlite> explain query plan select * from (select a as b from c) where > b > > >>> 2; > >>> 0|0|0|SCAN TABLE c > >>> > >>> sqlite> select * from (select abs(a-a-a) as b from c) where b > 2; > >>> 3 > >>> 4 > >>> sqlite> explain query plan select * from (select abs(a-a-a) as b from > c) > >>> where b > 2; > >>> 0|0|0|SCAN TABLE c > >>> > >>> Clearly, this gives you an opportunity to replace definitions with > >>> aliases, > >>> since the aliases in the inner query become the column names of the > outer > >>> query. > >>> > >>> Your example doesn't do the same thing at all (ignoring the difference > >>> between your condition of > 0 and mine of > 2). Your query is made of > >>> scalar subqueries and you will never get more than a single row back. > >>> > >>> The following two queries are not the same: > >>> > >>> select 1 from (select (select a from c) as b) where b > 0; > >>> > >>> select 1 from (select a as b from c) where b > 0; > >>> > >>> In any case, this is a perfectly good option to query building that > don't > >>> require non-standard behavior (as far as I can tell) while still giving > >>> the > >>> benefit of DRY (don't repeat yourself) as someone noted previously. > It's > >>> more verbose. It may not be as intuitive. But it works. > >>> > >>> I am not a SQL guru. If I am wrong about my alternative invoking only > >>> standard behavior, my apologies. > >>> > >>> > >> Yes. the debate is about column aliases. > >> Still the OP also wished to not call column expressions multiple times. > >> And I had to change your query to make that appear in the query plan. > >> But here is an example very close to yours where b is an alias for > >> random(). > >> I changed thea original where clause (b>2) to b <> b abd I hope it is > >> obvious > >> that the function is caled multiple times for each row. > >> > >> create table c(a); > >> insert into c values(1); > >> insert into c values(2); > >> insert into c values(3); > >> insert into c values(4); > >> select * from (select random() as b from c) where b <> b; > >> 232218896271007264 > >> -1043354911054439855 > >> 5116834959932449572 > >> 7115658816317887453 > >> > > > > Interesting query. So it would seem then that a temp table would be > > required for something like this to avoid calling random three times per > > row. I would have expected *this* version to return no rows. I guess this > > is why we test software. :) > > > A temp table is not required. Another feature of subqueries (apart from > sticking to standard use of aliases) is that you can make them behave as > temp tables. Just by adding some "hint" for the optimizer such as LIMIT > 99 or -1. > > select * from (select random() as b from c limit -1) where b <> b; > > Much easier than a temp table but not guaranteed to ever remain to work. Thanks for that pointer. -- Scott Robison
[sqlite] Third test of json and index expressions, now it works
On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma wrote: > > Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven: > >> >> One, the argument wasn't whether or not column expressions were called >> multiple times. My understanding of the debate was that using aliases in >> place of their definitions made code more understandable and more easily >> maintained than gratuitous repetition of the definitions. >> >> Two, you used a different query. Looking at my query again (fleshed out >> since I'm on a computer vs my phone): >> >> sqlite> create table c(a); >> sqlite> insert into c values(1); >> sqlite> insert into c values(2); >> sqlite> insert into c values(3); >> sqlite> insert into c values(4); >> sqlite> select * from (select a as b from c) where b > 2; >> 3 >> 4 >> sqlite> explain query plan select * from (select a as b from c) where b > >> 2; >> 0|0|0|SCAN TABLE c >> >> sqlite> select * from (select abs(a-a-a) as b from c) where b > 2; >> 3 >> 4 >> sqlite> explain query plan select * from (select abs(a-a-a) as b from c) >> where b > 2; >> 0|0|0|SCAN TABLE c >> >> Clearly, this gives you an opportunity to replace definitions with >> aliases, >> since the aliases in the inner query become the column names of the outer >> query. >> >> Your example doesn't do the same thing at all (ignoring the difference >> between your condition of > 0 and mine of > 2). Your query is made of >> scalar subqueries and you will never get more than a single row back. >> >> The following two queries are not the same: >> >> select 1 from (select (select a from c) as b) where b > 0; >> >> select 1 from (select a as b from c) where b > 0; >> >> In any case, this is a perfectly good option to query building that don't >> require non-standard behavior (as far as I can tell) while still giving >> the >> benefit of DRY (don't repeat yourself) as someone noted previously. It's >> more verbose. It may not be as intuitive. But it works. >> >> I am not a SQL guru. If I am wrong about my alternative invoking only >> standard behavior, my apologies. >> >> > Yes. the debate is about column aliases. > Still the OP also wished to not call column expressions multiple times. > And I had to change your query to make that appear in the query plan. > But here is an example very close to yours where b is an alias for > random(). > I changed thea original where clause (b>2) to b <> b abd I hope it is > obvious > that the function is caled multiple times for each row. > > create table c(a); > insert into c values(1); > insert into c values(2); > insert into c values(3); > insert into c values(4); > select * from (select random() as b from c) where b <> b; > 232218896271007264 > -1043354911054439855 > 5116834959932449572 > 7115658816317887453 > Interesting query. So it would seem then that a temp table would be required for something like this to avoid calling random three times per row. I would have expected *this* version to return no rows. I guess this is why we test software. :) -- Scott Robison
[sqlite] Third test of json and index expressions, now it works
Op 6 sep 2015, om 03:17 heeft Scott Robison het volgende geschreven: > > select * from (select a as b from c) where b is something > Still column expressions are called mutiple times. SQLite 3.8.11 2015-07-27 13:49:41 b8e92227a469de677a66da62e4361f099c0b79d0 create table c(a) ; explain query plan select 1 from (select (select a from c) as b) where b>0 ; 1|0|0|EXECUTE SCALAR SUBQUERY 2 2|0|0|SCAN TABLE c 1|0|0|EXECUTE SCALAR SUBQUERY 3 3|0|0|SCAN TABLE c 0|0|0|SCAN SUBQUERY 1
[sqlite] Third test of json and index expressions, now it works
On Sun, Sep 6, 2015 at 4:36 AM, E.Pasma wrote: > > Op 6 sep 2015, om 03:17 heeft Scott Robison het volgende geschreven: > >> >> select * from (select a as b from c) where b is something >> >> Still column expressions are called mutiple times. > > SQLite 3.8.11 2015-07-27 13:49:41 b8e92227a469de677a66da62e4361f099c0b79d0 > create table c(a) > ; > explain query plan > select 1 from (select (select a from c) as b) where b>0 > ; > 1|0|0|EXECUTE SCALAR SUBQUERY 2 > 2|0|0|SCAN TABLE c > 1|0|0|EXECUTE SCALAR SUBQUERY 3 > 3|0|0|SCAN TABLE c > 0|0|0|SCAN SUBQUERY 1 One, the argument wasn't whether or not column expressions were called multiple times. My understanding of the debate was that using aliases in place of their definitions made code more understandable and more easily maintained than gratuitous repetition of the definitions. Two, you used a different query. Looking at my query again (fleshed out since I'm on a computer vs my phone): sqlite> create table c(a); sqlite> insert into c values(1); sqlite> insert into c values(2); sqlite> insert into c values(3); sqlite> insert into c values(4); sqlite> select * from (select a as b from c) where b > 2; 3 4 sqlite> explain query plan select * from (select a as b from c) where b > 2; 0|0|0|SCAN TABLE c sqlite> select * from (select abs(a-a-a) as b from c) where b > 2; 3 4 sqlite> explain query plan select * from (select abs(a-a-a) as b from c) where b > 2; 0|0|0|SCAN TABLE c Clearly, this gives you an opportunity to replace definitions with aliases, since the aliases in the inner query become the column names of the outer query. Your example doesn't do the same thing at all (ignoring the difference between your condition of > 0 and mine of > 2). Your query is made of scalar subqueries and you will never get more than a single row back. The following two queries are not the same: select 1 from (select (select a from c) as b) where b > 0; select 1 from (select a as b from c) where b > 0; In any case, this is a perfectly good option to query building that don't require non-standard behavior (as far as I can tell) while still giving the benefit of DRY (don't repeat yourself) as someone noted previously. It's more verbose. It may not be as intuitive. But it works. I am not a SQL guru. If I am wrong about my alternative invoking only standard behavior, my apologies. > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Scott Robison
[sqlite] Third test of json and index expressions, now it works
Hello ! Sorry about that ! I confused your email with other person that was so negative and defensive about any critic to sqlite even constructive ones. I apologize again ! Cheers ! > Sat Sep 05 2015 10:19:55 pm CEST CEST from "R.Smith" >Subject: Re: [sqlite] Third test of json and index expressions, now it works > > On 2015-09-05 08:58 PM, Domingo Alvarez Duarte wrote: > >>Hello ! >> >> Again your proposition doesn't stand up ! >> > Again? > That was my first post on the subject and it was in reply to Darko whom > asked for a specific reason, so I supplied one. (Which by the way isn't > to say I am 100% convinced by the reason either, but no less, it's a > valid reason). > > >>There is no "waste of cpu cycles" for work that is not done, I mean for the >> ones that write queries for the machine instead to the humans, they will >> continue to have the same results (less parsing steps, besides the parsing >> normally accounts to something like 0.01% to 0.0001% or less of the time >> spent on the query). >> > If using aliases as valid where/group clause substitutes involved a mere > extra line of parsing code, then your assertion might hold water - but I > posit that believing that is akin to believing the Earth is flat. > > > Cheers, > Ryan > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Third test of json and index expressions, now it works
On 2015-09-05 08:58 PM, Domingo Alvarez Duarte wrote: > Hello ! > > Again your proposition doesn't stand up ! Again? That was my first post on the subject and it was in reply to Darko whom asked for a specific reason, so I supplied one. (Which by the way isn't to say I am 100% convinced by the reason either, but no less, it's a valid reason). > There is no "waste of cpu cycles" for work that is not done, I mean for the > ones that write queries for the machine instead to the humans, they will > continue to have the same results (less parsing steps, besides the parsing > normally accounts to something like 0.01% to 0.0001% or less of the time > spent on the query). If using aliases as valid where/group clause substitutes involved a mere extra line of parsing code, then your assertion might hold water - but I posit that believing that is akin to believing the Earth is flat. Cheers, Ryan
[sqlite] Third test of json and index expressions, now it works
Hello ! It seems that you mix apples to oranges and try to compare then. One thing is formal grammar/syntax and the other is implementation/optimization/execution. The fact that sqlite does things in one way now do not mean it's the best/definitive way of implement things and that can be changed. We are not going against anyone here we are discussing the way thins are/have been done and questioning the reason of it and possible alternatives for the same/different outcomes. All of it towards a more friendly/useful tool, but it seems that you fell attacked and try to negate most propositions with sometimes naive arguments. Relax and try to see the reasons behind other peoples view. Let's be friends! Cheers ! > Sat Sep 05 2015 8:55:55 pm CEST CEST from "Simon Slavin" > Subject: Re: [sqlite] Third test of json and index >expressions, now it works > > On 5 Sep 2015, at 7:07pm, Darko Volaric wrote: > > >>That's not a valid reason since it's trivial for SQLite to transform >> aliases by substituting their names with their definitions. It could be >> handled in the parser code. >> > And by doing that you would not get optimization, since SQLite would have >to work out the value once for the WHERE clause and once for the selected >value. Which is what SQLite does now. Which is what you are complaining about >in this thread. > > Remember: SQLite /does/ do this, even though it's not required by the SQL >standard. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Third test of json and index expressions, now it works
Hello ! Again your proposition doesn't stand up ! There is no "waste of cpu cycles" for work that is not done, I mean for the ones that write queries for the machine instead to the humans, they will continue to have the same results (less parsing steps, besides the parsing normally accounts to something like 0.01% to 0.0001% or less of the time spent on the query). Cheers ! ? > Sat Sep 05 2015 8:34:00 pm CEST CEST from "R.Smith" >Subject: Re: [sqlite] Third test of json and index expressions, now it works > > On 2015-09-05 08:07 PM, Darko Volaric wrote: > >>So my question remains: why not have this user friendly feature? What are >> the motivations for not having it? >> > Waste of CPU cycles punishing those users who stick to valid SQL for the > sins of those who like shortcuts. > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Third test of json and index expressions, now it works
On 2015-09-05 08:07 PM, Darko Volaric wrote: > So my question remains: why not have this user friendly feature? What are > the motivations for not having it? Waste of CPU cycles punishing those users who stick to valid SQL for the sins of those who like shortcuts.
[sqlite] Third test of json and index expressions, now it works
On 5 Sep 2015, at 7:07pm, Darko Volaric wrote: > That's not a valid reason since it's trivial for SQLite to transform > aliases by substituting their names with their definitions. It could be > handled in the parser code. And by doing that you would not get optimization, since SQLite would have to work out the value once for the WHERE clause and once for the selected value. Which is what SQLite does now. Which is what you are complaining about in this thread. Remember: SQLite /does/ do this, even though it's not required by the SQL standard. Simon.
[sqlite] Third test of json and index expressions, now it works
On Sep 5, 2015 4:10 PM, "Darko Volaric" wrote: > > That's not true. SQLite doesn't have to discard that information after > performing the substitutions. It can use it later for optimizations. > > I know that SQLite allows it. I'm not complaining about anything. Have a > look at my original question, which is, restated: > > What is the rationale behind disallowing using aliases in the body of > statements? While I agree that it is can be useful, the primary reason I can think of for disallowing it is preventing gratuitous incompatibilities between SQL engines. This is particularly true when there are two options that can give the same effect: expressing the select-from as a query vs a table name (something like): select * from (select a as b from c) where b is something Or with a common table expression which I have so little experience with so far that I'm not going to try to write an example on my phone. :) All this being said, and without the experience of having written a SQL engine personally, it sure feels like the standardization committee got this wrong. It is such an intuitive way of thinking of using aliases that it should be part of the standard. I only know from past experience with other SQL products that it doesn't work in other places.
[sqlite] Third test of json and index expressions, now it works
Hello ! Although your explanation is interesting it doesn't seems to be correct: First of all there is no point to to do anything if the whole sql statement is no correct. I mean: - If there is any reference to inexistent columns/functions/tables/views - If any expression is not valid (ex SELECT a+=b/0 AS c ...) - If there is any ambiguity .. Then and only then after a full parsing of all elements have passed the grammatical/syntactical/logical analysis then we should proceed to make a query plan to find the best alternative to answer the query. So if we need to do all of the mentioned checks before start to dig on the data we already know all the expressions and it's aliases and are able to switch between then without force users to explicitly and error prone duplicate then more than once. Cheers ! > Sat Sep 05 2015 6:23:01 pm CEST CEST from "Simon Slavin" > Subject: Re: [sqlite] Third test of json and index >expressions, now it works > > On 5 Sep 2015, at 5:07pm, Darko Volaric wrote: > > >>That's not what I said, why don't you read/quote the whole sentence: >> >> "Besides being part of the standard (I assume), what's the rationale for >> this restriction?" >> >> I'm asking why the SQL standard restricts the use of aliases in this way >> and what the benefit of this restriction is. >> > I'm sorry. I did not understand your grammar. Please put it down to me >being more used to British English than American English. I would have >expected "Apart from being part of the standard ...". > > The reason is that the SQL engine has to select the correct rows before it >has to work out the value of each column in the row. Consider these >statements: > > SELECT s FROM myTable WHERE l/z < 456; > SELECT * FROM myTable WHERE l/z < 456; > SELECT (s-70)*l/z, myfunction(l/z) FROM myTable WHERE l/z < 456; > > There is no point in SQL evaluating c-70*l/z or myfunction(l/z) for the >whole table if only two or three are going to satisfy b < 456. So it does the >WHERE clause first. Only once it has picked the right rows does it need to >pay attention to the bit between SELECT and FROM. Maybe no rows will satisfy >the WHERE clause and it won't have to bother at all. For a column alias to be >useful in both parts of the clause the syntax might be more like > > SELECT c-70*fract, myfunction(fract) FROM myTable WHERE (l/z AS fract) < >456; > > which is, of course, not valid SQL. > > You might also be interested to see whether this works: > > SELECT l/z AS fract, c-70*fract, myfunction(fract) FROM myTable WHERE l/z >< 456; > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Third test of json and index expressions, now it works
On 5 Sep 2015, at 5:07pm, Darko Volaric wrote: > That's not what I said, why don't you read/quote the whole sentence: > > "Besides being part of the standard (I assume), what's the rationale for > this restriction?" > > I'm asking why the SQL standard restricts the use of aliases in this way > and what the benefit of this restriction is. I'm sorry. I did not understand your grammar. Please put it down to me being more used to British English than American English. I would have expected "Apart from being part of the standard ...". The reason is that the SQL engine has to select the correct rows before it has to work out the value of each column in the row. Consider these statements: SELECT s FROM myTable WHERE l/z < 456; SELECT * FROM myTable WHERE l/z < 456; SELECT (s-70)*l/z, myfunction(l/z) FROM myTable WHERE l/z < 456; There is no point in SQL evaluating c-70*l/z or myfunction(l/z) for the whole table if only two or three are going to satisfy b < 456. So it does the WHERE clause first. Only once it has picked the right rows does it need to pay attention to the bit between SELECT and FROM. Maybe no rows will satisfy the WHERE clause and it won't have to bother at all. For a column alias to be useful in both parts of the clause the syntax might be more like SELECT c-70*fract, myfunction(fract) FROM myTable WHERE (l/z AS fract) < 456; which is, of course, not valid SQL. You might also be interested to see whether this works: SELECT l/z AS fract, c-70*fract, myfunction(fract) FROM myTable WHERE l/z < 456; Simon.
[sqlite] Third test of json and index expressions, now it works
On 5 Sep 2015, at 3:19pm, Darko Volaric wrote: > Besides being part of the standard (I assume) You assume incorrectly. In the classic SQL model, aliases to column names are assigned after the results have been returned. In other words, aliases cannot be used in the WHERE clause. Simon.
[sqlite] Third test of json and index expressions, now it works
Hello !
I also prefer a "DRY" approach than be repeating things.
Repetition is the mother of several errors !
Cheers !
> Sat Sep 05 2015 4:19:00 pm CEST CEST from "Darko Volaric"
> Subject: Re: [sqlite] Third test of json and index
>expressions, now it works
>
> Besides being part of the standard (I assume), what's the rationale for
> this restriction?
>
> It would seem that standard SQL is being willfully less efficient and more
> error prone by making the user rewrite expressions.
>
> Isn't this in the same category as manifest typing, where a more liberal
> approach is an improvement?
>
>
> On Fri, Sep 4, 2015 at 9:35 PM, Richard Hipp wrote:
>
>
>>On 9/4/15, Domingo Alvarez Duarte wrote:
>>
>>
>>>Would be nice to sqlite be able to recognize aliases and also do not call
>>> column expressions multiple times.
>>>
>>> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS
>> val
>>
>>>from json_tbl where val = 'the_value_1';"));
>>>
>>>
>> This is not valid SQL, actually. For clarity, here is the (invalid)
>> SQL reformatted:
>>
>> SELECT a+b AS x FROM t1 WHERE x=99;
>>
>> You are not suppose to be able to access the "x" alias within the WHERE
>> clause.
>>
>> Yes, I know that SQLite allows this. But it does so grudgingly, for
>> historical reasons. It is technically not valid. Note that none of
>> MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
>> above.
>>
>> Because the SQL is technically not valid, I am less inclined to spend
>> a lot of time trying to optimize it.
>>
>> I really wish there was a way for me to fix this historical
>> permissiveness in SQLite without breaking millions of (miscoded)
>> iPhone/Android apps. I'd do so if I could.
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] Third test of json and index expressions, now it works
That's not true. SQLite doesn't have to discard that information after performing the substitutions. It can use it later for optimizations. I know that SQLite allows it. I'm not complaining about anything. Have a look at my original question, which is, restated: What is the rationale behind disallowing using aliases in the body of statements? I think it's an important question since it's obviously easier (trivial) to do common sub-expression elimination with aliases and it's clearly easier for humans to write correct code using them. I'm wondering what the evils of aliases in bodies are, because I can't think of any. Maybe its because SQL(ite) always wants to reevaluate expressions wherever they occur, and the implied semantics of body aliases run counter to this. If that's the case, my next question would be why that behavior is desirable. On Sat, Sep 5, 2015 at 11:55 AM, Simon Slavin wrote: > > On 5 Sep 2015, at 7:07pm, Darko Volaric wrote: > > > That's not a valid reason since it's trivial for SQLite to transform > > aliases by substituting their names with their definitions. It could be > > handled in the parser code. > > And by doing that you would not get optimization, since SQLite would have > to work out the value once for the WHERE clause and once for the selected > value. Which is what SQLite does now. Which is what you are complaining > about in this thread. > > Remember: SQLite /does/ do this, even though it's not required by the SQL > standard. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Third test of json and index expressions, now it works
Hello again !
There is still some opportunities for constant folding that sqlite is not
using, I'm not saying that is easy to implement.
The same principle could be applied to deterministic functions where all of
it's parameters end up been constants.
_output of "sqlite3 < test.sql" commented
0|0|0|SCAN TABLE json_tbl
0|0|0|SCAN TABLE json_tbl
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
0|0|0|SCAN TABLE json_tbl <<<<< here sqlite could do a compile time constant
folding and use index
0|0|0|SCAN TABLE json_tbl <<<<< same here
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
_
_test.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE json_tbl(id integer primary key, json text collate nocase);
CREATE VIEW json_tbl_value_idx_view AS
??? SELECT *, '$.' AS idx1, 'value' AS idx2, '$.value' as path,
json_extract(json, '$.value') AS val
??? FROM json_tbl;
CREATE VIEW json_tbl_value_idx2_view AS
??? SELECT *, idx1 || idx2 AS idx_path -- compile time constant fold
opportunity
??? FROM json_tbl_value_idx_view;
CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx_view WHERE 'the_value_33'
= json_extract(json, '$.' || idx2); -- compile time constant fold opportunity
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx_view WHERE 'the_value_33'
= json_extract(json, idx1 || idx2); -- compile time constant fold opportunity
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx_view WHERE 'the_value_33'
= json_extract(json, path);
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx2_view WHERE
'the_value_33' = json_extract(json, idx_path);
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx2_view WHERE
'the_value_33' = json_extract(json, idx1 || idx2); -- compile time constant
fold opportunity
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx2_view WHERE
'the_value_33' = json_extract(json, path);
COMMIT;
_
> Sat Sep 05 2015 6:35:08 am CEST CEST from "Richard Hipp"
>Subject: Re: [sqlite] Third test of json and index expressions, now it works
>
> On 9/4/15, Domingo Alvarez Duarte wrote:
>
>
>>Would be nice to sqlite be able to recognize aliases and also do not call
>> column expressions multiple times.
>>
>> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS
>>val
>> from json_tbl where val = 'the_value_1';"));
>>
>>
> This is not valid SQL, actually. For clarity, here is the (invalid)
> SQL reformatted:
>
> SELECT a+b AS x FROM t1 WHERE x=99;
>
> You are not suppose to be able to access the "x" alias within the WHERE
>clause.
>
> Yes, I know that SQLite allows this. But it does so grudgingly, for
> historical reasons. It is technically not valid. Note that none of
> MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
> above.
>
> Because the SQL is technically not valid, I am less inclined to spend
> a lot of time trying to optimize it.
>
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps. I'd do so if I could.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] Third test of json and index expressions, now it works
Hello ! Thanks for reply ! I still think that is worth having warnings for the already known quirks on sqlite, probably even show it to stderr when building in debug mode because I believe at some point people will do some debugging. Or maybe even better having a pragma "PRAGMA check_valid_sql_statements=ON" and add a note on the documentation to developers to try at least once before deploy and or in development mode to be sure they have clean/valid sql statements. It probably will not clean all existing code but I believe it can gradually improve the situation. Cheers ! > Sat Sep 05 2015 11:16:26 am CEST CEST from "Simon Slavin" > Subject: Re: [sqlite] Third test of json and index >expressions, now it works > > On 5 Sep 2015, at 10:07am, Domingo Alvarez Duarte > wrote: > > >>What about the warning messages to stderr through sqlite3 when opening >> databases with invalid sql constructions, this way we can gradually have >>less >> and less code written in a non compliant way. >> > It might be acceptable to put warning code in sqlite3.exe and its >equivalents for other platforms. But the majority of SQLite installation are >on things which are not personal computers: mobile phones, GPS units, Digital >TV boxes, etc.. They don't have stderr. They don't even have stdout. > > Also I don't think it's worth doing at all. The developers are currently >playing with SQLite4 which does not have to support the same level of >backward compatibility as SQLite3. It can just refuse to parse commands which >the development team does not wish to support. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Third test of json and index expressions, now it works
Hello !
Thanks to point out that plain sql is better to demonstrate a point when
possible !
And after your answer I did another tests and could see that by using views
then we can achieve the use of aliases in a clean way.
This is supposed to be valid, doesn't it ?
__Output of "sqlite3 < test.sql"
0|0|0|SCAN TABLE json_tbl
0|0|0|SCAN TABLE json_tbl
0|0|0|SCAN TABLE json_tbl
0|0|0|SEARCH TABLE json_tbl USING INTEGER PRIMARY KEY (rowid>?)
0|0|0|SEARCH TABLE json_tbl USING INTEGER PRIMARY KEY (rowid>?)
0|0|0|SCAN TABLE json_tbl
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=? AND rowid>?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=? AND rowid>?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
__
__test.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE json_tbl(id integer primary key, json text collate nocase);
CREATE VIEW json_tbl_value_view AS SELECT id,? json_extract(json, '$value')
AS val FROM json_tbl;
INSERT INTO "json_tbl" VALUES(1,'{"id" : 1, "value" : "the_value_1"}');
INSERT INTO "json_tbl" VALUES(2,'{"id" : 2, "value" : "the_value_2"}');
INSERT INTO "json_tbl" VALUES(3,'{"id" : 3, "value" : "the_value_3"}');
INSERT INTO "json_tbl" VALUES(4,'{"id" : 4, "value" : "the_value_4"}');
INSERT INTO "json_tbl" VALUES(5,'{"id" : 5, "value" : "the_value_5"}');
INSERT INTO "json_tbl" VALUES(6,'{"id" : 6, "value" : "the_value_6"}');
INSERT INTO "json_tbl" VALUES(7,'{"id" : 7, "value" : "the_value_7"}');
INSERT INTO "json_tbl" VALUES(8,'{"id" : 8, "value" : "the_value_8"}');
INSERT INTO "json_tbl" VALUES(9,'{"id" : 9, "value" : "the_value_9"}');
INSERT INTO "json_tbl" VALUES(10,'{"id" : 10, "value" : "the_value_10"}');
EXPLAIN QUERY PLAN SELECT * FROM json_tbl WHERE json_extract(json, '$.value')
= 'the_value_33';
EXPLAIN QUERY PLAN SELECT * FROM json_tbl WHERE 'the_value_33' =
json_extract(json, '$.value');
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_view WHERE 'the_value_33' =
val;
EXPLAIN QUERY PLAN
WITH RECURSIVE above33 AS (SELECT * FROM json_tbl WHERE id > 33)
SELECT * FROM above33 WHERE json_extract(json, '$.value') = 'the_value_40';
EXPLAIN QUERY PLAN
WITH RECURSIVE above33 AS (SELECT id,? json_extract(json, '$.value') AS val
FROM json_tbl WHERE id > 33)
SELECT * FROM above33 WHERE val = 'the_value_40';
EXPLAIN QUERY PLAN
WITH RECURSIVE allofit AS (SELECT id,? json_extract(json, '$.value') AS val
FROM json_tbl)
SELECT * FROM allofit WHERE val = 'the_value_40';
CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));
EXPLAIN QUERY PLAN SELECT * FROM json_tbl WHERE json_extract(json, '$.value')
= 'the_value_33';
EXPLAIN QUERY PLAN SELECT * FROM json_tbl WHERE 'the_value_33' =
json_extract(json, '$.value');
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_view WHERE 'the_value_33' =
val;
EXPLAIN QUERY PLAN
WITH RECURSIVE above33 AS (SELECT * FROM json_tbl WHERE id > 33)
SELECT * FROM above33 WHERE json_extract(json, '$.value') = 'the_value_40';
EXPLAIN QUERY PLAN
WITH RECURSIVE above33 AS (SELECT id,? json_extract(json, '$.value') AS val
FROM json_tbl WHERE id > 33)
SELECT * FROM above33 WHERE val = 'the_value_40';
EXPLAIN QUERY PLAN
WITH RECURSIVE allofit AS (SELECT id,? json_extract(json, '$.value') AS val
FROM json_tbl)
SELECT * FROM allofit WHERE val = 'the_value_40';
COMMIT;
__
> Sat Sep 05 2015 6:35:08 am CEST CEST from "Richard Hipp"
>Subject: Re: [sqlite] Third test of json and index expressions, now it works
>
> On 9/4/15, Domingo Alvarez Duarte wrote:
>
>
>>Would be nice to sqlite be able to recognize aliases and also do not call
>> column expressions multiple times.
>>
>> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS
>>val
>> from json_tbl where val = 'the_value_1';"));
>>
>>
> This is not valid SQL, actually. For clarity, here is the (invalid)
> SQL reformatted:
>
> SELECT a+b AS x FROM t1 WHERE x=99;
>
> You are not suppose to be able to access the "x" alias within the WHERE
>clause.
>
> Yes, I know that SQLite allows this. But it does so grudgingly, for
> historical reasons. It is technically not valid. Note that none of
> MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
> above.
>
> Because the SQL is technically not valid, I am less inclined to spend
> a lot of time trying to optimize it.
>
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps. I'd do so if I could.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] Third test of json and index expressions, now it works
That's not a valid reason since it's trivial for SQLite to transform aliases by substituting their names with their definitions. It could be handled in the parser code. Meanwhile it's much harder for a human to do the opposite. So my question remains: why not have this user friendly feature? What are the motivations for not having it? On Sat, Sep 5, 2015 at 9:23 AM, Simon Slavin wrote: > > On 5 Sep 2015, at 5:07pm, Darko Volaric wrote: > > > That's not what I said, why don't you read/quote the whole sentence: > > > > "Besides being part of the standard (I assume), what's the rationale for > > this restriction?" > > > > I'm asking why the SQL standard restricts the use of aliases in this way > > and what the benefit of this restriction is. > > I'm sorry. I did not understand your grammar. Please put it down to me > being more used to British English than American English. I would have > expected "Apart from being part of the standard ...". > > The reason is that the SQL engine has to select the correct rows before it > has to work out the value of each column in the row. Consider these > statements: > > SELECT s FROM myTable WHERE l/z < 456; > SELECT * FROM myTable WHERE l/z < 456; > SELECT (s-70)*l/z, myfunction(l/z) FROM myTable WHERE l/z < 456; > > There is no point in SQL evaluating c-70*l/z or myfunction(l/z) for the > whole table if only two or three are going to satisfy b < 456. So it does > the WHERE clause first. Only once it has picked the right rows does it > need to pay attention to the bit between SELECT and FROM. Maybe no rows > will satisfy the WHERE clause and it won't have to bother at all. For a > column alias to be useful in both parts of the clause the syntax might be > more like > > SELECT c-70*fract, myfunction(fract) FROM myTable WHERE (l/z AS fract) < > 456; > > which is, of course, not valid SQL. > > You might also be interested to see whether this works: > > SELECT l/z AS fract, c-70*fract, myfunction(fract) FROM myTable WHERE l/z > < 456; > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Third test of json and index expressions, now it works
Hello ! > I really wish there was a way for me to fix this historical > permissiveness in SQLite without breaking millions of (miscoded) > iPhone/Android apps. I'd do so if I could. > -- > > ? > > What about the warning messages to stderr through sqlite3 when opening databases with invalid sql constructions, this way we can gradually have less and less code written in a non compliant way. >sqlite3 sqlite>SELECT a+b AS x FROM (select 4 as a, 5 as b) WHERE x=99; -- warning aliases are not supposed to be used on where clauses -- historical mistake of sqlite to accept it -- please rewrite your code in a compliant way, sqlite can stop support this at any time ? Cheers !
[sqlite] Third test of json and index expressions, now it works
On 5 Sep 2015, at 10:07am, Domingo Alvarez Duarte wrote: > What about the warning messages to stderr through sqlite3 when opening > databases with invalid sql constructions, this way we can gradually have less > and less code written in a non compliant way. It might be acceptable to put warning code in sqlite3.exe and its equivalents for other platforms. But the majority of SQLite installation are on things which are not personal computers: mobile phones, GPS units, Digital TV boxes, etc.. They don't have stderr. They don't even have stdout. Also I don't think it's worth doing at all. The developers are currently playing with SQLite4 which does not have to support the same level of backward compatibility as SQLite3. It can just refuse to parse commands which the development team does not wish to support. Simon.
[sqlite] Third test of json and index expressions, now it works
> > Besides being part of the standard (I assume) > You assume incorrectly. In the classic SQL model, aliases to column names > are assigned after the results have been returned. In other words, > aliases cannot be used in the WHERE clause. Or group by clause ... > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Third test of json and index expressions, now it works
On 5 Sep 2015, at 5:35am, Richard Hipp wrote: > I really wish there was a way for me to fix this historical > permissiveness in SQLite without breaking millions of (miscoded) > iPhone/Android apps. I'd do so if I could. That's what SQLite4 is for. I hope. Simon.
[sqlite] Third test of json and index expressions, now it works
That's not what I said, why don't you read/quote the whole sentence: "Besides being part of the standard (I assume), what's the rationale for this restriction?" I'm asking why the SQL standard restricts the use of aliases in this way and what the benefit of this restriction is. On Sat, Sep 5, 2015 at 8:49 AM, Simon Slavin wrote: > > On 5 Sep 2015, at 3:19pm, Darko Volaric wrote: > > > Besides being part of the standard (I assume) > > You assume incorrectly. In the classic SQL model, aliases to column names > are assigned after the results have been returned. In other words, aliases > cannot be used in the WHERE clause. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Third test of json and index expressions, now it works
Besides being part of the standard (I assume), what's the rationale for
this restriction?
It would seem that standard SQL is being willfully less efficient and more
error prone by making the user rewrite expressions.
Isn't this in the same category as manifest typing, where a more liberal
approach is an improvement?
On Fri, Sep 4, 2015 at 9:35 PM, Richard Hipp wrote:
> On 9/4/15, Domingo Alvarez Duarte wrote:
> >
> > Would be nice to sqlite be able to recognize aliases and also do not call
> > column expressions multiple times.
> >
> > print("json=", db.exec_get_one("select json_extract(json, '$.value') AS
> val
> > from json_tbl where val = 'the_value_1';"));
> >
>
> This is not valid SQL, actually. For clarity, here is the (invalid)
> SQL reformatted:
>
>SELECT a+b AS x FROM t1 WHERE x=99;
>
> You are not suppose to be able to access the "x" alias within the WHERE
> clause.
>
> Yes, I know that SQLite allows this. But it does so grudgingly, for
> historical reasons. It is technically not valid. Note that none of
> MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
> above.
>
> Because the SQL is technically not valid, I am less inclined to spend
> a lot of time trying to optimize it.
>
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps. I'd do so if I could.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] Third test of json and index expressions, now it works
On 9/4/15, Domingo Alvarez Duarte wrote:
>
> Would be nice to sqlite be able to recognize aliases and also do not call
> column expressions multiple times.
>
> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS val
> from json_tbl where val = 'the_value_1';"));
>
This is not valid SQL, actually. For clarity, here is the (invalid)
SQL reformatted:
SELECT a+b AS x FROM t1 WHERE x=99;
You are not suppose to be able to access the "x" alias within the WHERE clause.
Yes, I know that SQLite allows this. But it does so grudgingly, for
historical reasons. It is technically not valid. Note that none of
MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
above.
Because the SQL is technically not valid, I am less inclined to spend
a lot of time trying to optimize it.
I really wish there was a way for me to fix this historical
permissiveness in SQLite without breaking millions of (miscoded)
iPhone/Android apps. I'd do so if I could.
--
D. Richard Hipp
drh at sqlite.org
[sqlite] Third test of json and index expressions, now it works
Hello again !
I was trying to use the alias in the where clause hopping sqlite would be
smart enough to recognize it and use the index but that doesn't happen.
So I tried again repeating the expression instead of using the alias and now
sqlite recognize the index and run very fast.
Would be nice to sqlite be able to recognize aliases and also do not call
column expressions multiple times.
Cheers !
Output
Time to insert??? 5000??? 0.035002??? records by second = ???
142849
count=??? 4999
json=??? the_value_1
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select raw??? 5000??? 0.002689??? records by second = ???
1.85943e+06
Time to select raw one by one??? 5000??? 7.60242??? records by
second = ??? 657.685
Time to select json_extrat no index??? 5000??? 7.59418??? records by
second = ??? 658.399
Time to create index??? 5000??? 0.006153??? records by second =
??? 812612
??? 0??? 0??? 0??? SEARCH TABLE json_tbl USING INDEX json_tbl_idx
(=?)
Time to select json_extrat indexed??? 5000??? 0.034494??? records by
second = ??? 144953
Program
local max_count = 5000;
local sql = "CREATE TABLE IF NOT EXISTS json_tbl(id integer primary key, json
text collate nocase);";
local db = SQLite3(":memory:");
db.exec_dml(sql);
local stmt = db.prepare("insert into json_tbl(json) values(?);");
local start = os.clock();
db.exec_dml("begin;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();
db.exec_dml("commit;");
local time_spent = os.clock() -start;
print("Time to insert", max_count, time_spent, "records by second = ",
max_count/time_spent);
print("count=", db.exec_get_one("select count(*) from json_tbl"));
print("json=", db.exec_get_one("select json_extract(json, '$.value') AS val
from json_tbl? where val = 'the_value_1';"));
sql = "select? json_extract(json, '$.value') AS val? from json_tbl where
json_extract(json, '$.value') = ?;";
local showPlan = function()
{
??? stmt = db.prepare("explain query plan " + sql);
??? while(stmt.next_row())
??? {
??? ??? local line = "";
??? ??? for(local i=0, len = stmt.col_count(); i < len; ++i)
??? ??? {
??? ??? ??? line += "\t" + stmt.col(i);
??? ??? }
??? ??? print(line);
??? }
??? stmt.finalize();
}
showPlan();
start = os.clock();
stmt = db.prepare("select * from json_tbl");
while(stmt.next_row())
{
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select raw", max_count, time_spent, "records by second = ",
max_count/time_spent);
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select raw one by one", max_count, time_spent, "records by
second = ", max_count/time_spent);
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select json_extrat no index", max_count, time_spent, "records
by second = ", max_count/time_spent);
start = os.clock();
db.exec_dml("create index json_tbl_idx on json_tbl(json_extract(json,
'$.value'));");
time_spent = os.clock() -start;
print("Time to create index", max_count, time_spent, "records by second = ",
max_count/time_spent);
showPlan();
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select json_extrat indexed", max_count, time_spent, "records
by second = ", max_count/time_spent);
/*
local function unql_exec(db, sql)
{
??? local the_stmt = db.prepare(sql);
??? local rc = the_stmt.step();
??? the_stmt.finalize();
??? return rc;
}
start = os.clock();
local db_unql = xjd1(db);
unql_exec(db_unql, "CREATE COLLECTION unql_json;");
stmt = db_unql.prepare("INSERT INTO unql_json VALUE ?;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();
stmt = db_unql.prepare("SELECT FROM unql_json WHERE unql_json.value = ?;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select unql", max_count, time_spent, "records by second = ",
max_count/time_spent);
*/
//db.backup("json.db");
db.close();
