[MCOL-4901] Allow pushdown of queries involving UNIONs in outer select to ColumnStore - Jira

XML

Word

Printable

Details

Type: Task
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 6.1.1
Fix Version/s: columnstore-10.11.1, 23.02.1
Component/s: Columnstore Select Handler
Labels:
- mcs_rel_2302_1

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

blocks

MDEV-25080 Allow pushdown of queries involving UNIONs in outer select to foreign engines

Closed

is duplicated by

MCOL-4569 Queries with UNION ALL perform disproportionally badly

Closed

MCOL-4584 Significant performance degradation when UNION ALL is used in an outer select, compared to in a subquery.

Closed

relates to

MCOL-5222 ORDER BY on UNIONs in outer selects does not work

Stalled

Activity

People

Assignee:: Gagan Goel (Inactive)

Reporter:: Richard Stracke

Assigned for Testing:: Daniel Lee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2021-10-20 14:41

Updated:: 2023-11-17 15:38

Resolved:: 2023-03-01 19:46

Git Integration

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