[MCOL-5171] MariaDB Columnstore: subquery cannot be used with NVL Created: 2022-07-27  Updated: 2023-12-01

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

Type: New Feature Priority: Major
Reporter: andreas eschbacher Assignee: Max Mether
Resolution: Unresolved Votes: 1
Labels: None
Environment:

mariadb 10.5.13
Red Hat Enterprise Linux release 8.5
RAM 128GB
8 cpus
single server setup

Attachments: Text File IDB-3016.sql    
Issue Links:
Blocks
blocks MCOL-896 Function or Operator with sub query o... Closed
blocks MCOL-1414 Function or Operator with sub query o... Closed

 Description   

Over a sub-query for the selected columns only an NVL can be done if both affected tables are InnoDB. If one of them is Columnstore, the error:

Error Code: 1178. The storage engine for the table doesn't support IDB-3016: Function or Operator with sub query on the SELECT clause is currently not supported.
occurs.

Error occurs on EBI03 with MariaDB 10.5, Columnstore 5.6.3.

How to reproduce:

comment out the lines with "detailNameWorks"
delete the comments on the lines with "detailNameError"

 Comments   
Comment by Allen Herrera [ 2023-06-12 ]

Explicitly call out the join for the query to work

SELECT
 
a.a_ID as ID,
 
NVL(a.someName,'NICHT VORHANDEN') AS someName,
 
NVL(( d.detailName ), 'NA') AS detailNameError
 
FROM test_a_cs a
 
join test_b_cs d on a.a_ID= d.FK_ID;

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