[MCOL-5040] COALESCE and DAYOFYEAR nesting in outer join leads to wrong output in Columnstore Created: 2022-04-05  Updated: 2023-11-17

Status: Confirmed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 6.2.3, 23.02.3, 23.10.0, 5.6.8
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: andreas eschbacher Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None
Environment:

ebi01:
CentOS Linux release 7.6.1810
10.3.16-MariaDB
Columnstore 1.2.5-1

ebi04:
Red Hat Enterprise Linux release 8.2
10.5.10-MariaDB MariaDB Server
Columnstore_version 5.5.2

Attachments: PNG File EBI-499_Columnstore_bug.png     PNG File EBI-499_InnoDB_works.png     File EBI-499_bug_exposal_dummy_data.sql     File EBI-499_simplified.sql    

 Description   

See the attached SQL EBI-499_bug_exposal_dummy.sql: the lines with "!!!" expose the bug when tested against dummy data tables on both EBI01 and EBI04 (MariaDB 10.3 and 10.5).
The problem are the MAX_INT values that must not occur when using COALESCE in the green boxes (and do not occur using InnoDB engine).

TODOs:

  • check whether only Columnstore or also InnoDB is affected ==> DONE: only Columnstore
  • simplify the bug exposal by using dummy data ==> DONE, see attached
  • check if outer join or also inner join is affected ==> reduced to outer join (example makes no sense for inner join)
  • check which MariaDB Version is affected ==> DONE, also 10.5 (on EBI04) is affected, same behaviour as for 10.3 (EBI01).
Generated at Thu Feb 08 02:54:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.