use column alias in same SELECT

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

use column alias in same SELECT

Thomas Kurz
Dear all,

may I ask whether there is a deeper reason why using a column alias in the same SELECT query doesn't work, e.g.

SELECT column1 AS a, 5*column2 AS b, a+b FROM.......

This is not an SQlite issue, it doesn't work in MariaDB either. It would, however, be very handy if it worked. Sometimes I have complicated sub-expressions (calculations or sub-queries) which I need to re-use. Currently, as a workaround, I do something like this:

SELECT a, b, a+b FROM (SELECT column1 AS a, 5*column2 AS b FROM .....)

But nested SELECTs don't make things clearer.

Thanks,
Thomas

_______________________________________________
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: use column alias in same SELECT

Simon Slavin-3
On 31 Oct 2018, at 7:14pm, Thomas Kurz <[hidden email]> wrote:

> may I ask whether there is a deeper reason why using a column alias in the same SELECT query doesn't work, e.g.
>
> SELECT column1 AS a, 5*column2 AS b, a+b FROM.......

The canonical answer to this is that there's nothing in the SQL specification that says the columns have to be evaluated in left-to-right order.  Theoretically a computer which had massive parallelism could fetch the data from the database, then work out all the columns at the same time.

Simon.
_______________________________________________
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: use column alias in same SELECT

R Smith-2

On 2018/10/31 9:21 PM, Simon Slavin wrote:
> On 31 Oct 2018, at 7:14pm, Thomas Kurz <[hidden email]> wrote:
>
>> may I ask whether there is a deeper reason why using a column alias in the same SELECT query doesn't work, e.g.
>>
>> SELECT column1 AS a, 5*column2 AS b, a+b FROM.......
> The canonical answer to this is that there's nothing in the SQL specification that says the columns have to be evaluated in left-to-right order.  Theoretically a computer which had massive parallelism could fetch the data from the database, then work out all the columns at the same time.

Simon is correct, and to add to it with some bit of expansion (I assume
you asked because you are interested in the answer):

The rules for identifiers used in expressions are very strict (it's math
after all), and the rules for aliases are by design very loose.  Set
algebra further imposes no order on items. If a set contains A, B and
C... and you asked for everything in the set, you can receive it in any
order. (Most DB engines stick to the table-template order as a courtesy,
but they are not forced to do so).

To explain the strictness problem, imagine a query like

SELECT C+A as x, B+A as 'x', C+B as x FROM t

Those are all perfectly valid Aliases - but you can see how it will
never conform to the strict expression identifier rules. You cannot for
instance write a simple Algebraic expression that would explain what
value the added x2 column will hold if that query was adjusted to be:

SELECT C+A as x, B+A as 'x', C+B as x, x+x as x2 FROM t

Further, as Simon mentioned, you cannot be be sure that x+x won't (try
to) be evaluated before C+A for the same query.

"But..." I hear you ask, "can we not just use the Aliases if they do
conform? and can we not simply force left to right evaluation?"

Sure we can, but the price we pay in losing parallel processing,
multi-threading, and the stupendous inconvenience of either having to
use only strict aliases, or worse, having to code verification of
aliases as suitable expression identifiers in the same query level, is a
disaster in efficiency (and possibly mathematical integrity).

Incidentally, when the query evaluated, any aliases you've used may well
be referenced from the next level (outer) query, and here we do check
suitability. Doing this:

SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as x);
will simply result in an "Ambiguous column x" error, while:

SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as y);
will work perfectly.


Note also that most DB engines evaluate ORDER BY sorting after any other
Query parts, almost like a higher level outer query, and as such do
allow using a column aliases in the ORDER BY clause. I'm not sure that
SQLite does though, but it surely allows column indexing in the ORDER BY:

SELECT X*2, Y+7 FROM t ORDER BY 2,1;

is equivalent to the more convoluted:

SELECT X*2, Y+7 FROM t ORDER BY Y+7, X*2;



Cheers,
Ryan

PS: Pardon the verbose post - Once when I was young and wanted to change
the way the World programs, I had questions exactly like the above, and
it took me a lot longer to figure out the truth than reading a forum
post - so this is for all the younger-me's out there.  :)


_______________________________________________
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: use column alias in same SELECT

Thomas Kurz
Thank you very much for the detailed answer. I now understand things better and can better accept that they are how they are :-))

----- Original Message -----
From: R Smith <[hidden email]>
To: [hidden email] <[hidden email]>
Sent: Wednesday, October 31, 2018, 21:34:30
Subject: [sqlite] use column alias in same SELECT


On 2018/10/31 9:21 PM, Simon Slavin wrote:
> On 31 Oct 2018, at 7:14pm, Thomas Kurz <[hidden email]> wrote:

>> may I ask whether there is a deeper reason why using a column alias in the same SELECT query doesn't work, e.g.

>> SELECT column1 AS a, 5*column2 AS b, a+b FROM.......
> The canonical answer to this is that there's nothing in the SQL specification that says the columns have to be evaluated in left-to-right order.  Theoretically a computer which had massive parallelism could fetch the data from the database, then work out all the columns at the same time.

Simon is correct, and to add to it with some bit of expansion (I assume
you asked because you are interested in the answer):

The rules for identifiers used in expressions are very strict (it's math
after all), and the rules for aliases are by design very loose.  Set
algebra further imposes no order on items. If a set contains A, B and
C... and you asked for everything in the set, you can receive it in any
order. (Most DB engines stick to the table-template order as a courtesy,
but they are not forced to do so).

To explain the strictness problem, imagine a query like

SELECT C+A as x, B+A as 'x', C+B as x FROM t

Those are all perfectly valid Aliases - but you can see how it will
never conform to the strict expression identifier rules. You cannot for
instance write a simple Algebraic expression that would explain what
value the added x2 column will hold if that query was adjusted to be:

SELECT C+A as x, B+A as 'x', C+B as x, x+x as x2 FROM t

Further, as Simon mentioned, you cannot be be sure that x+x won't (try
to) be evaluated before C+A for the same query.

"But..." I hear you ask, "can we not just use the Aliases if they do
conform? and can we not simply force left to right evaluation?"

Sure we can, but the price we pay in losing parallel processing,
multi-threading, and the stupendous inconvenience of either having to
use only strict aliases, or worse, having to code verification of
aliases as suitable expression identifiers in the same query level, is a
disaster in efficiency (and possibly mathematical integrity).

Incidentally, when the query evaluated, any aliases you've used may well
be referenced from the next level (outer) query, and here we do check
suitability. Doing this:

SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as x);
will simply result in an "Ambiguous column x" error, while:

SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as y);
will work perfectly.


Note also that most DB engines evaluate ORDER BY sorting after any other
Query parts, almost like a higher level outer query, and as such do
allow using a column aliases in the ORDER BY clause. I'm not sure that
SQLite does though, but it surely allows column indexing in the ORDER BY:

SELECT X*2, Y+7 FROM t ORDER BY 2,1;

is equivalent to the more convoluted:

SELECT X*2, Y+7 FROM t ORDER BY Y+7, X*2;



Cheers,
Ryan

PS: Pardon the verbose post - Once when I was young and wanted to change
the way the World programs, I had questions exactly like the above, and
it took me a lot longer to figure out the truth than reading a forum
post - so this is for all the younger-me's out there.  :)


_______________________________________________
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