Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6.12
-
None
Description
The below diff shows incorrect outputs on the federatedx storage engine:
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result |
index 044f64c796f..8da86fdd016 100644 |
--- a/mysql-test/suite/federated/federatedx_create_handlers.result
|
+++ b/mysql-test/suite/federated/federatedx_create_handlers.result
|
@@ -550,6 +550,36 @@ CREATE TABLE t3 (a varchar(30)) ENGINE=MyISAM; |
CREATE TABLE t4 (a varchar(30)) ENGINE=MyISAM; |
INSERT INTO t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3'); |
INSERT INTO t4 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3'); |
+SELECT 1, a FROM federated.t1 UNION ALL SELECT 2, a FROM federated.t2 ORDER BY 3; |
+1 a
|
+1 abc
|
+1 bcd
|
+1 cde
|
+2 abc
|
+2 bcd
|
+2 cde
|
+2 def
|
+2 efg
|
+SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2; |
+a
|
+abc
|
+bcd
|
+cde
|
+abc
|
+bcd
|
+cde
|
+def
|
+efg
|
+SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 1; |
+a
|
+abc
|
+bcd
|
+cde
|
+abc
|
+bcd
|
+cde
|
+def
|
+efg
|
# Pushdown of the whole UNION |
SELECT * from federated.t1 UNION SELECT * from federated.t2; |
a
|
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test |
index 8ad3172b35d..f9bdb9cf04e 100644 |
--- a/mysql-test/suite/federated/federatedx_create_handlers.test
|
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
|
@@ -421,6 +421,10 @@ CREATE TABLE t4 (a varchar(30)) ENGINE=MyISAM; |
INSERT INTO t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3'); |
INSERT INTO t4 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3'); |
|
+SELECT 1, a FROM federated.t1 UNION ALL SELECT 2, a FROM federated.t2 ORDER BY 3; |
+SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2; |
+SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 1; |
+
|
--echo # Pushdown of the whole UNION |
SELECT * from federated.t1 UNION SELECT * from federated.t2; |
EXPLAIN SELECT * from federated.t1 UNION SELECT * from federated.t2; |
The expected output for the above 3 queries should be as follows (here i1 and i2 are equivalent InnoDB tables):
MariaDB [test]> SELECT 1, a FROM i1 UNION ALL SELECT 2, a FROM i2 ORDER BY 3; |
ERROR 1054 (42S22): Unknown column '3' in 'order clause' |
MariaDB [test]> SELECT a FROM i1 UNION ALL SELECT a FROM i2 ORDER BY 2; |
ERROR 1054 (42S22): Unknown column '2' in 'order clause' |
MariaDB [test]> SELECT a FROM i1 UNION ALL SELECT a FROM i2 ORDER BY 1; |
+------+ |
| a |
|
+------+ |
| abc |
|
| abc |
|
| bcd |
|
| bcd |
|
| cde |
|
| cde |
|
| def |
|
| efg |
|
+------+ |
8 rows in set (0.003 sec) |
It is possible query 3 is currently not supported by the federatedx storage engine. But for queries 1 and 2, the server should error out (as is the case with InnoDB) before attempting to pushdown the query to the foreign engine.
Attachments
Issue Links
- blocks
-
MCOL-5222 ORDER BY on UNIONs in outer selects does not work
-
- Stalled
-
- relates to
-
MDEV-25080 Allow pushdown of queries involving UNIONs in outer select to foreign engines
-
- Closed
-