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

Union use table mode without good reason (needs server)

    XMLWordPrintable

    Details

      Description

      Preparation:

      DROP TABLE IF EXISTS `r1`;
      DROP TABLE IF EXISTS `r2`;
       
      CREATE TABLE IF NOT EXISTS  `r1` (`id1` int) ENGINE=Columnstore;
      CREATE TABLE IF NOT EXISTS  `r2` (`id2` int) ENGINE=Columnstore;
       
      INSERT INTO `r1`  (
      with recursive series as (
      select 1 as id union all
      select id +1 as id from series
      where id < 10000)
      select id from series);
       
       
      INSERT INTO `r2`  (
      with recursive series as (
      select 1 as id union all
      select id +1 as id from series
      where id < 10000)
      select id from series);
      
      

      A simple join will executed as usual (no table mode)

      SELECT   r1.id1,r2.id2
      FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)
      

      Adding a simple union select 1,2
      change the behaviour.

       select calSetTrace(1);
      (SELECT   r1.id1,r2.id2
      FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)
      union select 1,2;
        select calGetTrace();
      

       select calSetTrace(1);
      (SELECT   r1.id1,r2.id2
      FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)
      union select 1,2;
        select calGetTrace();
      

      debug log:

      Oct 20 16:17:39 localhost ExeMgr[961]: 39.070160 |31|0|0| D 16 CAL0041: Start SQL statement: (SELECT   r1.id1,r2.id2#012FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)#012union select 1,2; |test1|
      Oct 20 16:17:39 localhost ExeMgr[961]: 39.123944 |31|0|0| D 16 CAL0041: Start SQL statement: <part of the query executed in table mode>; |test1|
      Oct 20 16:17:39 localhost ExeMgr[961]: 39.146723 |31|0|0| D 16 CAL0042: End SQL statement
      Oct 20 16:17:39 localhost ExeMgr[961]: 39.147224 |31|0|0| D 16 CAL0042: End SQL statement
      

      With big tables, it can cause huge performance impact.

      If I wrap the whole statement, columnstore shows, he can it without table mode.

      Oct 20 16:17:52 localhost ExeMgr[961]: 52.051270 |31|0|0| D 16 CAL0041: Start SQL statement: select * from (#012(SELECT   r1.id1,r2.id2#012FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)#012union select 1,2) a; |test1|
      Oct 20 16:17:52 localhost ExeMgr[961]: 52.062831 |31|0|0| D 16 CAL0042: End SQL statement
      
      

      calGetTrace()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 
      Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows 
      BPS  PM   r2    3056     (id2)             0   2   0   0.002   1001 
      BPS  PM   r1    3054     (id1)             0   2   0   0.011   1001 
      HJS  PM   r1-r2 3054     -                 -   -   -   -----   -    
      TNS  UM   -     -        -                 -   -   -   0.000   10   
      TCS  UM   -     -        -                 -   -   -   0.000   1    
      TUS  UM   -     -        -                 -   -   -   0.016   11   
      TNS  UM   -     -        -                 -   -   -   0.000   11   
       |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Debug log:

      Oct 20 16:17:52 localhost ExeMgr[961]: 52.051270 |31|0|0| D 16 CAL0041: Start SQL statement: select * from (#012(SELECT   r1.id1,r2.id2#012FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)#012union select 1,2) a; |test1|
      Oct 20 16:17:52 localhost ExeMgr[961]: 52.062831 |31|0|0| D 16 CAL0042: End SQL statement
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              Richard Richard Stracke
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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