Re: column alias combined with ORDER BY returns wrong data

3 May 2013


      i take this as a yes and i choose to interpret the yes as a hint to add 
this to the existing bug ;)
m

On 5/2/2013 9:25 PM, Martin Kersten wrote:
...
Hi
Sometimes bugs are like ants, they come in groups and any concise 
collection
of test cases to improve the code are indeed highly appreciated.
regards, Martin Kersten
On 5/2/13 8:47 PM, Budulinku Dejmihrasku wrote:
...
Hi,
since you like my test cases i have another few for you - to make 
sure its the same problem, although i think it gets worse...
originally we started with much more complex query but after some 
cleansing it boiled down to similar issue - i.e. the alias seems to 
be the culprit now, slightly different behavior though... see the 
testcase 7, the worst of them all which produces "killed
by signal SIGSEGV"
shall i add this to the same bug or create a new one?
pls advice
thanks
milan
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 BYto #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
On 5/2/2013 9:17 AM, Hannes Mühleisen wrote:
...
Hello Milan,
thank you for your bug report and easy-to-reproduce test cases. I 
have confirmed this issue, it is also present in the current (47544) 
'default' development branch. Would you be so nice and open a bug 
report at http://bugs.monetdb.org/ ?
Thanks,
Hannes
On 02/05/13 01:25, Budulinku Dejmihrasku wrote:
...
Hi,
please note the test cases below - it should be self-explanatory
i am on MonetDB v11.15.7 (Feb2013-SP2), i run the test before the
upgrade with the same results
please advice
thanks a lot
milan
*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
+------+------+------+------+------+
| c1   | c2   | c3   | c4   | c5   |
+======+======+======+======+======+
|    0 | a    | b    | c    |    1 |
+------+------+------+------+------+
*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
+------+------+------+------+------+
| 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.
*
**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
+------+------+------+------+------+
| c1   | a2   | a3   | a4   | a5   |
+======+======+======+======+======+
|    0 | a    | b    | c    |    1 |
+------+------+------+------+------+
_______________________________________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list
.
_______________________________________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list