[MCOL-5539] Columnstore query returns wrong count (3 table join) Created: 2023-07-22  Updated: 2023-10-04  Resolved: 2023-09-06

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: None
Fix Version/s: 23.10.0

Type: Bug Priority: Critical
Reporter: Edward Stoever Assignee: Denis Khalikov
Resolution: Fixed Votes: 0
Labels: triage

Sprint: 2023-8, 2023-10
Assigned for Review: Sergey Zefirov Sergey Zefirov
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

To replicate this issue, download the file CS162.tar.gz (360MB) from Support shared google drive, subdirectory CS0586162. You will need at least10G free space to make this happen. Follow these steps:

root@logs:~/CS0586162/run$ tar xvfz /tmp/CS162.tar.gz
 
root@logs:~/CS0586162/run$ ls -l ./*
 
-rw-r--r-- 1 root root       2898 Jul 22 03:52 ./cs_162.sql
 
-rw-r--r-- 1 root root        908 Jul 22 03:17 ./cs_queries.sql
 
-rw-r--r-- 1 root root 3581066458 Jul 22 01:56 ./inno_162.sql
 
-rw-r--r-- 1 root root        910 Jul 22 03:17 ./inno_queries.sql
 
root@logs:~/CS0586162/run$ mariadb < inno_162.sql # will create schema "inno" with innodb tables
 
root@logs:~/CS0586162/run$ mariadb < cs_162.sql # will create schema "cs" with columnstore tables, and load from "inno" schema using cpimport

Now, using the provided scripts, you can compare the results of the same queries on "inno" tables with queries on "cs" tables. You will see that the final query returns 111 rows on "inno" tables and 10 rows on "cs" tables, even though the data is identical.

root@logs:~/CS0586162/run$ mariadb -v -Ae "source inno_queries.sql"
 
+----------+
 
| count(*) |
 
+----------+
 
|      111 |
 
+----------+
 
root@logs:~/CS0586162/run$ mariadb -v -Ae "source cs_queries.sql"
 
+----------+
 
| count(*) |
 
+----------+
 
|       10 |
 
+----------+



 Comments   
Comment by Daniel Lee (Inactive) [ 2023-08-09 ]

Build tested: develop branch, latest

engine: f2affca162232063481a2f6189db3ab08c28cfaa
server: 62d6100a913699fec9ff48284a76bfe6226e70bc
buildNo: 8368

Confirmed the reported issue

[rocky8:root@rocky8~]# mariadb inno < /data/qa/shares/inno_queries.sql 
COUNT_LINKING_PAIR
 
100001
 
COUNT_LP_MEDICAL_UNIT_105330235
 
1
 
COUNT_MDCDM_BILL_LINE_CUR
 
144000000
 
COUNT_MBLC_2020_105330235
 
111
 
COUNT_LU_DRG
 
797
 
count(*)
 
111
 
[rocky8:root@rocky8~]# mariadb cs < /data/qa/shares/cs_queries.sql 
COUNT_LINKING_PAIR
 
100001
 
COUNT_LP_MEDICAL_UNIT_105330235
 
1
 
COUNT_MDCDM_BILL_LINE_CUR
 
144000000
 
COUNT_MBLC_2020_105330235
 
111
 
COUNT_LU_DRG
 
797
 
count(*)
 
10

Comment by Daniel Lee (Inactive) [ 2023-09-06 ]

Build verified: develop branch, build 8569

engine: 931f2b36a104b002161feaa263effe3c4bc2e619
server: 2056532dcd0a8a65d594300ea7e6a5aca4dcc21c
buildNo: 8569

[rocky8:root@rocky8~]# mariadb inno < /data/qa/shares/inno_queries.sql 
COUNT_LINKING_PAIR
 
100001
 
COUNT_LP_MEDICAL_UNIT_105330235
 
1
 
COUNT_MDCDM_BILL_LINE_CUR
 
144000000
 
COUNT_MBLC_2020_105330235
 
111
 
COUNT_LU_DRG
 
797
 
count(*)
 
111
 
[rocky8:root@rocky8~]# mariadb cs < /data/qa/shares/cs_queries.sql 
COUNT_LINKING_PAIR
 
100001
 
COUNT_LP_MEDICAL_UNIT_105330235
 
1
 
COUNT_MDCDM_BILL_LINE_CUR
 
144000000
 
COUNT_MBLC_2020_105330235
 
111
 
COUNT_LU_DRG
 
797
 
count(*)
 
111

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