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.
