[sqlite] What's the reason for alias names not being visible in subqueries?
Not really, then I would have to select child table with which to JOIN on condition, based on the value of parent table. CREATE TABLE parent( id INTEGER PRIMARY KEY, child_type INTEGER, CHECK(child_type IN (1, 2)) ); CREATE TABLE child_1( id INTEGER PRIMARY KEY, my_value INTEGER, ... ); CREATE TABLE child_2( id INTEGER PRIMARY KEY, my_value INTEGER, my_other value INTEGER, ... ); CREATE TABLE parent_child_1_link( parent_id INTEGER PRIMARY KEY, child_1_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(id), FOREIGN KEY(child_1_id) REFERENCES child_1(id) ); CREATE TABLE parent_child_2_link( parent_id INTEGER PRIMARY KEY, child_2_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(id), FOREIGN KEY(child_2_id) REFERENCES child_2(id) ); now, depending on the child_type in the parent I want to select * child_type * child_id * my_value of specific child * some other values of specific child * some other values of parent I cannot JOIN obviously, so I decided to first fetch specific ID of a child, alias it and then use it in selection of properties of specific child. This way i would avoid querying parent_child_1_link or parent_child_2_link tables for each property of specific child. But i think 2 queries will work more efficiently. (SIDE NOTE: I know about necessity of indices for FKs, I decided to omit them because they are meaningless in this example) 8 February 2016, 13:42:04, by "J Decker" : > On Mon, Feb 8, 2016 at 3:38 AM, Paul wrote: > > I see, thank you for pointing out. > > > > I wanted to use it on table with conditional relations with 3 different > > child tables. > > Though I could use a trick and fit data selection into one query, > > efficiently. > > Alas I am forced to stick to 2 queries. > > > > Might still be able to collapse it into one CTE query but I don't > know what the other part is to demo that... > > https://www.sqlite.org/lang_with.html > > > Thank you! > > > > 8 February 2016, 12:08:26, by "Clemens Ladisch" : > > > >> Paul wrote: > >> > I am curious what is a particular reason that aliased columns in a query > >> > not visible to sub-queries? > >> > >> Because the SQL standard says so. > >> > >> > Of course it is possible to duplicate expression in sub-query ... > >> > But this would not be as efficient as to access result of already > >> > evaluated expression. > >> > >> An alias refers not to an expression's value but to the expression > >> itself, so it would not be any more efficient. (SQLite's optimizer > >> typically is not smart enough to detect and remove the duplication.) > >> > >> > >> As a workaround, you have to move the expression into a subquery in > >> the FROM clause: > >> > >> SELECT super_id, > >>(SELECT qux FROM baz WHERE id = super_id) > >> FROM (SELECT id as super_id FROM foo); > >> > >> > >> Regards, > >> Clemens > >> ___ > >> 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-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What's the reason for alias names not being visible in subqueries?
I see, thank you for pointing out. I wanted to use it on table with conditional relations with 3 different child tables. Though I could use a trick and fit data selection into one query, efficiently. Alas I am forced to stick to 2 queries. Thank you! 8 February 2016, 12:08:26, by "Clemens Ladisch" : > Paul wrote: > > I am curious what is a particular reason that aliased columns in a query > > not visible to sub-queries? > > Because the SQL standard says so. > > > Of course it is possible to duplicate expression in sub-query ... > > But this would not be as efficient as to access result of already evaluated > > expression. > > An alias refers not to an expression's value but to the expression > itself, so it would not be any more efficient. (SQLite's optimizer > typically is not smart enough to detect and remove the duplication.) > > > As a workaround, you have to move the expression into a subquery in > the FROM clause: > > SELECT super_id, >(SELECT qux FROM baz WHERE id = super_id) > FROM (SELECT id as super_id FROM foo); > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What's the reason for alias names not being visible in subqueries?
Paul wrote: > CREATE TABLE parent( > CREATE TABLE child_1( > CREATE TABLE child_2( > CREATE TABLE parent_child_1_link( > CREATE TABLE parent_child_2_link( > > now, depending on the child_type in the parent I want to select ... Show your query. Or at least example data and the desired result. Regards, Clemens
[sqlite] What's the reason for alias names not being visible in subqueries?
Paul wrote: > I am curious what is a particular reason that aliased columns in a query not > visible to sub-queries? Because the SQL standard says so. > Of course it is possible to duplicate expression in sub-query ... > But this would not be as efficient as to access result of already evaluated > expression. An alias refers not to an expression's value but to the expression itself, so it would not be any more efficient. (SQLite's optimizer typically is not smart enough to detect and remove the duplication.) As a workaround, you have to move the expression into a subquery in the FROM clause: SELECT super_id, (SELECT qux FROM baz WHERE id = super_id) FROM (SELECT id as super_id FROM foo); Regards, Clemens
[sqlite] What's the reason for alias names not being visible in subqueries?
Hello! I am curious what is a particular reason that aliased columns in a query not visible to sub-queries? CREATE TABLE foo( id INTEGER, bar INTEGER ); INSERT INTO foo VALUES(1, 2), (3, 4); SELECT 1 as super_id, (SELECT bar FROM foo WHERE id = super_id); Gives an error: Error: no such column: super_id On the other hand, we can easily refer to values of a table that participates in a query. CREATE TABLE baz( id INTEGER, qux INTEGER ); INSERT INTO baz VALUES(1, 6), (2, 8); SELECT id, (SELECT qux FROM baz WHERE id = foo.id) FROM foo; id (SELECT qux FROM baz WHERE id = foo.id) -- --- 1 6 3 But still not though alias: SELECT id as super_id, (SELECT qux FROM baz WHERE id = super_id) FROM foo; Error: no such column: super_id Why would this be useful? Sometimes you need a sub-select by a result of an expression. SELECT ..., , (SELECT ... FROM FOO where id = ) ..; Of course it is possible to duplicate expression in sub-query SELECT ..., , (SELECT ... FROM FOO where id = ) ..; But this would not be as efficient as to access result of already evaluated expression. Especially if itself is a sub-select. Also imagine a chain of sub-queries.
[sqlite] What's the reason for alias names not being visible in subqueries?
select * from parent P, parent_child_1_link L, child_1 C where P.id = L.parent_id and C.id = L.child_id and P.child_type = 1 union select * from parent P, parent_child_2_link L, child_2 C where P.id = L.parent_id and C.id = L.child_id and P.child_type = 2 It is a very straightforward join. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Paul > Sent: Monday, 8 February, 2016 04:57 > To: SQLite mailing list > Subject: Re: [sqlite] What's the reason for alias names not being visible > in subqueries? > > Not really, then I would have to select child table with which to JOIN on > condition, based on the value of parent table. > > > CREATE TABLE parent( > id INTEGER PRIMARY KEY, > child_type INTEGER, > CHECK(child_type IN (1, 2)) > ); > > CREATE TABLE child_1( > id INTEGER PRIMARY KEY, > my_value INTEGER, > ... > ); > > CREATE TABLE child_2( > id INTEGER PRIMARY KEY, > my_value INTEGER, > my_other value INTEGER, > ... > ); > > CREATE TABLE parent_child_1_link( > parent_id INTEGER PRIMARY KEY, > child_1_id INTEGER, > FOREIGN KEY(parent_id) REFERENCES parent(id), > FOREIGN KEY(child_1_id) REFERENCES child_1(id) > ); > > CREATE TABLE parent_child_2_link( > parent_id INTEGER PRIMARY KEY, > child_2_id INTEGER, > FOREIGN KEY(parent_id) REFERENCES parent(id), > FOREIGN KEY(child_2_id) REFERENCES child_2(id) > ); > > > now, depending on the child_type in the parent I want to select > * child_type > * child_id > * my_value of specific child > * some other values of specific child > * some other values of parent > > I cannot JOIN obviously, so I decided to first fetch specific ID of a > child, alias it and then use it > in selection of properties of specific child. This way i would avoid > querying parent_child_1_link > or parent_child_2_link tables for each property of specific child. > > But i think 2 queries will work more efficiently. > > (SIDE NOTE: I know about necessity of indices for FKs, I decided > to omit them because they are meaningless in this example) > > 8 February 2016, 13:42:04, by "J Decker" : > > > On Mon, Feb 8, 2016 at 3:38 AM, Paul wrote: > > > I see, thank you for pointing out. > > > > > > I wanted to use it on table with conditional relations with 3 > different child tables. > > > Though I could use a trick and fit data selection into one query, > efficiently. > > > Alas I am forced to stick to 2 queries. > > > > > > > Might still be able to collapse it into one CTE query but I don't > > know what the other part is to demo that... > > > > https://www.sqlite.org/lang_with.html > > > > > Thank you! > > > > > > 8 February 2016, 12:08:26, by "Clemens Ladisch" : > > > > > >> Paul wrote: > > >> > I am curious what is a particular reason that aliased columns in a > query not visible to sub-queries? > > >> > > >> Because the SQL standard says so. > > >> > > >> > Of course it is possible to duplicate expression in sub-query ... > > >> > But this would not be as efficient as to access result of already > evaluated expression. > > >> > > >> An alias refers not to an expression's value but to the expression > > >> itself, so it would not be any more efficient. (SQLite's optimizer > > >> typically is not smart enough to detect and remove the duplication.) > > >> > > >> > > >> As a workaround, you have to move the expression into a subquery in > > >> the FROM clause: > > >> > > >> SELECT super_id, > > >>(SELECT qux FROM baz WHERE id = super_id) > > >> FROM (SELECT id as super_id FROM foo); > > >> > > >> > > >> Regards, > > >> Clemens > > >> ___ > > >> 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-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] What's the reason for alias names not being visible in subqueries?
On Mon, Feb 8, 2016 at 3:38 AM, Paul wrote: > I see, thank you for pointing out. > > I wanted to use it on table with conditional relations with 3 different child > tables. > Though I could use a trick and fit data selection into one query, efficiently. > Alas I am forced to stick to 2 queries. > Might still be able to collapse it into one CTE query but I don't know what the other part is to demo that... https://www.sqlite.org/lang_with.html > Thank you! > > 8 February 2016, 12:08:26, by "Clemens Ladisch" : > >> Paul wrote: >> > I am curious what is a particular reason that aliased columns in a query >> > not visible to sub-queries? >> >> Because the SQL standard says so. >> >> > Of course it is possible to duplicate expression in sub-query ... >> > But this would not be as efficient as to access result of already >> > evaluated expression. >> >> An alias refers not to an expression's value but to the expression >> itself, so it would not be any more efficient. (SQLite's optimizer >> typically is not smart enough to detect and remove the duplication.) >> >> >> As a workaround, you have to move the expression into a subquery in >> the FROM clause: >> >> SELECT super_id, >>(SELECT qux FROM baz WHERE id = super_id) >> FROM (SELECT id as super_id FROM foo); >> >> >> Regards, >> Clemens >> ___ >> 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] What's the reason for alias names not being visible in subqueries?
On Mon, Feb 8, 2016 at 12:47 AM, Paul wrote: > Hello! > > I am curious what is a particular reason that aliased columns in a query not > visible to sub-queries? > > CREATE TABLE foo( > id INTEGER, > bar INTEGER > ); > > INSERT INTO foo VALUES(1, 2), (3, 4); > > SELECT 1 as super_id, (SELECT bar FROM foo WHERE id = super_id); > This looks like a simple select select 1 as super_id,bar from foo where id=super_id > Gives an error: > Error: no such column: super_id > > On the other hand, we can easily refer to values of a table that participates > in a query. > > CREATE TABLE baz( > id INTEGER, > qux INTEGER > ); > > INSERT INTO baz VALUES(1, 6), (2, 8); > > SELECT id, (SELECT qux FROM baz WHERE id = foo.id) FROM foo; > this looks like a join select foo.id,qux from foo join baz on baz.id=foo.id > id (SELECT qux FROM baz WHERE id = foo.id) > -- --- > 1 6 > 3 > > But still not though alias: > > SELECT id as super_id, (SELECT qux FROM baz WHERE id = super_id) FROM foo; > Error: no such column: super_id And this select foo.id as super_id,qux from foo join baz on baz.id=super_id > > > > Why would this be useful? > > Sometimes you need a sub-select by a result of an expression. > > SELECT ..., , (SELECT ... FROM FOO where id = expression X>) ..; > > Of course it is possible to duplicate expression in sub-query > > SELECT ..., , (SELECT ... FROM FOO where id = ) > ..; > and both of those > But this would not be as efficient as to access result of already evaluated > expression. > Especially if itself is a sub-select. Also imagine a chain of > sub-queries. > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
