diff options
Diffstat (limited to 'mysql-test/suite/compat/oracle/t')
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/binlog_stm_ps.test | 20 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/binlog_stm_sp.test | 23 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/column_compression.test | 11 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/func_concat.test | 32 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/func_time.test | 25 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/gis.test | 4 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/parser.test | 65 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/sp-expr.test | 165 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/statement-expr.test | 86 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/table_value_constr.test | 1083 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/versioning.test | 13 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/win.test | 22 |
12 files changed, 1549 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/t/binlog_stm_ps.test b/mysql-test/suite/compat/oracle/t/binlog_stm_ps.test index 996ef574413..f305f611bd2 100644 --- a/mysql-test/suite/compat/oracle/t/binlog_stm_ps.test +++ b/mysql-test/suite/compat/oracle/t/binlog_stm_ps.test @@ -35,3 +35,23 @@ SELECT * FROM t1; --let $binlog_file = LAST source include/show_binlog_events.inc; DROP TABLE t1; + + +--echo # +--echo # MDEV-16095 Oracle-style placeholder inside GROUP BY..WITH ROLLUP breaks replication +--echo # + +FLUSH LOGS; +CREATE TABLE t1 (d DATE); +INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24'); +CREATE TABLE t2 (d DATE, c BIGINT); +DELIMITER $$; +BEGIN + EXECUTE IMMEDIATE 'INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, :param' USING 1; + EXECUTE IMMEDIATE 'INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, :param WITH ROLLUP' USING 1; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1,t2; +--let $binlog_file = LAST +source include/show_binlog_events.inc; diff --git a/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test b/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test index 065c43eb274..e6f33cb1118 100644 --- a/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test +++ b/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test @@ -194,3 +194,26 @@ DROP TABLE t2; DROP PROCEDURE p1; --let $binlog_file = LAST source include/show_binlog_events.inc; + + +--echo # +--echo # MDEV-16020 SP variables inside GROUP BY..WITH ROLLUP break replication +--echo # + +FLUSH LOGS; +CREATE TABLE t1 (d DATE); +INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24'); +CREATE TABLE t2 (d DATE, c BIGINT); +DELIMITER $$; +DECLARE + var INT; +BEGIN + INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var; + INSERT INTO t2 SELECT d, COUNT(*) FROM t1 GROUP BY d, var WITH ROLLUP; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1,t2; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; diff --git a/mysql-test/suite/compat/oracle/t/column_compression.test b/mysql-test/suite/compat/oracle/t/column_compression.test new file mode 100644 index 00000000000..5544ff6c0b7 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/column_compression.test @@ -0,0 +1,11 @@ +--source include/have_innodb.inc +--source include/have_csv.inc + +SET sql_mode=ORACLE; + +SET column_compression_zlib_wrap=true; +CREATE TABLE t1 (a BLOB COMPRESSED); +INSERT INTO t1 VALUES (REPEAT('a',10000)); +SELECT DATA_LENGTH<100 AS c FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/func_concat.test b/mysql-test/suite/compat/oracle/t/func_concat.test index e1d8a5c477f..7f9fec4f2fe 100644 --- a/mysql-test/suite/compat/oracle/t/func_concat.test +++ b/mysql-test/suite/compat/oracle/t/func_concat.test @@ -114,3 +114,35 @@ SET sql_mode=ORACLE; SHOW CREATE VIEW v1; SELECT * FROM v1; DROP VIEW v1; + + +--echo # +--echo # MDEV-16186 Concatenation operator || returns wrong results in sql_mode=ORACLE +--echo # + +# Concatenation operator || has the same precedence with + +# (stronger than << and weaker than * ^) + +SELECT -1<<1||1 AS a FROM DUAL; +SELECT -1||0<<1 AS a FROM DUAL; + +EXPLAIN EXTENDED SELECT -1<<1||1 AS a FROM DUAL; +EXPLAIN EXTENDED SELECT -1||0<<1 AS a FROM DUAL; + +SELECT -1+1||1 AS a FROM DUAL; +SELECT -1||0+1 AS a FROM DUAL; + +EXPLAIN EXTENDED SELECT -1+1||1 AS a FROM DUAL; +EXPLAIN EXTENDED SELECT -1||0+1 AS a FROM DUAL; + +SELECT 1*1||-1 AS a FROM DUAL; +SELECT 1||1*-1 AS a FROM DUAL; + +EXPLAIN EXTENDED SELECT 1*1||-1 AS a FROM DUAL; +EXPLAIN EXTENDED SELECT 1||1*-1 AS a FROM DUAL; + +SELECT -1^1||1 AS a FROM DUAL; +SELECT -1||0^1 AS a FROM DUAL; + +EXPLAIN EXTENDED SELECT -1^1||1 AS a FROM DUAL; +EXPLAIN EXTENDED SELECT -1||0^1 AS a FROM DUAL; diff --git a/mysql-test/suite/compat/oracle/t/func_time.test b/mysql-test/suite/compat/oracle/t/func_time.test new file mode 100644 index 00000000000..c1174f7f395 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_time.test @@ -0,0 +1,25 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-16152 Expressions with INTERVAL return bad results in some cases +--echo # + +SELECT TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1, + -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2; + +SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1, + INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2, + +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3; + +EXPLAIN EXTENDED SELECT + TIMESTAMP'2001-01-01 10:20:30' - INTERVAL '10' YEAR AS c1, + -INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2; + +EXPLAIN EXTENDED SELECT + TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR AS c1, + INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c2, + +INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' AS c3; diff --git a/mysql-test/suite/compat/oracle/t/gis.test b/mysql-test/suite/compat/oracle/t/gis.test new file mode 100644 index 00000000000..a684563390b --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/gis.test @@ -0,0 +1,4 @@ +-- source include/have_geometry.inc + +SELECT WITHIN(POINT(1,1), POINT(1,1)); +SELECT WITHIN(POINT(1,1), POINT(0,0)); diff --git a/mysql-test/suite/compat/oracle/t/parser.test b/mysql-test/suite/compat/oracle/t/parser.test index 5aa37c1cd33..c0048e26fa8 100644 --- a/mysql-test/suite/compat/oracle/t/parser.test +++ b/mysql-test/suite/compat/oracle/t/parser.test @@ -22,3 +22,68 @@ BEGIN END; $$ DELIMITER ;$$ + + +--echo # +--echo # MDEV-16202 Latest changes made erroneously some keywords reserved in sql_mode=ORACLE +--echo # + + +DELIMITER $$; +CREATE PROCEDURE p1(name VARCHAR(64), pattern TEXT) AS + query TEXT DEFAULT REPLACE(pattern, 'name', name); +BEGIN + SELECT query AS ''; + EXECUTE IMMEDIATE query; +EXCEPTION + WHEN OTHERS THEN + BEGIN + SHOW ERRORS; + END; +END; +$$ + +CREATE PROCEDURE p2(name VARCHAR(64)) AS +BEGIN + CALL p1(name, 'DECLARE name INT; BEGIN name:=10; SELECT name; END'); + EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (name INT)', 'name', name); + CALL p1(name, 'SELECT name FROM t1'); + CALL p1(name, 'SELECT name ''alias'' FROM t1'); + CALL p1(name, 'SELECT name()'); + CALL p1(name, 'SELECT name.name()'); + CALL p1(name, 'SELECT name DATE FROM t1'); + CALL p1(name, 'SELECT name HISTORY FROM t1'); + CALL p1(name, 'SELECT name NEXT FROM t1'); + CALL p1(name, 'SELECT name PERIOD FROM t1'); + CALL p1(name, 'SELECT name PREVIOUS FROM t1'); + CALL p1(name, 'SELECT name SYSTEM FROM t1'); + CALL p1(name, 'SELECT name SYSTEM_TIME FROM t1'); + CALL p1(name, 'SELECT name TIME FROM t1'); + CALL p1(name, 'SELECT name TIMESTAMP FROM t1'); + CALL p1(name, 'SELECT name TRANSACTION FROM t1'); + CALL p1(name, 'SELECT name VALUE FROM t1'); + CALL p1(name, 'SELECT name VERSIONING FROM t1'); + CALL p1(name, 'SELECT name WITHOUT FROM t1'); + DROP TABLE t1; +END; +$$ +DELIMITER ;$$ + +--disable_column_names +CALL p2('date'); +CALL p2('history'); +CALL p2('next'); +CALL p2('period'); +CALL p2('previous'); +CALL p2('system'); +CALL p2('system_time'); +CALL p2('time'); +CALL p2('timestamp'); +CALL p2('transaction'); +CALL p2('value'); +CALL p2('versioning'); +CALL p2('without'); +--enable_column_names + +DROP PROCEDURE p2; +DROP PROCEDURE p1; diff --git a/mysql-test/suite/compat/oracle/t/sp-expr.test b/mysql-test/suite/compat/oracle/t/sp-expr.test new file mode 100644 index 00000000000..06a5c59b80c --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-expr.test @@ -0,0 +1,165 @@ +# Testing expressions of different kinds in various parts of SP syntax + +SET sql_mode=ORACLE; + +--echo # +--echo # Start of 10.3 tests +--echo # + +# +# Subselects in SP control structures +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +DELIMITER $$; + +BEGIN + CASE ((1) IN (SELECT a FROM t1)) WHEN 1 THEN SELECT 1; + ELSE SELECT NULL; + END CASE; +END; +$$ +BEGIN + CASE (EXISTS (SELECT a FROM t1)) WHEN 1 THEN SELECT 1; + ELSE SELECT NULL; + END CASE; +END; +$$ + +BEGIN + IF ((1) IN (SELECT a FROM t1)) THEN SELECT 1; + ELSE SELECT NULL; + END IF; +END; +$$ +BEGIN + IF (EXISTS (SELECT a FROM t1)) THEN SELECT 1; + ELSE SELECT NULL; + END IF; +END; +$$ + +BEGIN + WHILE ((1234) IN (SELECT * FROM t1)) LOOP + SELECT 1; + END LOOP; +END; +$$ +BEGIN + WHILE (EXISTS (SELECT * FROM t1 WHERE a=1234)) LOOP + SELECT 1; + END LOOP; +END; +$$ + +BEGIN + REPEAT + SELECT 1; + UNTIL (1 IN (SELECT * FROM t1)) + END REPEAT; +END; +$$ +BEGIN + REPEAT + SELECT 1; + UNTIL EXISTS (SELECT * FROM t1 WHERE a=1) + END REPEAT; +END; +$$ + +BEGIN + FOR i IN 0..(1 IN (SELECT * FROM t1)) + LOOP + SELECT i; + END LOOP; +END; +$$ +BEGIN + FOR i IN 0..EXISTS (SELECT * FROM t1 WHERE a=1) + LOOP + SELECT i; + END LOOP; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + + +# +# Subselects as SP variable default values +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +DELIMITER $$; +DECLARE + a INT DEFAULT ((10) IN (SELECT * FROM t1)); +BEGIN + SELECT a; +END; +$$ +DECLARE + a INT DEFAULT EXISTS (SELECT * FROM t1); +BEGIN + SELECT a; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + + +# +# Subselects SP function return values +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +DELIMITER $$; +CREATE FUNCTION f1() RETURN INT AS +BEGIN + RETURN ((1) IN (SELECT * FROM t1)); +END; +$$ +CREATE FUNCTION f2() RETURN INT AS +BEGIN + RETURN EXISTS (SELECT * FROM t1 WHERE a=1); +END; +$$ +DELIMITER ;$$ +SELECT f1(); +SELECT f2(); +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; + + +# +# Subselects in CURSOR parameters +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +DELIMITER $$; +DECLARE + va INT; + CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a; +BEGIN + OPEN cur(1 IN (SELECT * FROM t1)); + FETCH cur INTO va; + SELECT va; + CLOSE cur; +END; +$$ +DECLARE + va INT; + CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a; +BEGIN + OPEN cur(EXISTS (SELECT * FROM t1)); + FETCH cur INTO va; + SELECT va; + CLOSE cur; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/statement-expr.test b/mysql-test/suite/compat/oracle/t/statement-expr.test new file mode 100644 index 00000000000..f4a6a25eff3 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/statement-expr.test @@ -0,0 +1,86 @@ +# Testing expressions of different kinds in various non-SELECT statements + +SET sql_mode=ORACLE; + +--echo # +--echo # Start of 10.3 tests +--echo # + +# +# Subselects in non-SELECT statements +# + +CREATE TABLE t1 (id INT, id1 INT); +INSERT INTO t1 VALUES (1,7); +INSERT INTO t1 VALUES (1,8); +SELECT ROW(1,7) IN (SELECT id, id1 FROM t1 WHERE id1= 8); +EXECUTE IMMEDIATE 'SELECT ROW(1, 7) IN (SELECT id, id1 FROM t1 WHERE id1= 8)'; +DROP TABLE t1; + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE 'SELECT ?' USING (1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE 'SELECT ?' USING (SELECT * FROM t1); + + +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (10); +DELIMITER $$; +CREATE PROCEDURE p1(a INT) AS BEGIN NULL; END; +$$ +DELIMITER ;$$ +CALL p1((1) IN (SELECT * FROM t1)); +CALL p1(EXISTS (SELECT * FROM t1)); +DROP PROCEDURE p1; +DROP TABLE t1; + +--error ER_PARSE_ERROR +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1); + +DELIMITER $$; +--error ER_PARSE_ERROR +BEGIN NOT ATOMIC + DECLARE CONTINUE HANDLER FOR SQLWARNING + RESIGNAL SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1)); + SIGNAL SQLSTATE '01000'; +END; +$$ +--error ER_PARSE_ERROR +BEGIN NOT ATOMIC + DECLARE CONTINUE HANDLER FOR SQLWARNING + RESIGNAL SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1); + SIGNAL SQLSTATE '01000'; +END; +$$ +DELIMITER ;$$ + + +--error ER_PARSE_ERROR +PREPARE stmt FROM (1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +PREPARE stmt FROM EXISTS (SELECT * FROM t1); + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE (1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE EXISTS (SELECT * FROM t1); + +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION (1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO; + +--error ER_PARSE_ERROR +PURGE BINARY LOGS BEFORE (1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +PURGE BINARY LOGS BEFORE EXISTS (SELECT * FROM t1); + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +DO 1 IN (SELECT * FROM t1); +DO EXISTS (SELECT * FROM t1); +DROP TABLE t1; + + diff --git a/mysql-test/suite/compat/oracle/t/table_value_constr.test b/mysql-test/suite/compat/oracle/t/table_value_constr.test new file mode 100644 index 00000000000..66519e93a36 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/table_value_constr.test @@ -0,0 +1,1083 @@ +SET sql_mode=ORACLE; + +create table t1 (a int, b int); + +insert into t1 values (1,2),(4,6),(9,7), + (1,1),(2,5),(7,8); + +--echo # just VALUES + +values (1,2); + +values (1,2), (3,4), (5.6,0); + +values ('abc', 'def'); + +--echo # UNION that uses VALUES structure(s) + +select 1,2 +union +values (1,2); + +values (1,2) +union +select 1,2; + +select 1,2 +union +values (1,2),(3,4),(5,6),(7,8); + +select 3,7 +union +values (1,2),(3,4),(5,6); + +select 3,7,4 +union +values (1,2,5),(4,5,6); + +select 1,2 +union +values (1,7),(3,6.5); + +select 1,2 +union +values (1,2.0),(3,6); + +select 1.8,2 +union +values (1,2),(3,6); + +values (1,2.4),(3,6) +union +select 2.8,9; + +values (1,2),(3,4),(5,6),(7,8) +union +select 5,6; + +select 'ab','cdf' +union +values ('al','zl'),('we','q'); + +values ('ab', 'cdf') +union +select 'ab','cdf'; + +values (1,2) +union +values (1,2),(5,6); + +values (1,2) +union +values (3,4),(5,6); + +values (1,2) +union +values (1,2) +union values (4,5); + +--echo # UNION ALL that uses VALUES structure + +values (1,2),(3,4) +union all +select 5,6; + +values (1,2),(3,4) +union all +select 1,2; + +select 5,6 +union all +values (1,2),(3,4); + +select 1,2 +union all +values (1,2),(3,4); + +values (1,2) +union all +values (1,2),(5,6); + +values (1,2) +union all +values (3,4),(5,6); + +values (1,2) +union all +values (1,2) +union all +values (4,5); + +values (1,2) +union all +values (1,2) +union values (1,2); + +values (1,2) +union +values (1,2) +union all +values (1,2); + +--echo # EXCEPT that uses VALUES structure(s) + +select 1,2 +except +values (3,4),(5,6); + +select 1,2 +except +values (1,2),(3,4); + +values (1,2),(3,4) +except +select 5,6; + +values (1,2),(3,4) +except +select 1,2; + +values (1,2),(3,4) +except +values (5,6); + +values (1,2),(3,4) +except +values (1,2); + +--echo # INTERSECT that uses VALUES structure(s) + +select 1,2 +intersect +values (3,4),(5,6); + +select 1,2 +intersect +values (1,2),(3,4); + +values (1,2),(3,4) +intersect +select 5,6; + +values (1,2),(3,4) +intersect +select 1,2; + +values (1,2),(3,4) +intersect +values (5,6); + +values (1,2),(3,4) +intersect +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION + EXCEPT + +values (1,2),(3,4) +except +select 1,2 +union values (1,2); + +values (1,2),(3,4) +except +values (1,2) +union +values (1,2); + +values (1,2),(3,4) +except +values (1,2) +union +values (3,4); + +values (1,2),(3,4) +union +values (1,2) +except +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT + +values (1,2),(3,4) +except +select 1,2 +union all +values (1,2); + +values (1,2),(3,4) +except +values (1,2) +union all +values (1,2); + +values (1,2),(3,4) +except +values (1,2) +union all +values (3,4); + +values (1,2),(3,4) +union all +values (1,2) +except +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION + INTERSECT + +values (1,2),(3,4) +intersect +select 1,2 +union +values (1,2); + +values (1,2),(3,4) +intersect +values (1,2) +union +values (1,2); + +values (1,2),(3,4) +intersect +values (1,2) +union +values (3,4); + +values (1,2),(3,4) +union +values (1,2) +intersect +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT + +values (1,2),(3,4) +intersect +select 1,2 +union all +values (1,2); + +values (1,2),(3,4) +intersect +values (1,2) +union all +values (1,2); + +values (1,2),(3,4) +intersect +values (1,2) +union all +values (3,4); + +values (1,2),(3,4) +union all +values (1,2) +intersect +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION + UNION ALL + +values (1,2),(3,4) +union all +select 1,2 +union +values (1,2); + +values (1,2),(3,4) +union all +values (1,2) +union +values (1,2); + +values (1,2),(3,4) +union all +values (1,2) +union +values (3,4); + +values (1,2),(3,4) +union +values (1,2) +union all +values (1,2); + +values (1,2) +union +values (1,2) +union all +values (1,2); + +--echo # CTE that uses VALUES structure(s) : non-recursive CTE + +with t2 as +( + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + select 1,2 + union + values (1,2) +) +select * from t2; + +with t2 as +( + select 1,2 + union + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + values (1,2) + union + select 1,2 +) +select * from t2; + +with t2 as +( + values (1,2),(3,4) + union + select 1,2 +) +select * from t2; + +with t2 as +( + values (5,6) + union + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + values (1,2) + union + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + select 1,2 + union all + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + values (1,2),(3,4) + union all + select 1,2 +) +select * from t2; + +with t2 as +( + values (1,2) + union all + values (1,2),(3,4) +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor + +with recursive t2(a,b) as +( + values(1,1) + union + select t1.a, t1.b + from t1,t2 + where t1.a=t2.a +) +select * from t2; + +with recursive t2(a,b) as +( + values(1,1) + union + select t1.a+1, t1.b + from t1,t2 + where t1.a=t2.a +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors + +with recursive t2(a,b) as +( + values(1,1) + union + values (3,4) + union + select t2.a+1, t1.b + from t1,t2 + where t1.a=t2.a +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : that uses UNION ALL + +with recursive t2(a,b,st) as +( + values(1,1,1) + union all + select t2.a, t1.b, t2.st+1 + from t1,t2 + where t1.a=t2.a and st<3 +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements) + +with recursive fact(n,f) as +( + values(1,1) + union + select n+1,f*n from fact where n < 10 +) +select * from fact; + +--echo # Derived table that uses VALUES structure(s) : singe VALUES structure + +select * from (values (1,2),(3,4)) as t2; + +--echo # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s) + +select * from (select 1,2 union values (1,2)) as t2; + +select * from (select 1,2 union values (1,2),(3,4)) as t2; + +select * from (values (1,2) union select 1,2) as t2; + +select * from (values (1,2),(3,4) union select 1,2) as t2; + +select * from (values (5,6) union values (1,2),(3,4)) as t2; + +select * from (values (1,2) union values (1,2),(3,4)) as t2; + +--echo # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s) + +select * from (select 1,2 union all values (1,2),(3,4)) as t2; + +select * from (values (1,2),(3,4) union all select 1,2) as t2; + +select * from (values (1,2) union all values (1,2),(3,4)) as t2; + +--echo # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure + +let $drop_view= drop view v1; +let $select_view= select * from v1; + +create view v1 as values (1,2),(3,4); + +eval $select_view; +eval $drop_view; + +--echo # CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s) + +create view v1 as + select 1,2 + union + values (1,2); + +eval $select_view; +eval $drop_view; + +create view v1 as + select 1,2 + union + values (1,2),(3,4); + +eval $select_view; +eval $drop_view; + +create view v1 as + values (1,2) + union + select 1,2; + +eval $select_view; +eval $drop_view; + +create view v1 as + values (1,2),(3,4) + union + select 1,2; + +eval $select_view; +eval $drop_view; + +create view v1 as + values (5,6) + union + values (1,2),(3,4); + +eval $select_view; +eval $drop_view; + +--echo # CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s) + +create view v1 as + values (1,2) + union + values (1,2),(3,4); + +eval $select_view; +eval $drop_view; + +create view v1 as + select 1,2 + union all + values (1,2),(3,4); + +eval $select_view; +eval $drop_view; + +create view v1 as + values (1,2),(3,4) + union all + select 1,2; + +eval $select_view; +eval $drop_view; + +create view v1 as + values (1,2) + union all + values (1,2),(3,4); + +eval $select_view; +eval $drop_view; + +--echo # IN-subquery with VALUES structure(s) : simple case +let $query= +select * from t1 +where a in (values (1)); +let $subst_query= +select * from t1 +where a in (select * from (values (1)) as tvc_0); +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place +let $query= +select * from t1 +where a in (values (1) union select 2); +let $subst_query= +select * from t1 +where a in (select * from (values (1)) as tvc_0 union + select 2); +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the second place +let $query= +select * from t1 +where a in (select 2 union values (1)); +let $subst_query= +select * from t1 +where a in (select 2 union + select * from (values (1)) tvc_0); +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # IN-subquery with VALUES structure(s) : UNION ALL +let $query= +select * from t1 +where a in (values (1) union all select b from t1); +let $subst_query= +select * from t1 +where a in (select * from (values (1)) as tvc_0 union all + select b from t1); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # NOT IN subquery with VALUES structure(s) : simple case +let $query= +select * from t1 +where a not in (values (1),(2)); +let $subst_query= +select * from t1 +where a not in (select * from (values (1),(2)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place +let $query= +select * from t1 +where a not in (values (1) union select 2); +let $subst_query= +select * from t1 +where a not in (select * from (values (1)) as tvc_0 union + select 2); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place +let $query= +select * from t1 +where a not in (select 2 union values (1)); +let $subst_query= +select * from t1 +where a not in (select 2 union + select * from (values (1)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ANY-subquery with VALUES structure(s) : simple case +let $query= +select * from t1 +where a = any (values (1),(2)); +let $subst_query= +select * from t1 +where a = any (select * from (values (1),(2)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place +let $query= +select * from t1 +where a = any (values (1) union select 2); +let $subst_query= +select * from t1 +where a = any (select * from (values (1)) as tvc_0 union + select 2); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place +let $query= +select * from t1 +where a = any (select 2 union values (1)); +let $subst_query= +select * from t1 +where a = any (select 2 union + select * from (values (1)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ALL-subquery with VALUES structure(s) : simple case +let $query= +select * from t1 +where a = all (values (1)); +let $subst_query= +select * from t1 +where a = all (select * from (values (1)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place +let $query= +select * from t1 +where a = all (values (1) union select 1); +let $subst_query= +select * from t1 +where a = all (select * from (values (1)) as tvc_0 union + select 1); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place +let $query= +select * from t1 +where a = any (select 1 union values (1)); +let $subst_query= +select * from t1 +where a = any (select 1 union + select * from (values (1)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # prepare statement that uses VALUES structure(s): single VALUES structure + +prepare stmt1 from ' +values (1,2); +'; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +--echo # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s) + +prepare stmt1 from ' + select 1,2 + union + values (1,2),(3,4); +'; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from ' + values (1,2),(3,4) + union + select 1,2; +'; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from ' + select 1,2 + union + values (3,4) + union + values (1,2); +'; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from ' + values (5,6) + union + values (1,2),(3,4); +'; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +--echo # prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s) + +prepare stmt1 from ' + select 1,2 + union + values (1,2),(3,4); +'; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from ' + values (1,2),(3,4) + union all + select 1,2; +'; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from ' + select 1,2 + union all + values (3,4) + union all + values (1,2); +'; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from ' + values (1,2) + union all + values (1,2),(3,4); +'; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +--echo # explain query that uses VALUES structure(s): single VALUES structure + +explain +values (1,2); + +explain format=json +values (1,2); + +--echo # explain query that uses VALUES structure(s): UNION with VALUES structure(s) + +explain +select 1,2 +union +values (1,2),(3,4); + +explain +values (1,2),(3,4) +union +select 1,2; + +explain +values (5,6) +union +values (1,2),(3,4); + +explain format=json +select 1,2 +union +values (1,2),(3,4); + +explain format=json +values (1,2),(3,4) +union +select 1,2; + +explain format=json +values (5,6) +union +values (1,2),(3,4); + +explain +select 1,2 +union +values (3,4) +union +values (1,2); + +explain format=json +select 1,2 +union +values (3,4) +union +values (1,2); + +--echo # explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s) + +explain +select 1,2 +union +values (1,2),(3,4); + +explain +values (1,2),(3,4) +union all +select 1,2; + +explain +values (1,2) +union all +values (1,2),(3,4); + +explain format=json +values (1,2),(3,4) +union all +select 1,2; + +explain format=json +select 1,2 +union +values (1,2),(3,4); + +explain format=json +values (1,2) +union all +values (1,2),(3,4); + +explain +select 1,2 +union all +values (3,4) +union all +values (1,2); + +explain format=json +select 1,2 +union all +values (3,4) +union all +values (1,2); + +--echo # analyze query that uses VALUES structure(s): single VALUES structure + +analyze +values (1,2); + +analyze format=json +values (1,2); + +--echo # analyze query that uses VALUES structure(s): UNION with VALUES structure(s) + +analyze +select 1,2 +union +values (1,2),(3,4); + +analyze +values (1,2),(3,4) +union +select 1,2; + +analyze +values (5,6) +union +values (1,2),(3,4); + +analyze format=json +select 1,2 +union +values (1,2),(3,4); + +analyze format=json +values (1,2),(3,4) +union +select 1,2; + +analyze format=json +values (5,6) +union +values (1,2),(3,4); + +analyze +select 1,2 +union +values (3,4) +union +values (1,2); + +analyze format=json +select 1,2 +union +values (3,4) +union +values (1,2); + +--echo # analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s) + +analyze +select 1,2 +union +values (1,2),(3,4); + +analyze +values (1,2),(3,4) +union all +select 1,2; + +analyze +values (1,2) +union all +values (1,2),(3,4); + +analyze format=json +values (1,2),(3,4) +union all +select 1,2; + +analyze format=json +select 1,2 +union +values (1,2),(3,4); + +analyze format=json +values (1,2) +union all +values (1,2),(3,4); + +analyze +select 1,2 +union all +values (3,4) +union all +values (1,2); + +analyze format=json +select 1,2 +union all +values (3,4) +union all +values (1,2); + +--echo # different number of values in TVC +--error ER_WRONG_NUMBER_OF_VALUES_IN_TVC +values (1,2),(3,4,5); + +--echo # illegal parameter data types in TVC +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +values (1,point(1,1)),(1,1); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +values (1,point(1,1)+1); + +--echo # field reference in TVC +--error ER_FIELD_REFERENCE_IN_TVC +select * from (values (1), (b), (2)) as new_tvc; +--error ER_FIELD_REFERENCE_IN_TVC +select * from (values (1), (t1.b), (2)) as new_tvc; + +drop table t1; + +--echo # +--echo # MDEV-15940: cursor over TVC +--echo # + +DELIMITER |; + +DECLARE + v INT; + CURSOR cur IS VALUES(7); +BEGIN + OPEN cur; + FETCH cur INTO v; + SELECT v; +END; +| + +DECLARE + v INT DEFAULT 0; +BEGIN + FOR a IN (VALUES (7)) LOOP + SET v = v + 1; + END LOOP; + SELECT v; +END; +| + +DELIMITER ;| + +--echo # +--echo # MDEV-16038: empty row in TVC +--echo # + +--error ER_EMPTY_ROW_IN_TVC +with t as (values (),()) select 1 from t; diff --git a/mysql-test/suite/compat/oracle/t/versioning.test b/mysql-test/suite/compat/oracle/t/versioning.test new file mode 100644 index 00000000000..d70058c56e4 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/versioning.test @@ -0,0 +1,13 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-15975 PL/SQL parser does not understand historical queries +--echo # + +CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING; +INSERT INTO t1 VALUES (10); +DELETE FROM t1; +INSERT INTO t1 VALUES (20); +SELECT * FROM t1 FOR SYSTEM_TIME ALL; +SELECT * FROM t1 FOR SYSTEM_TIME AS OF (NOW()+INTERVAL 10 YEAR); +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/win.test b/mysql-test/suite/compat/oracle/t/win.test new file mode 100644 index 00000000000..c6f0b6474cf --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/win.test @@ -0,0 +1,22 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-13384: "window" seems like a reserved column name but it's not listed as one +--echo # +--echo # Currently we allow window as an identifier, except for table aliases. +--echo # + +CREATE TABLE door (id INT, window VARCHAR(10)); + +--error ER_PARSE_ERROR +SELECT id +FROM door as window; + +SELECT id, window +FROM door; + +--error ER_PARSE_ERROR +SELECT id, window +FROM door as window; + +DROP TABLE door; |