[
https://issues.apache.org/jira/browse/IGNITE-28201?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksim Zhuravkov updated IGNITE-28201:
--------------------------------------
Description:
Current partition pruning metadata collection algorithm for INSERT operations
has the following limitations:
- it can extract metadata only from column-order preserving projections in the
SELECT ... part of a INSERT INTO SELECT statement.
- it does not collect metadata when INSERT INTO ... SELECT .. reference
different tables.
Examples that can potentially benefit from partition pruning but the algorithm
does not handle them:
{noformat}
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1, c2));
CREATE TABLE t2 (id INT, val INT PRIMARY KEY (id));
--- Same table
--- PP metadata t1(INSERT)={id=99}, t1 (SELECT)={id=42}
INSERT INTO t2 SELECT 99, val FROM t1 WHERE id = 42
--- Different tables
--- t1.c1 and t1.c2 are constant
--- PP metadata t1={c1=1, c2=2}, t2={id=42}
INSERT INTO t1 SELECT 1, 2, val FROM t2 WHERE id = 42
--- t1.c1 = t2.id
--- PP metadata t1={c1=42, c2=2}, t2={id=42}
INSERT INTO t1 SELECT id, 2, val FROM t2 WHERE id = 42
{noformat}
We need to update the algorithm to support extracting metadata for such cases.
was:
Current partition pruning metadata collection algorithm for INSERT operations
has the following limitations:
- it can extract metadata only from column-order preserving projections in the
SELECT ... part of a INSERT INTO SELECT statement.
- it does not collect metadata when INSERT INTO ... SELECT .. reference
different tables.
Examples that can potentially benefit from partition pruning but the algorithm
does not handle them:
{noformat}
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1, c2));
CREATE TABLE t2 (id INT, val INT PRIMARY KEY (c1, c2));
--- Same table
--- PP metadata t1(INSERT)={id=99}, t1 (SELECT)={id=42}
INSERT INTO t2 SELECT 99, val FROM t1 WHERE id = 42
--- Different tables
--- t1.c1 and t1.c2 are constant
--- PP metadata t1={c1=1, c2=2}, t2={id=42}
INSERT INTO t1 SELECT 1, 2, val FROM t2 WHERE id = 42
--- t1.c1 = t2.id
--- PP metadata t1={c1=42, c2=2}, t2={id=42}
INSERT INTO t1 SELECT id, 2, val FROM t2 WHERE id = 42
{noformat}
We need to update the algorithm to support extracting metadata for such cases.
> Sql. Partition Pruning. Arbitrary projections in INSERT FROM SELECT
> -------------------------------------------------------------------
>
> Key: IGNITE-28201
> URL: https://issues.apache.org/jira/browse/IGNITE-28201
> Project: Ignite
> Issue Type: Improvement
> Components: sql ai3
> Reporter: Maksim Zhuravkov
> Priority: Major
> Labels: ignite-3
>
> Current partition pruning metadata collection algorithm for INSERT operations
> has the following limitations:
> - it can extract metadata only from column-order preserving projections in
> the SELECT ... part of a INSERT INTO SELECT statement.
> - it does not collect metadata when INSERT INTO ... SELECT .. reference
> different tables.
> Examples that can potentially benefit from partition pruning but the
> algorithm does not handle them:
> {noformat}
> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1, c2));
> CREATE TABLE t2 (id INT, val INT PRIMARY KEY (id));
> --- Same table
> --- PP metadata t1(INSERT)={id=99}, t1 (SELECT)={id=42}
> INSERT INTO t2 SELECT 99, val FROM t1 WHERE id = 42
> --- Different tables
> --- t1.c1 and t1.c2 are constant
> --- PP metadata t1={c1=1, c2=2}, t2={id=42}
> INSERT INTO t1 SELECT 1, 2, val FROM t2 WHERE id = 42
> --- t1.c1 = t2.id
> --- PP metadata t1={c1=42, c2=2}, t2={id=42}
> INSERT INTO t1 SELECT id, 2, val FROM t2 WHERE id = 42
> {noformat}
> We need to update the algorithm to support extracting metadata for such
> cases.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
