[MCOL-5223] querytstats + LEFT JOIN + LOWER() in ON condition cause "t/tuplehashjoin.cpp@1095: assertion 'idlsz > 1' failed" error Created: 2022-09-19  Updated: 2023-11-14  Resolved: 2023-01-19

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 6.3.1, 6.4.2
Fix Version/s: 22.08.8

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Denis Khalikov
Resolution: Fixed Votes: 0
Labels: querystats

Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

To reproduce:

  create table t1 (c1 varchar(200)) engine = columnstore;
 
  create table t2 (c2 char(20)) engine = columnstore;
 
   create view v1 as select distinct `t1`.`c1` AS `c1` from `t1` where !(`t1`.`c1` in (select `t2`.`c2` from `t2`));
 
   SELECT * from t1 a LEFT JOIN v1 n ON LOWER(n.c1) = LOWER(a.c1);


Sep 19 09:31:37 1e3afaf1eaef Calpont[135]: 37.906020 |0|0|0| E 00 CAL0000: /home/buildbot/buildbot/padding_for_CPACK_RPM_BUILD_SOURCE_DIRS_PREFIX/mariadb-10.6.8/storage/columnstore/columnstore/dbcon/joblist/tuplehashjoin.cpp@1095: assertion 'idlsz > 1' failed

Error occured only if this all 3 conditions is given.

  • Querystats enabled
  • LEFT JOIN instead INNER JOIN
  • LOWER() function in the ON Condition.

So if only querystats is disabled, it works.

If only LEFT JOIN is replaced with INNER JOIN, it works.

If only LOWER() funcions in the ON Condition will be removed, it works.

 Comments   
Comment by alexey vorovich (Inactive) [ 2023-01-05 ]

dleeyh , could you pls confirm

Comment by Daniel Lee (Inactive) [ 2023-01-19 ]

Build verified: 23.02 (Drone build #buildNo: 6523)

MariaDB [mytest]> select calSetTrace(1);
 
+----------------+
 
| calSetTrace(1) |
 
+----------------+
 
|              1 |
 
+----------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [mytest]> create table t1 (c1 varchar(200)) engine = columnstore;
 
Query OK, 0 rows affected (0.144 sec)
 
 
 
MariaDB [mytest]> create table t2 (c2 char(20)) engine = columnstore;
 
Query OK, 0 rows affected (0.127 sec)
 
 
 
MariaDB [mytest]> create view v1 as select distinct `t1`.`c1` AS `c1` from `t1` where !(`t1`.`c1` in (select `t2`.`c2` from `t2`));
 
Query OK, 0 rows affected, 1 warning (0.002 sec)
 
 
 
MariaDB [mytest]> SELECT * from t1 a LEFT JOIN v1 n ON LOWER(n.c1) = LOWER(a.c1);
 
Empty set, 1 warning (0.098 sec)
 
 
 
MariaDB [mytest]> select calGetStats();
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| calGetStats()                                                                                                                                                                            |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-3; CacheI/O-9; BlocksTouched-6; PartitionBlocksEliminated-0; MsgBytesIn-321B; MsgBytesOut-4KB; Mode-Distributed |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)

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