mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-23 07:05:36 +03:00
2f1f9c0ef0
1. In TupleUnion::writeNull(), add the missing switch case for wide decimal with 16bytes column width. 2. MCOL-5432 Disable complete/partial pushdown of UNION operation if the query involves an ORDER BY or a LIMIT clause, until MCOL-5222 is fixed. Also add MTR test cases for this.
186 lines
4.7 KiB
Plaintext
186 lines
4.7 KiB
Plaintext
--echo #
|
|
--echo # MDEV-25080: Allow pushdown of queries involving UNIONs
|
|
--echo # in outer select to foreign engines
|
|
--echo #
|
|
--echo # Remove the sorted_result MTR qualifier and add ORDER BY
|
|
--echo # clause after MCOL-5222 is fixed
|
|
--echo #
|
|
|
|
--source ../include/have_columnstore.inc
|
|
|
|
if (!$MASTER_MYPORT)
|
|
{
|
|
# Running with --extern
|
|
let $MASTER_MYPORT=`SELECT @@port`;
|
|
}
|
|
|
|
#
|
|
# Enable cross engine join
|
|
# Configure user and password in Columnstore.xml file
|
|
#
|
|
--exec $MCS_MCSSETCONFIG CrossEngineSupport User 'cejuser'
|
|
--exec $MCS_MCSSETCONFIG CrossEngineSupport Password 'Vagrant1|0000001'
|
|
--exec $MCS_MCSSETCONFIG CrossEngineSupport Port $MASTER_MYPORT
|
|
|
|
#
|
|
# Create corresponding in the server
|
|
#
|
|
--disable_warnings
|
|
CREATE USER IF NOT EXISTS'cejuser'@'localhost' IDENTIFIED BY 'Vagrant1|0000001';
|
|
--enable_warnings
|
|
GRANT ALL PRIVILEGES ON *.* TO 'cejuser'@'localhost';
|
|
FLUSH PRIVILEGES;
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mdev25080;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mdev25080;
|
|
|
|
USE mdev25080;
|
|
|
|
CREATE TABLE t1 (a varchar(30)) ENGINE=ColumnStore;
|
|
CREATE TABLE t2 (a varchar(30)) ENGINE=ColumnStore;
|
|
CREATE TABLE t3 (a varchar(30)) ENGINE=MyISAM;
|
|
CREATE TABLE t4 (a varchar(30)) ENGINE=MyISAM;
|
|
|
|
INSERT INTO t1 VALUES ('abc'), ('bcd'), ('cde');
|
|
INSERT INTO t2 VALUES ('bcd'), ('cde'), ('def'), ('efg');
|
|
|
|
INSERT INTO t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3');
|
|
INSERT INTO t4 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3');
|
|
|
|
--echo # Pushdown of the whole UNION
|
|
--sorted_result
|
|
SELECT * FROM t1 UNION SELECT * FROM t2;
|
|
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;
|
|
|
|
--sorted_result
|
|
SELECT * FROM t1 UNION ALL SELECT * FROM t2;
|
|
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2;
|
|
|
|
--echo # UNION with a foreign engine
|
|
--sorted_result
|
|
SELECT * FROM t1 UNION SELECT * FROM t3;
|
|
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t3;
|
|
|
|
--echo # More than two SELECTs in a UNIT:
|
|
--sorted_result
|
|
SELECT * FROM t1 UNION
|
|
SELECT * FROM t2 UNION ALL
|
|
SELECT * FROM t1;
|
|
|
|
EXPLAIN SELECT * FROM t1 UNION
|
|
SELECT * FROM t2 UNION ALL
|
|
SELECT * FROM t1;
|
|
|
|
--sorted_result
|
|
(SELECT * FROM t1 UNION
|
|
SELECT * FROM t2) UNION ALL
|
|
SELECT * FROM t1;
|
|
|
|
EXPLAIN (SELECT * FROM t1 UNION
|
|
SELECT * FROM t2) UNION ALL
|
|
SELECT * FROM t1;
|
|
|
|
--sorted_result
|
|
SELECT * FROM t1 UNION
|
|
SELECT * FROM t2 UNION ALL
|
|
SELECT * FROM t3 UNION
|
|
SELECT * FROM t4;
|
|
|
|
EXPLAIN SELECT * FROM t1 UNION
|
|
SELECT * FROM t2 UNION ALL
|
|
SELECT * FROM t3 UNION
|
|
SELECT * FROM t4;
|
|
|
|
--sorted_result
|
|
(SELECT * FROM t1 UNION
|
|
SELECT * FROM t2) UNION ALL
|
|
(SELECT * FROM t3 UNION
|
|
SELECT * FROM t4);
|
|
|
|
EXPLAIN (SELECT * FROM t1 UNION
|
|
SELECT * FROM t2) UNION ALL
|
|
(SELECT * FROM t3 UNION
|
|
SELECT * FROM t4);
|
|
|
|
--sorted_result
|
|
SELECT count(*) FROM t1 UNION
|
|
SELECT count(*) FROM t2 UNION ALL
|
|
SELECT count(*)+20 FROM t2 UNION
|
|
SELECT count(*)+5 FROM t1;
|
|
|
|
EXPLAIN
|
|
SELECT count(*) FROM t1 UNION
|
|
SELECT count(*) FROM t2 UNION ALL
|
|
SELECT count(*)+20 FROM t2 UNION
|
|
SELECT count(*)+5 FROM t1;
|
|
|
|
--echo # UNION inside a derived table: the whole derived table must be pushed
|
|
SELECT a FROM
|
|
(SELECT a FROM t1 UNION ALL SELECT a FROM t2) q ORDER BY a;
|
|
|
|
EXPLAIN
|
|
SELECT a FROM
|
|
(SELECT a FROM t1 UNION ALL SELECT a FROM t2) q ORDER BY a;
|
|
|
|
SELECT a FROM
|
|
(SELECT a FROM t1 UNION ALL SELECT a FROM t3) q ORDER BY a;
|
|
|
|
EXPLAIN
|
|
SELECT a FROM
|
|
(SELECT a FROM t1 UNION ALL SELECT a FROM t3) q ORDER BY a;
|
|
|
|
--echo # Prepared statements
|
|
PREPARE stmt FROM "SELECT * FROM t1 UNION
|
|
SELECT * FROM t2";
|
|
|
|
--sorted_result
|
|
EXECUTE stmt;
|
|
--sorted_result
|
|
EXECUTE stmt;
|
|
--sorted_result
|
|
EXECUTE stmt;
|
|
|
|
PREPARE stmt FROM "EXPLAIN SELECT * FROM t1 UNION
|
|
SELECT * FROM t2";
|
|
|
|
EXECUTE stmt;
|
|
EXECUTE stmt;
|
|
|
|
PREPARE stmt FROM "(SELECT * FROM t1 UNION
|
|
SELECT * FROM t2) UNION ALL
|
|
(SELECT * FROM t1 UNION
|
|
SELECT * FROM t2)";
|
|
|
|
--sorted_result
|
|
EXECUTE stmt;
|
|
--sorted_result
|
|
EXECUTE stmt;
|
|
--sorted_result
|
|
EXECUTE stmt;
|
|
|
|
PREPARE stmt FROM "EXPLAIN (SELECT * FROM t1 UNION
|
|
SELECT * FROM t2) UNION ALL
|
|
(SELECT * FROM t1 UNION
|
|
SELECT * FROM t2)";
|
|
|
|
EXECUTE stmt;
|
|
EXECUTE stmt;
|
|
|
|
--echo # MCOL-5432 Disable UNION pushdown if an ORDER BY or a LIMIT
|
|
--echo # clause is involved, until MCOL-5222 is fixed.
|
|
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a;
|
|
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a;
|
|
SELECT * FROM t1 UNION ALL SELECT * FROM t2 LIMIT 3;
|
|
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2 LIMIT 3;
|
|
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a DESC LIMIT 5;
|
|
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a DESC LIMIT 5;
|
|
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a LIMIT 3 OFFSET 2;
|
|
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a LIMIT 3 OFFSET 2;
|
|
|
|
DROP USER 'cejuser'@'localhost';
|
|
DROP TABLE t1, t2, t3, t4;
|
|
DROP DATABASE mdev25080;
|