column alias combined with ORDER BY returns wrong data

[monetdb-team]

Date: 2013-05-02 14:17:13 +0200
From: milan <<milan.martak>>
To: SQL devs <>
Version: 11.15.7 (Feb2013-SP2)
CC: @njnes

Last updated: 2013-07-03 08:47:58 +0200

Comment 18704

Date: 2013-05-02 14:17:13 +0200
From: milan <<milan.martak>>

User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:20.0) Gecko/20100101 Firefox/20.0
Build Identifier:

(cannot read version in the list - shows HAS code) so -> i am on MonetDB v11.15.7 (Feb2013-SP2)

when using combination of column aliases with ORDER BY clause, the result set is not correct - it returns repeating value of first column for all columns in the SELECT

see the test cases to reproduce

confirmed as bug by Hannes Muehleisen

Reproducible: Always

Steps to Reproduce:

test case 1, no ORDER BY - correct*
*----------------------------------

 WITH SAWITH0 AS ( select 'a' as c1,
                           'b' as c2,
                           'c' as c3,
                            1 as c4 )
 select  0 as c1,
       D1.c1 as c2,
       D1.c2 as c3,
       D1.c3 as c4,
       D1.c4 as c5
 from SAWITH0 D1

test case 2, add ORDER BY - wrong result (repeats the 1st value for all
columns)
--------------------------------------------------------------------------------

 WITH SAWITH0 AS ( select 'a' as c1,
                           'b' as c2,
                           'c' as c3,
                             1 as c4 )
 select  0 as c1,
       D1.c1 as c2,
       D1.c2 as c3,
       D1.c3 as c4,
       D1.c4 as c5
 from SAWITH0 D1
 order by 5, 4, 3, 2
 --order by c5,c4,c3,c2

**test case 3, use completely different alias with order by - correct **
*-------------------------------------------------------------------

 WITH SAWITH0 AS ( select 'a' as c1,
                           'b' as c2,
                           'c' as c3,
                             1 as c4 )
 select  0 as c1,
       D1.c1 as a2,
       D1.c2 as a3,
       D1.c3 as a4,
       D1.c4 as a5
 from SAWITH0 D1
 order by 5, 4, 3, 2

Actual Results:

+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+======+======+======+======+======+
| 0 | a | b | c | 1 |
+------+------+------+------+------+

+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+======+======+======+======+======+
| 0 | a | a | a | a |
+------+------+------+------+------+
Note that the result is the same even if you use the column alias
instead of the ord.num.

+------+------+------+------+------+
| c1 | a2 | a3 | a4 | a5 |
+======+======+======+======+======+
| 0 | a | b | c | 1 |
+------+------+------+------+------+

Expected Results:

+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+======+======+======+======+======+
| 0 | a | b | c | 1 |
+------+------+------+------+------+

+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+======+======+======+======+======+
| 0 | a | b | c | 1 |
+------+------+------+------+------+

+------+------+------+------+------+
| c1 | a2 | a3 | a4 | a5 |
+======+======+======+======+======+
| 0 | a | b | c | 1 |
+------+------+------+------+------+

Comment 18707

Date: 2013-05-03 09:30:59 +0200
From: milan <<milan.martak>>

Additional testcases, not sure if related to this bug, the worst one (7) can kill the server:

---

Test Case 4 - will emit error

 WITH SAWITH0 AS ( select 2 as c2 ),
       SAWITH1 AS ( select  5 as c2, null as c4
                   UNION ALL
                    select  5 as c2, 'x' as c4  )
 ( select
       cast(NULL as  VARCHAR ( 1 ) ) as c2,
       D1.c2 as c7
     from  SAWITH0 D1
 union all
   select
       D1.c4 as c2,
       D1.c2 as c7
     from  SAWITH1 D1 )

Note: plain UNION will fail too emitting even more errors

---

Test Case 5 - correct (note different alias c200000 instead of c2)

 WITH SAWITH0 AS ( select 2 as c2 ),
      SAWITH1 AS (select  5 as c2, null as c4
                UNION ALL
                select  5 as c2, 'x' as c4  )
 (select
       cast(NULL as  VARCHAR ( 1 ) ) as c200000,
       D1.c2 as c7
 from  SAWITH0 D1
 union all
 select
       D1.c4 as c200000,
       D1.c2 as c7
 from  SAWITH1 D1 )

---

Test Case 6, modification of 4, just one query from the UNION- correct

 WITH SAWITH0 AS ( select 2 as c2 ),
      SAWITH1 AS (select  5 as c2, null as c4
                UNION ALL
                select  5 as c2, 'x' as c4  )
 select
       cast(NULL as  VARCHAR ( 1 ) ) as c2,
       D1.c2 as c7
 from  SAWITH0 D1

---

Test Case 7, add ORDER BY to 6 - error (SIGSEGV)

 WITH SAWITH0 AS ( select 2 as c2 ),
      SAWITH1 AS (select  5 as c2, null as c4
                UNION ALL
                select  5 as c2, 'x' as c4  )
 select
       cast(NULL as  VARCHAR ( 1 ) ) as c2,
       D1.c2 as c7
 from  SAWITH0 D1
 ORDER BY 1

Comment 18712

Date: 2013-05-03 13:07:53 +0200
From: MonetDB Mercurial Repository <>

Changeset bf23e8009dd2 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=bf23e8009dd2

Changeset description:

Added test for bug #3279.
Hand-approved outputs for failing tests--should be checked.

Comment 18785

Date: 2013-06-06 19:26:21 +0200
From: MonetDB Mercurial Repository <>

Changeset 52ed955b70c9 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=52ed955b70c9

Changeset description:

fixed bug #3279 and 3278. We properly look up the order by columns.
And keep table names with all sub-relations.
Fixed bug #3296, ie fixed type checking on table returning functions.

Comment 18788

Date: 2013-06-07 13:32:18 +0200
From: @njnes

fixed