[MCOL-5311] timestamp selected from CTE that uses a columnstore join intermitantly returns zero date Created: 2022-11-17  Updated: 2022-12-28  Resolved: 2022-12-12

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 6.4.6
Fix Version/s: 22.08.7

Type: Bug Priority: Critical
Reporter: George Birnbaum Assignee: David Hall (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

columnstore 1:10.7.7-6.4.6+maria~ubu2004
mariadb Ver 15.1 Distrib 10.7.6-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Attachments: PNG File Screenshot from 2022-11-17 13-45-57.png     PNG File Screenshot from 2022-11-17 13-46-11.png     File query-example.sql     File schema-setup.sql    
Assigned for Review: Gagan Goel Gagan Goel (Inactive)
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

Current:
as a user selecting a timestamp from a CTE, which is composed of an innodb table and a columnstore table joined together, the resulting select data yields a zero date stamp intermittently.

Expectation:
as a user selecting a timestamp from a CTE, which is composed of an innodb table and a columnstore table joined together, the resulting select data should include a valid timestamp.

Notes
Attached is a schema setup query that includes some clear sample data. The second file is my sample query that reproduces the bug. As a user, I can execute the select used for the CTE without issue. As a user executing the select from the cte, I get the bad data out on and off.

Im also attaching timestamped screenshots showing where the query results are with timestamp and then immediately show the zero timestamp. Mariadb version and columnstore version noted.

Please let me know if you need additional details.

Best,
George

 Comments   
Comment by George Birnbaum [ 2022-11-18 ]

To add to this, Im experiencing the issue when creating a view of the CTE select and then using that for my over all query.

Comment by Kathryn Sizemore [ 2022-12-06 ]

@David Hall , FYI, I've run this example 200 times with 22.08.4 and the 0000 date only appeared once on my M1 laptop. You might have to run it with different builds. Please let us know if you aren't able to reproduce.

Comment by David Hall (Inactive) [ 2022-12-06 ]

I also get consistent good results (Rocky 8, 20.08.4)
I notice the complaint is ubuntu 20.04 and columnstore 6.4.6. I will set this up and try again.

Comment by David Hall (Inactive) [ 2022-12-06 ]

Consistency is relative. After some large number of runs, I get incorrect results on 22.08.4:

MariaDB [test]> with audit_keywords as (     select sp_keyword_list.id,            sp_keyword_list.created_at,            asrr.record_id as other_id,     asrr.report_date     from sp_keyword_list              inner join sponsored_products_14d_metrics asrr on sp_keyword_list.record_id = asrr.record_id     where          sp_keyword_list.id = 1 ) select * from audit_keywords;
 
+----+---------------------+-----------------+-------------+
 
| id | created_at          | other_id        | report_date |
 
+----+---------------------+-----------------+-------------+
 
|  1 | 0000-00-00 00:00:00 | 106824737247411 | 2022-11-04  |
 
|  1 | 0000-00-00 00:00:00 | 106824737247411 | 2022-11-05  |
 
|  1 | 0000-00-00 00:00:00 | 106824737247411 | 2022-11-06  |
 
|  1 | 0000-00-00 00:00:00 | 106824737247411 | 2022-11-07  |
 
|  1 | 0000-00-00 00:00:00 | 106824737247411 | 2022-11-08  |
 
+----+---------------------+-----------------+-------------+
 
5 rows in set (0.019 sec)

Comment by Gagan Goel (Inactive) [ 2022-12-07 ]

dleeyh Please refer to the ticket description and David's comment from yesterday to reproduce the bug. Target platform is Ubuntu 20.04. Fix is just now merged to develop.

Comment by Daniel Lee (Inactive) [ 2022-12-12 ]

Build verified: 22.08.78 (latest build in develop)

engine: e243a5332b8613ce0e370a503461990fefc24fce
server: d3049350bb5c61340f5a7518b155d3c9dacdcb33
buildNo: 6202

Verified test case in both Rocky8 and ubuntu20.04.

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