[MCOL-957] with cte self join query get alternating ambiguous column reference on repeat invocations Created: 2017-10-04  Updated: 2022-11-05  Resolved: 2022-11-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.1.0
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: David Thompson (Inactive) Assignee: Unassigned
Resolution: Won't Do Votes: 0
Labels: None

Epic Link: ColumnStore Compatibility Improvements

 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.



 Comments   
Comment by Todd Stoffel (Inactive) [ 2022-11-05 ]

This item is being closed because it was well passed the expiration date with no activity. If you suspect this was done in error please create a new ticket.

Generated at Thu Feb 08 02:25:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.