Bug 6799 - allow using alias names in expressions in ORDER BY clause
Summary: allow using alias names in expressions in ORDER BY clause
Status: RESOLVED WONTFIX
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.35.3 (Nov2019)
Hardware: All Linux
: Normal normal
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-12-11 18:57 CET by Martin van Dinther
Modified: 2020-09-21 20:25 CEST (History)
2 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther cwiconfidential 2019-12-11 18:57:22 CET
User-Agent:       Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:70.0) Gecko/20100101 Firefox/70.0
Build Identifier: 

You can use expressions in the order by, but when referencing an alias instead of a column name, it fails with identifier 'alias1' unknown.
It should be allowed to use the alias names in expressions in ORDER BY.

Reproducible: Always

Steps to Reproduce:
CREATE TABLE obale (nm VARCHAR(99) );
INSERT INTO obale VALUES ('a'), ('b'), ('b'), (NULL);
SELECT nm FROM obale ORDER BY 1 desc;
SELECT nm FROM obale ORDER BY upper(nm);
SELECT nm FROM obale ORDER BY nm || nm;
SELECT nm as alias1 FROM obale ORDER BY alias1, nm;    -- no problemo
SELECT nm as alias1 FROM obale ORDER BY upper(alias1);  -- retuns error: SELECT: identifier 'alias1' unknown
SELECT nm as alias1 FROM obale ORDER BY nm || alias1;  -- retuns error: SELECT: identifier 'alias1' unknown
SELECT nm, upper(nm) as alias1 FROM obale ORDER BY alias1;    -- no problemo
SELECT nm, nm||nm as alias1 FROM obale ORDER BY alias1;    -- no problemo
SELECT nm, COUNT(nm) countnm, COUNT(DISTINCT nm) countdnm FROM obale GROUP BY nm ORDER BY countnm desc, countdnm;    -- no problemo
SELECT nm, COUNT(nm) countnm, COUNT(DISTINCT nm) countdnm FROM obale GROUP BY nm ORDER BY countdnm - countnm;  -- retuns error: SELECT: identifier 'countdnm' unknown
DROP TABLE obale;


Actual Results:  
sql>CREATE TABLE obale (nm VARCHAR(99) );
operation successful
sql>INSERT INTO obale VALUES ('a'), ('b'), ('b'), (NULL);
4 affected rows
sql>SELECT nm FROM obale ORDER BY 1 desc;
+------+
| nm   |
+======+
| b    |
| b    |
| a    |
| null |
+------+
4 tuples
sql>SELECT nm FROM obale ORDER BY upper(nm);
+------+
| nm   |
+======+
| null |
| a    |
| b    |
| b    |
+------+
4 tuples
sql>SELECT nm FROM obale ORDER BY nm || nm;
+------+
| nm   |
+======+
| null |
| a    |
| b    |
| b    |
+------+
4 tuples
sql>SELECT nm as alias1 FROM obale ORDER BY alias1, nm;    -- no problemo
+--------+
| alias1 |
+========+
| null   |
| a      |
| b      |
| b      |
+--------+
4 tuples
sql>SELECT nm as alias1 FROM obale ORDER BY upper(alias1);  -- retuns error: SELECT: identifier 'alias1' unknown
SELECT: identifier 'alias1' unknown
sql>SELECT nm as alias1 FROM obale ORDER BY nm || alias1;  -- retuns error: SELECT: identifier 'alias1' unknown
SELECT: identifier 'alias1' unknown
sql>SELECT nm, upper(nm) as alias1 FROM obale ORDER BY alias1;    -- no problemo
+------+--------+
| nm   | alias1 |
+======+========+
| null | null   |
| a    | A      |
| b    | B      |
| b    | B      |
+------+--------+
4 tuples
sql>SELECT nm, nm||nm as alias1 FROM obale ORDER BY alias1;    -- no problemo
+------+--------+
| nm   | alias1 |
+======+========+
| null | null   |
| a    | aa     |
| b    | bb     |
| b    | bb     |
+------+--------+
4 tuples
sql>SELECT nm, COUNT(nm) countnm, COUNT(DISTINCT nm) countdnm FROM obale GROUP BY nm ORDER BY countnm desc, countdnm;    -- no problemo
+------+---------+----------+
| nm   | countnm | countdnm |
+======+=========+==========+
| b    |       2 |        1 |
| a    |       1 |        1 |
| null |       0 |        0 |
+------+---------+----------+
3 tuples
sql>SELECT nm, COUNT(nm) countnm, COUNT(DISTINCT nm) countdnm FROM obale GROUP BY nm ORDER BY countdnm - countnm;  -- retuns error: SELECT: identifier 'countdnm' unknown
SELECT: identifier 'countdnm' unknown
sql>DROP TABLE obale;
operation successful


Expected Results:  
sql>CREATE TABLE obale (nm VARCHAR(99) );
operation successful
sql>INSERT INTO obale VALUES ('a'), ('b'), ('b'), (NULL);
4 affected rows
sql>SELECT nm FROM obale ORDER BY 1 desc;
+------+
| nm   |
+======+
| b    |
| b    |
| a    |
| null |
+------+
4 tuples
sql>SELECT nm FROM obale ORDER BY upper(nm);
+------+
| nm   |
+======+
| null |
| a    |
| b    |
| b    |
+------+
4 tuples
sql>SELECT nm FROM obale ORDER BY nm || nm;
+------+
| nm   |
+======+
| null |
| a    |
| b    |
| b    |
+------+
4 tuples
sql>SELECT nm as alias1 FROM obale ORDER BY alias1, nm;    -- no problemo
+--------+
| alias1 |
+========+
| null   |
| a      |
| b      |
| b      |
+--------+
4 tuples
sql>SELECT nm as alias1 FROM obale ORDER BY upper(alias1);  -- retuns error: SELECT: identifier 'alias1' unknown
+--------+
| alias1 |
+========+
| null   |
| a      |
| b      |
| b      |
+--------+
4 tuples
sql>SELECT nm as alias1 FROM obale ORDER BY nm || alias1;  -- retuns error: SELECT: identifier 'alias1' unknown
+--------+
| alias1 |
+========+
| null   |
| a      |
| b      |
| b      |
+--------+
4 tuples
sql>SELECT nm, upper(nm) as alias1 FROM obale ORDER BY alias1;    -- no problemo
+------+--------+
| nm   | alias1 |
+======+========+
| null | null   |
| a    | A      |
| b    | B      |
| b    | B      |
+------+--------+
4 tuples
sql>SELECT nm, nm||nm as alias1 FROM obale ORDER BY alias1;    -- no problemo
+------+--------+
| nm   | alias1 |
+======+========+
| null | null   |
| a    | aa     |
| b    | bb     |
| b    | bb     |
+------+--------+
4 tuples
sql>SELECT nm, COUNT(nm) countnm, COUNT(DISTINCT nm) countdnm FROM obale GROUP BY nm ORDER BY countnm desc, countdnm;    -- no problemo
+------+---------+----------+
| nm   | countnm | countdnm |
+======+=========+==========+
| b    |       2 |        1 |
| a    |       1 |        1 |
| null |       0 |        0 |
+------+---------+----------+
3 tuples
sql>SELECT nm, COUNT(nm) countnm, COUNT(DISTINCT nm) countdnm FROM obale GROUP BY nm ORDER BY countdnm - countnm;  -- retuns error: SELECT: identifier 'countdnm' unknown
+------+---------+----------+
| nm   | countnm | countdnm |
+======+=========+==========+
| null |       0 |        0 |
| a    |       1 |        1 |
| b    |       2 |        1 |
+------+---------+----------+
3 tuples
sql>DROP TABLE obale;
operation successful
Comment 1 Pedro Ferreira 2019-12-12 10:06:36 CET
This is a non-standard extension that some databases implement. I'm not sure if we should do it as well.
Comment 2 MonetDB Mercurial Repository cwiconfidential 2019-12-12 14:50:31 CET
Changeset aaa89146c3da, made by Martin van Dinther <martin.van.dinther@monetdbsolutions.com> in the MonetDB repo, refers to this bug.

For complete details, see https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=aaa89146c3da

Changeset description:

	Add test for bug 6799
Comment 3 Niels Nes cwiconfidential 2020-09-21 20:25:59 CEST
we only allow direct alias us in the order by, ie not within some new expression.