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 (<expr>=?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=? AND rowid>?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=? AND rowid>?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=?)
__________
__________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" <drh at sqlite.org>
>Subject: Re: [sqlite] Third test of json and index expressions, now it works
>
> On 9/4/15, Domingo Alvarez Duarte <sqlite-mail at dev.dadbiz.es> 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
>
>
>
?
