OnDuplicateUpdate Subquery Parameterization
- 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.