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 <drh at sqlite.org> wrote:
> 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
>
