Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
N/A
-
Not for Release Notes
-
Q4/2025 Server Maintenance
Description
Testcases use oracle and MariaDB compatible SQL (with some auto-ignored error statements to align instances).
SAVEPOINTs partially work in combination with GTT tables:
SET autocommit=0; # error-ignored in oracle |
SET SQL_MODE='ORACLE'; # idem |
|
|
CREATE GLOBAL TEMPORARY TABLE gtt (c INT) ON COMMIT PRESERVE ROWS; |
|
|
SAVEPOINT sp1;
|
INSERT INTO gtt (c) VALUES (1); |
ROLLBACK TO sp1; |
SELECT * FROM gtt; |
|
|
-- Cleanup
|
TRUNCATE TABLE gtt; |
COMMIT; |
DROP TABLE gtt; |
Will yield 0 rows on both MariaDB and oracle, which is correct, and demonstrates basic SAVEPOINT functionality works correctly in combination with a GTT.
However, the following testcase:
SET autocommit=0; # error-ignored in oracle |
SET SQL_MODE='ORACLE'; # idem |
|
|
CREATE GLOBAL TEMPORARY TABLE gtt (c INT) ON COMMIT PRESERVE ROWS; |
|
|
INSERT INTO gtt (c) VALUES (0); |
SAVEPOINT sp1;
|
INSERT INTO gtt (c) VALUES (1); |
ROLLBACK TO sp1; |
SELECT * FROM gtt; |
|
|
-- Cleanup
|
TRUNCATE TABLE gtt; |
COMMIT; |
DROP TABLE gtt; |
Which matches the first, except for an additional INSERT done just before the SAVEPOINT is created.
Will result, for the SELECT, on oracle, as:
|
oracle |
SQL> SELECT * FROM gtt;
|
C
|
----------
|
0
|
Whereas on MariaDB we see:
|
MDEV-35915-6 CS 12.2.0 ed3c63488a1613377d92ee3ade3fe6870e39b4db (Optimized, Clang 21.1.0-20250811) Build 24/09/2025 |
12.2.0-opt>SELECT * FROM gtt;
|
+------+
|
| c |
|
+------+
|
| 0 |
|
| 1 |
|
+------+
|
2 rows in set (0.000 sec)
|
The same outcome is present for InnoDB and MyISAM on MariaDB.
The reason is clear:
|
MDEV-35915-6 CS 12.2.0 ed3c63488a1613377d92ee3ade3fe6870e39b4db (Optimized, Clang 21.1.0-20250811) Build 24/09/2025 |
12.2.0-opt>SAVEPOINT sp1;
|
ERROR 1178 (42000): The storage engine for the table doesn't support SAVEPOINT
|
...
|
12.2.0-opt>ROLLBACK TO sp1;
|
ERROR 1305 (42000): SAVEPOINT sp1 does not exist
|
In other words, the first testcase used a SAVEPOINT on an empty GTT correctly, the second testcase fails to use a SAVEPOINT on the same GTT due to a data row being present, and this leads to data differences when the same SQL is replayed on oracle vs MariaDB.
Attachments
Issue Links
- is caused by
-
MDEV-35915 Implement Global temporary tables
-
- Stalled
-