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. :) -- Scott Robison
