Using computed column once in SELECT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Using computed column once in SELECT

Tony Papadimitriou
I don’t know if this has come up before.

Example:

select cast((julianday('now')-julianday(dob))/365.25 as int) age,
       87-cast((julianday('now')-julianday(dob))/365.25 as int) life_expectancy

(... assuming 87 year average life span)

This works, but as you see the age calculation has to be repeated in every column that needs it (and there could many more).

Why not be able to this instead?

select cast((julianday('now')-julianday(dob))/365.25 as int) age,
       87-age life_expectancy

Note that age is defined before it is referenced.

Apparently, ‘age’ can be used in a subsequent join but not a subsequent column definition.
Is there a technical limitation for this or simply an unimplemented feature?

BTW, is ‘now’ value locked during the query execution to avoid the possibility (however small) of two columns ending up with different age calculations (e.g., running during date crossover on someone’s birthday)?

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using computed column once in SELECT

petern
CTE's can give you the dose of syntactic sugar you're craving:
[No, you can reference other column definitions within the same SELECT
statement.]

WITH w_age_col AS (SELECT *,CAST((julianday('now')-julianday(dob))/365.25
AS INT)age FROM the_table)
SELECT *, age,87-age life_expectancy FROM w_age_col;




On Thu, Nov 23, 2017 at 2:17 PM, Tony Papadimitriou <[hidden email]> wrote:

> I don’t know if this has come up before.
>
> Example:
>
> select cast((julianday('now')-julianday(dob))/365.25 as int) age,
>        87-cast((julianday('now')-julianday(dob))/365.25 as int)
> life_expectancy
>
> (... assuming 87 year average life span)
>
> This works, but as you see the age calculation has to be repeated in every
> column that needs it (and there could many more).
>
> Why not be able to this instead?
>
> select cast((julianday('now')-julianday(dob))/365.25 as int) age,
>        87-age life_expectancy
>
> Note that age is defined before it is referenced.
>
> Apparently, ‘age’ can be used in a subsequent join but not a subsequent
> column definition.
> Is there a technical limitation for this or simply an unimplemented
> feature?
>
> BTW, is ‘now’ value locked during the query execution to avoid the
> possibility (however small) of two columns ending up with different age
> calculations (e.g., running during date crossover on someone’s birthday)?
>
> Thanks.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using computed column once in SELECT

Keith Medcalf
In reply to this post by Tony Papadimitriou

>BTW, is ‘now’ value locked during the query execution to avoid the
>possibility (however small) of two columns ending up with different
>age calculations (e.g., running during date crossover on someone’s
>birthday)?

By default, 'now' is step stable (that is, it will return the same value for all usage within the same step of a statement) -- ie, for each row.  

You can make it statement stable with a small patch to sqlite3VdbeExec and compiling with -DSQLITE_NOW_STABILITY_STMT.  This will cause the
p->iCurrentTime to be reset ONLY when step 0 of the VDBE program is executed, rather than on each step.

  assert( p->rc==SQLITE_OK || (p->rc&0xff)==SQLITE_BUSY );
  assert( p->bIsReader || p->readOnly!=0 );
#ifdef SQLITE_NOW_STABILITY_STMT /* INSERT */
  if (p->pc == 0) /* INSERT */
#endif /* INSERT */
  p->iCurrentTime = 0;

I believe that since the addition of the SLOCHG option the value of 'now' is statement stable by default (ie, it is deterministic for the purposes of indexes but not for the query planner), however I still have the above suspenders in place just to be sure it is statement stable.

Since the time is cached in the VDBE it can only be made statement stable (all steps in the same statement), not transaction stable (all statements in the same transaction).  You would have to write your own User-Defined-Function that attaches to the commit/rollback hooks to have the 'now' be transaction stable if that was required.





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users