dolthub / dolthub/dolt

OnDuplicateUpdate Subquery Parameterization

Open
#6,437 0 comments 0 reactions 1 assignee Claimed by @zachmu View on GitHub
analyzer bug sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 8h
Merged PRs (30d)
120

Description

On duplicate update expressions are hooks that edit insert rows after insert failures, with the simplest form looking like :
```sql
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
```
They can become more complicated ([docs here](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html)) in several ways:
- we can differentiate between `new` and `old` rows in the RHS expression (`VALUES(field)` expressions do the same thing, accessing the new source row value)
```sql
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
```
- the source for an insert can contain new columns to resolve
```sql
INSERT INTO t1
SELECT * FROM (SELECT c, c+d AS e FROM t2) AS dt
ON DUPLICATE KEY UPDATE b = e;
```
- we can resolve columns that comprise the input to an insert source, but are not a projected output:
```sql
insert into a1
(select t.i from b as t, b where t.i = b.i)
on duplicate key update i = b.i;
```

We do not currently support the last condition. There are resolve-time and execution-time problems.

It is somewhat unclear how deep of table cols we should support for name resolving. For example, an insert source of a subquery join will allow access to any column from the join tables. There does not appear to be functional dependency analysis limiting visibility to columns equivalent to the source projections:
```sql
explain analyze insert into a1 (select t.i from b1 as t, b1 where t.i = b1.i)
on duplicate key update i = b1.i;
explain analyze insert into a1 (select t.i from b1 as t, (select i+1 from b1) b2(i) where t.i = b2.i)
on duplicate key update i = b2.i;
explain analyze insert into a1 (select t.i from b1 as t, (select i+1 from b1) b2(i) where t.i = b2.i-1)
on duplicate key update i = b2.i;
```

Nested subquery tables and CTEs not in the top-level join are not visible:
```sql
insert into a1 (select t.i from b1 as t, (select b3.i+1 as i from b1 as t, (select i+2 from b1) b3(i) where t.i = b3.i) b2(i) where t.i = b2.i)
on duplicate key update i = b3.i;
insert into a1 (with b3(i) as (select i+2 from b1) select t.i from b1 as t, (select i+1 from b3) b2(i) where t.i = b2.i)
on duplicate key update i = b3.i;
ERROR 1054 (42S22): Unknown column 'b3.i' in 'field list'
```

At execution time, we do not have a good way to pluck the variables from the middle of an executed query for duplicate expressions to use afterwards. Two ideas:
- The top-level join could be separated from its projection node, giving us the full schema/row that the duplicate expressions can resolve. Regular inserts would apply the projection, and follow-up duplicate expression execution would still have access to the old row.
- We could track a parameterization map that is populated during execution for on duplicate expression access afterwards.

For now I will make changes that error indicating we were unable to resolve the expression.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.