[MCOL-4942] Different results compared to innodb with union all and subselect with null in select and impossible where condition Created: 2021-12-09  Updated: 2022-01-05

Status: Open
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 6.4.1
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-4937 Union ALL with wrapped selects -Groub... Closed

 Description   

Columnstore throws rows with union all and subselect with null in select and impossible where condition

To reproduce:

drop table if exists t1col;
drop table if exists t1inno;
 
create table t1col (id int) engine=columnstore;
create table t1inno (id int) engine=innodb;
 
select * from
(
Select ID
FROM
(
SELECT 1 ID
FROM 
t1col
) V
UNION ALL
SELECT ID
FROM
(
SELECT NULL ID WHERE 1=0
) V
) U;
 
 
 
select * from
(
Select ID
FROM
(
SELECT 1 ID
FROM 
t1inno
) V
UNION ALL
SELECT ID
FROM
(
SELECT NULL ID WHERE 1=0
) V
) U;
 

Result with innodb table:

Empty set (0.001 sec)

Result with Columnstore table:

------

ID

------

NULL

------
1 row in set (0.043 sec)

Columnstore throws the same result like innodb, if

  • change "where 1= 0" to "where 1=1" (or without where condition)
  • change SELECT NULL ID to SELECT 1 ID
  • Remove the outer wrapper "select * from (...."

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