column alias combined with ORDER BY returns wrong data
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 | +------+------+------+------+------+
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
I think this may be the same issue that I reported here:
http://bugs.monetdb.org/show_bug.cgi?id=3278
On 2 May 2013 08:17, Hannes Mühleisen
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
*bug #3279* http://bugs.monetdb.org/show_bug.cgi?id=3279 created m. 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 .
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 .
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
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
participants (4)
-
Budulinku Dejmihrasku -
Hannes Mühleisen -
Martin Kersten -
Pete Hollobon