Details
-
Technical task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3(EOL)
-
10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18
Description
This script works fine in Oracle (and PostgreSQL):
DROP TABLE t1; |
CREATE TABLE t1 (c1 INT, c2 INT); |
INSERT INTO t1 VALUES (10,10); |
INSERT INTO t1 VALUES (20,20); |
UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1); |
SELECT * FROM t1; |
SQL> SELECT * FROM t1;
|
|
|
C1 C2
|
---------- ----------
|
10 10
|
21 20
|
Notice, the table t1 is used twice in the UPDATE query, as a target and as a source.
Under term of this tasks will allow to use the same table as a source and as a target in UPDATE statements in MariaDB.
Currently its not allowed and returns this error:
ERROR 1093 (HY000): Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
|
Attachments
Issue Links
- is blocked by
-
MDEV-13911 Support ORDER BY and LIMIT in multi-table update
-
- Closed
-
- relates to
-
MDEV-12137
DELETE statement with the same source and target
-
- Closed
-
- links to
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| Labels | Compatibility |
| Summary |
|
UPDATE statements with the same source and target |
| Assignee | Sergei Golubchik [ serg ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Remote Link | This issue links to "patch (Web Link)" [ 28015 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Description |
This script works fine in Oracle (and PostgreSQL):
{code:sql} DROP TABLE t1; CREATE TABLE t1 (c1 INT, c2 INT); INSERT INTO t1 VALUES (10,10); INSERT INTO t1 VALUES (20,20); UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1); SELECT * FROM t1; {code} {noformat} SQL> SELECT * FROM t1; C1 C2 ---------- ---------- 10 10 21 20 {noformat} Notice, the table {{t1}} is used twice in the {{UPDATE}} query, as a target and as a source. Under term of this tasks will allow to use the same table as a source and as a target in {{UPDATE}} statements in MariaDB. Currently its not allowed and returns this error: {noformat} ERROR 1093 (HY000): Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data {noformat} |
This script works fine in Oracle (and PostgreSQL):
{code:sql} DROP TABLE t1; CREATE TABLE t1 (c1 INT, c2 INT); INSERT INTO t1 VALUES (10,10); INSERT INTO t1 VALUES (20,20); UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1); SELECT * FROM t1; {code} {noformat} SQL> SELECT * FROM t1; C1 C2 ---------- ---------- 10 10 21 20 {noformat} Notice, the table {{t1}} is used twice in the {{UPDATE}} query, as a target and as a source. Under term of this tasks will allow to use the same table as a source and as a target in {{UPDATE}} statements in MariaDB. Currently its not allowed and returns this error: {noformat} ERROR 1093 (HY000): Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data {noformat} |
| Remote Link | This issue links to "patch (Web Link)" [ 28015 ] |
| Remote Link | This issue links to "patch (Web Link)" [ 28021 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Link |
This issue is blocked by |
| Fix Version/s | 10.3.2 [ 22533 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Parent Issue | MDEV-10764 [ MDEV-10764 ] |
|
| Workflow | MariaDB v3 [ 80892 ] | MariaDB v4 [ 152202 ] |