On Sun, Sep 6, 2015 at 3:47 PM, pasma10 <pasma10 at concepts.nl> wrote:
> op 06-09-2015 22:42 schreef Scott Robison op scott at casaderobison.com: > > > On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma <pasma10 at concepts.nl> wrote: > > > >> > >> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven: > >> > >>> > >>> One, the argument wasn't whether or not column expressions were called > >>> multiple times. My understanding of the debate was that using aliases > in > >>> place of their definitions made code more understandable and more > easily > >>> maintained than gratuitous repetition of the definitions. > >>> > >>> Two, you used a different query. Looking at my query again (fleshed out > >>> since I'm on a computer vs my phone): > >>> > >>> sqlite> create table c(a); > >>> sqlite> insert into c values(1); > >>> sqlite> insert into c values(2); > >>> sqlite> insert into c values(3); > >>> sqlite> insert into c values(4); > >>> sqlite> select * from (select a as b from c) where b > 2; > >>> 3 > >>> 4 > >>> sqlite> explain query plan select * from (select a as b from c) where > b > > >>> 2; > >>> 0|0|0|SCAN TABLE c > >>> > >>> sqlite> select * from (select abs(a-a-a) as b from c) where b > 2; > >>> 3 > >>> 4 > >>> sqlite> explain query plan select * from (select abs(a-a-a) as b from > c) > >>> where b > 2; > >>> 0|0|0|SCAN TABLE c > >>> > >>> Clearly, this gives you an opportunity to replace definitions with > >>> aliases, > >>> since the aliases in the inner query become the column names of the > outer > >>> query. > >>> > >>> Your example doesn't do the same thing at all (ignoring the difference > >>> between your condition of > 0 and mine of > 2). Your query is made of > >>> scalar subqueries and you will never get more than a single row back. > >>> > >>> The following two queries are not the same: > >>> > >>> select 1 from (select (select a from c) as b) where b > 0; > >>> > >>> select 1 from (select a as b from c) where b > 0; > >>> > >>> In any case, this is a perfectly good option to query building that > don't > >>> require non-standard behavior (as far as I can tell) while still giving > >>> the > >>> benefit of DRY (don't repeat yourself) as someone noted previously. > It's > >>> more verbose. It may not be as intuitive. But it works. > >>> > >>> I am not a SQL guru. If I am wrong about my alternative invoking only > >>> standard behavior, my apologies. > >>> > >>> > >> Yes. the debate is about column aliases. > >> Still the OP also wished to not call column expressions multiple times. > >> And I had to change your query to make that appear in the query plan. > >> But here is an example very close to yours where b is an alias for > >> random(). > >> I changed thea original where clause (b>2) to b <> b abd I hope it is > >> obvious > >> that the function is caled multiple times for each row. > >> > >> create table c(a); > >> insert into c values(1); > >> insert into c values(2); > >> insert into c values(3); > >> insert into c values(4); > >> select * from (select random() as b from c) where b <> b; > >> 232218896271007264 > >> -1043354911054439855 > >> 5116834959932449572 > >> 7115658816317887453 > >> > > > > Interesting query. So it would seem then that a temp table would be > > required for something like this to avoid calling random three times per > > row. I would have expected *this* version to return no rows. I guess this > > is why we test software. :) > > > A temp table is not required. Another feature of subqueries (apart from > sticking to standard use of aliases) is that you can make them behave as > temp tables. Just by adding some "hint" for the optimizer such as LIMIT > 999999 or -1. > > select * from (select random() as b from c limit -1) where b <> b; > > Much easier than a temp table but not guaranteed to ever remain to work. Thanks for that pointer. -- Scott Robison
