[MCOL-4334] Procedure Hanging When Using Columnstore Table Created: 2020-10-02  Updated: 2020-10-13  Resolved: 2020-10-12

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

Type: Bug Priority: Major
Reporter: Todd Stoffel (Inactive) Assignee: David Hall (Inactive)
Resolution: Fixed Votes: 0
Labels: CustomerRequest


 Description   

When running the following command outside of a procedure, the query completes fine:

CREATE OR REPLACE TEMPORARY TABLE tmp_global_localdes AS
 
SELECT mcc.ref_cd,mcc.REF_CD_TYP, mcc.ref_cd_desc
 
FROM gistgt.ref_cds1 mcc,
 
(SELECT ref_cd,ref_cd_typ, max(updt_dt) updt_dt
 
FROM gistgt.ref_cds1
 
WHERE updt_dt <= NAME_CONST('p_BusinessDt',TIMESTAMP'2020-08-23 00:00:00')
 
GROUP BY ref_cd,ref_cd_typ) cc
 
WHERE mcc.ref_cd_typ = cc.ref_cd_typ
 
AND mcc.ref_cd = cc.ref_cd
 
AND mcc.updt_dt = cc.updt_dt;

However when wrapped in a procedure it hangs on "Creating sort index":

DELIMITER $$
 
CREATE OR REPLACE PROCEDURE PopulateLocalDesc_test1 (p_BusinessDt DATE)
 
BEGIN
 
 
 
CREATE OR REPLACE TEMPORARY TABLE tmp_global_localdes AS
 
SELECT mcc.ref_cd,mcc.REF_CD_TYP, mcc.ref_cd_desc
 
FROM gistgt.ref_cds1 mcc,
 
(SELECT ref_cd,ref_cd_typ, max(updt_dt) updt_dt
 
FROM gistgt.ref_cds1
 
WHERE updt_dt <= p_BusinessDt
 
GROUP BY ref_cd,ref_cd_typ) cc
 
WHERE mcc.ref_cd_typ = cc.ref_cd_typ
 
AND mcc.ref_cd = cc.ref_cd
 
AND mcc.updt_dt = cc.updt_dt;
 
 
 
END $$


CALL PopulateLocalDesc_test1('20200823');

In order for this issue to manifest, the table must contain millions of records. A small test of a few mock data rows will not suffice.

CREATE DATABASE `gistgt`;
 
 
 
USE `gistgt`;
 
 
 
CREATE TABLE `ref_cds1` (
 
  `ref_cd` varchar(50) NOT NULL,
 
  `ref_cd_typ` varchar(30) NOT NULL,
 
  `updt_dt` datetime NOT NULL,
 
  `ref_cd_desc` varchar(512) DEFAULT NULL,
 
  `cntry_cd` varchar(3) DEFAULT 'SG'
 
) ENGINE=Columnstore DEFAULT CHARSET=latin1;



 Comments   
Comment by David Hall (Inactive) [ 2020-10-09 ]

This requires at least a 16GB VM
Using the create table above, you can insert records using this:
insert into ref_cds1 select l_comment, l_shipinstruct, l_shipdate, l_shipmode, l_returnflag from mytest.lineitem;
Then the above query and Stored Procedure should have stuff to work with.

Comment by Daniel Lee (Inactive) [ 2020-10-09 ]

Build tested: 5.4.3-1 ( Drone custom build #905 )
Tested on a Centos 8 VM with 18gb of memory

It look good.

Test #1. Table with 6,001,215 rows:

query time:

Query OK, 5094623 rows affected (36.201 sec)
Records: 5094623 Duplicates: 0 Warnings: 0

Stored Procedure time:

MariaDB [gistgt]> CALL PopulateLocalDesc_test1('20200823');
Query OK, 5094623 rows affected (35.923 sec)

Test #2. Table with 24,004,860 rows:

query time:

Query OK, 20378492 rows affected (2 min 10.834 sec)
Records: 20378492 Duplicates: 0 Warnings: 0

Stored Procedure time:

MariaDB [gistgt]> CALL PopulateLocalDesc_test1('20200823');
Query OK, 20378492 rows affected (2 min 7.111 sec)

Comment by Roman [ 2020-10-12 ]

Plz review.

Comment by Gagan Goel (Inactive) [ 2020-10-12 ]

David.Hall I have merged the PR to develop-1.5 for your testing.

drrtuy I have added a minor comment for the develop-1.5 PR. Also, can you create a similar PR for develop?

Comment by David Hall (Inactive) [ 2020-10-12 ]

I ran a series of stored procedures, some with large data sets and some small. All passed without any noticeable execution time difference from Columnstore 1.2 .

I ran the query and Stored Procedure list in this Jira, and both ran in about the same time.

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