Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4334

Procedure Hanging When Using Columnstore Table

    XMLWordPrintable

Details

    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;
      

      Attachments

        Activity

          People

            David.Hall David Hall (Inactive)
            toddstoffel Todd Stoffel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.