Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Do
-
1.1.0
-
None
Description
This was originally found with tpcds queries 39 and 64. The first invocation of the query will return:
ERROR 1052 (23000): Column 'cnt' in order clause is ambiguous
|
but executing the exact same sql will return the correct results. A third time will have the error and so on.
This only happens for columnstore tables not innodb so it does not appear to be a server bug.
A simpler test case is:
create table o1(c char(3), y int, i int) engine=columnstore;
|
insert into o1 values ('abc',2016, 23), ('abc', 2016, 46), ('def', 2016, 27);
|
insert into o1 values ('abc',2017, 24), ('abc', 2017, 47), ('def', 2017, 28);
|
with c as (select y, c, count(*) cnt, sum(i) sumi from o1 group by y,c)
|
select
|
c1.y,
|
c1.c,
|
c1.cnt,
|
c1.sumi,
|
c2.y,
|
c2.c,
|
c2.cnt,
|
c2.sumi
|
from c c1, c c2
|
where c1.c = c2.c
|
and c1.y = 2016
|
and c2.y = 2017
|
order by c2.cnt;
|
I found a workaround which is adding an alias to the select list for c2.cnt e.g. c2.cnt c2_cnt, makes the query work consistently.