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 (<expr>=?)
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 (<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_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" <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
>
>
>
?
