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
