diff options
| author | Nirbhay Choubey <nirbhay@skysql.com> | 2014-03-26 14:27:24 -0400 |
|---|---|---|
| committer | Nirbhay Choubey <nirbhay@skysql.com> | 2014-03-26 14:27:24 -0400 |
| commit | 90e4f7f9d3f2669ac99f2817f884315bfc86b0a7 (patch) | |
| tree | 60a10a515a9f5656e797d8142f97b94f89f4e797 /mysql-test/t | |
| parent | 586fab72f01e1c7f0f8bf363fa6b06a2f10965b4 (diff) | |
| parent | 5b7cab82195268f7657504d0b53995654748cefa (diff) | |
| download | mariadb-git-90e4f7f9d3f2669ac99f2817f884315bfc86b0a7.tar.gz | |
* bzr merge -rtag:mariadb-10.0.9 maria/10.0
* Fix for post-merge build failures.
Diffstat (limited to 'mysql-test/t')
76 files changed, 2568 insertions, 260 deletions
diff --git a/mysql-test/t/aborted_clients.test b/mysql-test/t/aborted_clients.test index fafcfb6b3e9..20ddc9991e6 100644 --- a/mysql-test/t/aborted_clients.test +++ b/mysql-test/t/aborted_clients.test @@ -1,28 +1,28 @@ -# Test case for MDEV-246, lp:992983
-# Check that ordinary connect/disconnect does not increase aborted_clients
-# status variable, but KILL connection does
-
--- source include/not_embedded.inc
--- source include/count_sessions.inc
-
-FLUSH STATUS;
-# Connect/Disconnect look that aborted_clients stays 0
-connect (con1,localhost,root,,);
-disconnect con1;
-connection default;
--- source include/wait_until_count_sessions.inc
-# Check that there is 0 aborted clients so far
-SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='aborted_clients';
-
-# Kill a connection, check that aborted_clients is incremented
-connect(con2,localhost,root,,);
---disable_reconnect
---error ER_CONNECTION_KILLED
-KILL CONNECTION_ID();
-disconnect con2;
-connection default;
--- source include/wait_until_count_sessions.inc
-
-# aborted clients must be 1 now
-SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='aborted_clients';
-FLUSH STATUS;
+# Test case for MDEV-246, lp:992983 +# Check that ordinary connect/disconnect does not increase aborted_clients +# status variable, but KILL connection does + +-- source include/not_embedded.inc +-- source include/count_sessions.inc + +FLUSH STATUS; +# Connect/Disconnect look that aborted_clients stays 0 +connect (con1,localhost,root,,); +disconnect con1; +connection default; +-- source include/wait_until_count_sessions.inc +# Check that there is 0 aborted clients so far +SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='aborted_clients'; + +# Kill a connection, check that aborted_clients is incremented +connect(con2,localhost,root,,); +--disable_reconnect +--error ER_CONNECTION_KILLED +KILL CONNECTION_ID(); +disconnect con2; +connection default; +-- source include/wait_until_count_sessions.inc + +# aborted clients must be 1 now +SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='aborted_clients'; +FLUSH STATUS; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 2053e2d9d59..3050fc0378d 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1258,7 +1258,7 @@ CREATE TABLE t1 ( id INT(11) NOT NULL, x_param INT(11) DEFAULT NULL, PRIMARY KEY (id) -); +) ENGINE=MYISAM; ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, ADD COLUMN IF NOT EXISTS lol INT AFTER id; @@ -1277,6 +1277,45 @@ CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); SHOW CREATE TABLE t1; DROP TABLE t1; +CREATE TABLE t1 ( + id INT(11) NOT NULL, + x_param INT(11) DEFAULT NULL, + PRIMARY KEY (id) +) ENGINE=INNODB; + +CREATE TABLE t2 ( + id INT(11) NOT NULL) ENGINE=INNODB; + +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, + ADD COLUMN IF NOT EXISTS lol INT AFTER id; +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; + +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); +ALTER TABLE t1 MODIFY IF EXISTS lol INT; + +DROP INDEX IF EXISTS x_param ON t1; +DROP INDEX IF EXISTS x_param ON t1; +CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); +CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); +SHOW CREATE TABLE t1; + +ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id); +ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id); +ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk; +ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk; +SHOW CREATE TABLE t2; +ALTER TABLE t2 ADD FOREIGN KEY (id) REFERENCES t1(id); +ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS t2_ibfk_1(id) REFERENCES t1(id); +ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1; +ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1; +SHOW CREATE TABLE t2; + +DROP TABLE t2; +DROP TABLE t1; + --echo # --echo # Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED --echo # diff --git a/mysql-test/t/alter_table_autoinc-5574.test b/mysql-test/t/alter_table_autoinc-5574.test new file mode 100644 index 00000000000..95c2b8d81bb --- /dev/null +++ b/mysql-test/t/alter_table_autoinc-5574.test @@ -0,0 +1,12 @@ +# +# MDEV-5574 Set AUTO_INCREMENT below max value of column +# +--source include/have_innodb.inc +create table t1(a int(10)unsigned not null auto_increment primary key, +b varchar(255) not null) engine=innodb default charset=utf8; +insert into t1 values(1,'aaa'),(2,'bbb'); +alter table t1 auto_increment=1; +insert into t1 values(NULL, 'ccc'); +select * from t1; +drop table t1; + diff --git a/mysql-test/t/alter_table_trans.test b/mysql-test/t/alter_table_trans.test index 9096a392af4..29b9b4c212f 100644 --- a/mysql-test/t/alter_table_trans.test +++ b/mysql-test/t/alter_table_trans.test @@ -13,3 +13,23 @@ drop table if exists t1,t2; CREATE TABLE t1 (a INT, INDEX(a)) engine=innodb; ALTER TABLE t1 RENAME TO t2, DISABLE KEYS; DROP TABLE t2; + +# +# MDEV-5406 add index to an innodb table with a uniqueness violation crashes mysqld +# + +CREATE TABLE t1 ( + col4 text NOT NULL, + col2 int(11) NOT NULL DEFAULT '0', + col3 int(11) DEFAULT NULL, + extra int(11) DEFAULT NULL, + KEY idx (col4(10)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +insert t1 values (repeat('1', 8193),3,1,1); +insert t1 values (repeat('3', 8193),3,1,1); +--error ER_DUP_ENTRY +ALTER TABLE t1 ADD PRIMARY KEY (col4(10)) , ADD UNIQUE KEY uidx (col3); +DROP TABLE t1; + + diff --git a/mysql-test/t/assign_key_cache-5405.test b/mysql-test/t/assign_key_cache-5405.test new file mode 100644 index 00000000000..2839e040bd3 --- /dev/null +++ b/mysql-test/t/assign_key_cache-5405.test @@ -0,0 +1,27 @@ +# +# MDEV-5405 RQG induced crash in mi_assign_to_key_cache in safe mutex unlock +# +--source include/have_debug_sync.inc +create table t1 (f int, key(f)) engine=myisam; +set global kc1.key_buffer_size = 65536; + +connect (con1, localhost, root); + +set debug_sync='assign_key_cache_op_unlock wait_for op_locked'; +send cache index t1 in kc1; + +connection default; +sleep 1; +set debug_sync='assign_key_cache_op_lock signal op_locked wait_for assigned'; +send cache index t1 in kc1; + +connection con1; +reap; +set debug_sync='now signal assigned'; +disconnect con1; +connection default; +reap; + +drop table t1; +set global kc1.key_buffer_size = 0; +set debug_sync='reset'; diff --git a/mysql-test/t/auth_rpl.test b/mysql-test/t/auth_rpl.test index c413a84b53c..0ff024c73e7 100644 --- a/mysql-test/t/auth_rpl.test +++ b/mysql-test/t/auth_rpl.test @@ -1,66 +1,66 @@ ---source include/have_plugin_auth.inc
---source include/not_embedded.inc
---source include/master-slave.inc
-
-#
-# Check that replication slave can connect to master using an account
-# which authenticates with an external authentication plugin (bug#12897501).
-
-#
-# First stop the slave to guarantee that nothing is replicated.
-#
---connection slave
---echo [connection slave]
---source include/stop_slave.inc
-#
-# Create an replication account on the master.
-#
---connection master
---echo [connection master]
-CREATE USER 'plug_user' IDENTIFIED WITH 'test_plugin_server' AS 'plug_user';
-GRANT REPLICATION SLAVE ON *.* TO plug_user;
-FLUSH PRIVILEGES;
-
-#
-# Now go to slave and change the replication user.
-#
---connection slave
---echo [connection slave]
---let $master_user= query_get_value(SHOW SLAVE STATUS, Master_User, 1)
-CHANGE MASTER TO
- MASTER_USER= 'plug_user',
- MASTER_PASSWORD= 'plug_user';
-
-#
-# Start slave with new replication account - this should trigger connection
-# to the master server.
-#
---source include/start_slave.inc
-
-# Replicate all statements executed on master, in this case,
-# (creation of the plug_user account).
-#
---connection master
---sync_slave_with_master
---echo # Slave in-sync with master now.
-
-SELECT user, plugin, authentication_string FROM mysql.user WHERE user LIKE 'plug_user';
-
-#
-# Now we can stop the slave and clean up.
-#
-# Note: it is important that slave is stopped at this
-# moment - otherwise master's cleanup statements
-# would be replicated on slave!
-#
---echo # Cleanup (on slave).
---source include/stop_slave.inc
-eval CHANGE MASTER TO MASTER_USER='$master_user';
-DROP USER 'plug_user';
-
---echo # Cleanup (on master).
---connection master
-DROP USER 'plug_user';
-
---let $rpl_only_running_threads= 1
---source include/rpl_end.inc
+--source include/have_plugin_auth.inc +--source include/not_embedded.inc +--source include/master-slave.inc + +# +# Check that replication slave can connect to master using an account +# which authenticates with an external authentication plugin (bug#12897501). + +# +# First stop the slave to guarantee that nothing is replicated. +# +--connection slave +--echo [connection slave] +--source include/stop_slave.inc +# +# Create an replication account on the master. +# +--connection master +--echo [connection master] +CREATE USER 'plug_user' IDENTIFIED WITH 'test_plugin_server' AS 'plug_user'; +GRANT REPLICATION SLAVE ON *.* TO plug_user; +FLUSH PRIVILEGES; + +# +# Now go to slave and change the replication user. +# +--connection slave +--echo [connection slave] +--let $master_user= query_get_value(SHOW SLAVE STATUS, Master_User, 1) +CHANGE MASTER TO + MASTER_USER= 'plug_user', + MASTER_PASSWORD= 'plug_user'; + +# +# Start slave with new replication account - this should trigger connection +# to the master server. +# +--source include/start_slave.inc + +# Replicate all statements executed on master, in this case, +# (creation of the plug_user account). +# +--connection master +--sync_slave_with_master +--echo # Slave in-sync with master now. + +SELECT user, plugin, authentication_string FROM mysql.user WHERE user LIKE 'plug_user'; + +# +# Now we can stop the slave and clean up. +# +# Note: it is important that slave is stopped at this +# moment - otherwise master's cleanup statements +# would be replicated on slave! +# +--echo # Cleanup (on slave). +--source include/stop_slave.inc +eval CHANGE MASTER TO MASTER_USER='$master_user'; +DROP USER 'plug_user'; + +--echo # Cleanup (on master). +--connection master +DROP USER 'plug_user'; + +--let $rpl_only_running_threads= 1 +--source include/rpl_end.inc diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 008610569e2..b6c37cacd8a 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -2,6 +2,9 @@ # Test of cast function # +# For TIME->DATETIME conversion +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); + select CAST(1-2 AS UNSIGNED); select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER); select CAST('10 ' as unsigned integer); diff --git a/mysql-test/t/comments.test b/mysql-test/t/comments.test index ce4f4d50675..6cf69635d1e 100644 --- a/mysql-test/t/comments.test +++ b/mysql-test/t/comments.test @@ -27,6 +27,8 @@ select 1 # The rest of the row will be ignored select 1 /*M! +1 */; select 1 /*M!50000 +1 */; select 1 /*M!50300 +1 */; +select 2 /*M!99999 +1 */; +select 2 /*M!100000 +1 */; select 2 /*M!999999 +1 */; --error ER_PARSE_ERROR select 2 /*M!0000 +1 */; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index ebcad5f4af4..8bb7339ce83 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -2014,6 +2014,8 @@ create table t1 (a int, b int); create table t1 (a int, b int) select 2,2; --error ER_TABLE_EXISTS_ERROR create table t1 like t2; +--error ER_LOCK_WAIT_TIMEOUT +create or replace table t1 (a int, b int) select 2,2; disconnect user1; connection default; select * from t1; diff --git a/mysql-test/t/create_or_replace-master.opt b/mysql-test/t/create_or_replace-master.opt new file mode 100644 index 00000000000..e94228f2f33 --- /dev/null +++ b/mysql-test/t/create_or_replace-master.opt @@ -0,0 +1 @@ +--log-output=TABLE,FILE --general-log=1 --slow-query-log=1 diff --git a/mysql-test/t/create_or_replace.test b/mysql-test/t/create_or_replace.test new file mode 100644 index 00000000000..88fbdb179e0 --- /dev/null +++ b/mysql-test/t/create_or_replace.test @@ -0,0 +1,293 @@ +# +# Check CREATE OR REPLACE ALTER TABLE +# + +--source include/have_innodb.inc +--source include/have_metadata_lock_info.inc +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings + +# +# Create help table +# + +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES(1),(2),(3); + +--echo # +--echo # Check first syntax and wrong usage +--echo # + +--error ER_WRONG_USAGE +CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int); +--error ER_WRONG_USAGE +create or replace trigger trg before insert on t1 for each row set @a:=1; + +# check that we don't try to create a log table in use +--error ER_BAD_LOG_STATEMENT +create or replace table mysql.general_log (a int); +--error ER_BAD_LOG_STATEMENT +create or replace table mysql.slow_log (a int); + +--echo # +--echo # Usage when table doesn't exist +--echo # + +CREATE OR REPLACE TABLE t1 (a int); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 (a int); +DROP TABLE t1; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int); +--error ER_TABLE_EXISTS_ERROR +CREATE TEMPORARY TABLE t1 (a int, b int, c int); +DROP TEMPORARY TABLE t1; + +--echo # +--echo # Testing with temporary tables +--echo # + +CREATE OR REPLACE TABLE t1 (a int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int); +SHOW CREATE TABLE t1; +DROP TEMPORARY TABLE t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# Test also with InnoDB +create temporary table t1 (i int) engine=InnoDB; +create or replace temporary table t1 (a int, b int) engine=InnoDB; +create or replace temporary table t1 (j int); +show create table t1; +drop table t1; + +# Using lock tables on normal tables with create or replace on temp tables +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine= innodb; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int) engine= innodb; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine=myisam; +SHOW CREATE TABLE t1; +DROP TEMPORARY TABLE t1; +SHOW CREATE TABLE t1; +# Verify that table is still locked +--error ER_TABLE_NOT_LOCKED +CREATE OR REPLACE TABLE t2 (a int); +DROP TABLE t1; +UNLOCK TABLES; + +# +# Using CREATE SELECT +# + +CREATE OR REPLACE TEMPORARY TABLE t1 (a int) SELECT * from t2; +SELECT * FROM t1; +CREATE OR REPLACE TEMPORARY TABLE t1 (b int) SELECT * from t2; +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 AS SELECT a FROM t2; +CREATE TEMPORARY TABLE IF NOT EXISTS t1(a int, b int) SELECT 1,2 FROM t2; +DROP TABLE t1; + +CREATE TABLE t1 (a int); +CREATE OR REPLACE TABLE t1 AS SELECT 1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +create table t1 (a int); +--error ER_UPDATE_TABLE_USED +create or replace table t1 as select * from t1; +--error ER_UPDATE_TABLE_USED +create or replace table t1 as select a from (select a from t1) as t3; +--error ER_UPDATE_TABLE_USED +create or replace table t1 as select a from t2 where t2.a in (select a from t1); +drop table t1; + +--echo # +--echo # Testing with normal tables +--echo # + +CREATE OR REPLACE TABLE t1 (a int); +CREATE OR REPLACE TABLE t1 (a int, b int); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a int) SELECT * from t2; +SELECT * FROM t1; +TRUNCATE TABLE t1; +CREATE TABLE IF NOT EXISTS t1 (a int) SELECT * from t2; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (i int); +CREATE OR REPLACE TABLE t1 AS SELECT 1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# Using lock tables with CREATE OR REPLACE +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write,t2 write; +CREATE OR REPLACE TABLE t1 (a int, b int); +# Verify if table is still locked +SELECT * FROM t1; +INSERT INTO t1 values(1,1); +CREATE OR REPLACE TABLE t1 (a int, b int, c int); +INSERT INTO t1 values(1,1,1); +--error ER_TABLE_NOT_LOCKED +CREATE OR REPLACE TABLE t3 (a int); +UNLOCK TABLES; +DROP TABLE t1; + +# Using lock tables with CREATE OR REPLACE ... SELECT +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write,t2 write; +CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2; +# Verify if table is still locked +SELECT * FROM t2; +SELECT * FROM t1; +SELECT * FROM t1; +INSERT INTO t1 values(1,1,1); +CREATE OR REPLACE TABLE t1 (a int, b int, c int, d int); +INSERT INTO t1 values(1,1,1,1); +--error ER_TABLE_NOT_LOCKED +CREATE OR REPLACE TABLE t3 (a int); +UNLOCK TABLES; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write,t2 write, t1 as t1_read read; +CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2; +SELECT * FROM t1; +SELECT * FROM t2; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM t1 as t1_read; +DROP TABLE t1; +UNLOCK TABLES; + +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLE t1 WRITE; +CREATE OR REPLACE TABLE t1 AS SELECT 1; +SELECT * from t1; +--error ER_TABLE_NOT_LOCKED +SELECT * from t2; +DROP TABLE t1; + +--echo # +--echo # Test also with InnoDB (transactional engine) +--echo # + +create table t1 (i int) engine=innodb; +lock table t1 write; +create or replace table t1 (j int); +unlock tables; +show create table t1; +drop table t1; + +create table t1 (i int) engine=InnoDB; +lock table t1 write, t2 write; +create or replace table t1 (j int) engine=innodb; +unlock tables; +drop table t1; + +create table t1 (i int) engine=InnoDB; +create table t3 (i int) engine=InnoDB; +insert into t3 values(1),(2),(3); +lock table t1 write, t2 write, t3 write; +create or replace table t1 (a int, i int) engine=innodb select t2.a,t3.i from t2,t3; +unlock tables; +select * from t1 order by a,i; +drop table t1,t3; + +--echo # +--echo # Testing CREATE .. LIKE +--echo # + +create or replace table t1 like t2; +create or replace table t1 like t2; +show create table t1; +drop table t1; +create table t1 (b int); +lock tables t1 write, t2 read; +create or replace table t1 like t2; +SELECT * FROM t1; +INSERT INTO t1 values(1); +CREATE OR REPLACE TABLE t1 like t2; +INSERT INTO t1 values(2); +unlock tables; +show create table t1; +drop table t1; + +create or replace table t1 like t2; +--error ER_NONUNIQ_TABLE +create or replace table t1 like t1; +drop table t1; + +CREATE TEMPORARY TABLE t1 like t2; +--error ER_NONUNIQ_TABLE +CREATE OR REPLACE TABLE t1 like t1; +--error ER_NONUNIQ_TABLE +CREATE OR REPLACE TABLE t1 like t1; +drop table t1; + +CREATE TEMPORARY TABLE t1 like t2; +CREATE OR REPLACE TEMPORARY TABLE t3 like t1; +--error ER_NONUNIQ_TABLE +CREATE OR REPLACE TEMPORARY TABLE t3 like t3; +drop table t1,t3; + +--echo # +--echo # Test with prepared statements +--echo # + +prepare stmt1 from 'create or replace table t1 select * from t2'; +execute stmt1; +select * from t1; +execute stmt1; +select * from t1; +drop table t1; +execute stmt1; +select * from t1; +deallocate prepare stmt1; +drop table t1; + +--echo # +--echo # Test with views +--echo # + +create view t1 as select 1; +create table if not exists t1 (a int); +--error ER_IT_IS_A_VIEW +create or replace table t1 (a int); +--error ER_IT_IS_A_VIEW +drop table t1; +drop view t1; + +--echo # +--echo # MDEV-5602 CREATE OR REPLACE obtains stricter locks than the +--echo # connection had before +--echo # + +create table t1 (a int); +lock table t1 write, t2 read; +--replace_column 1 # +select * from information_schema.metadata_lock_info; +create or replace table t1 (i int); +--replace_column 1 # +select * from information_schema.metadata_lock_info; +create or replace table t1 like t2; +--replace_column 1 # +select * from information_schema.metadata_lock_info; +create or replace table t1 select 1 as f1; +--replace_column 1 # +select * from information_schema.metadata_lock_info; +drop table t1; +unlock tables; + +# +# Cleanup +# +DROP TABLE t2; diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index 7e6990f4754..972543aefc2 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -32,6 +32,8 @@ ORDER BY variable_name; # SET date_format='%d.%m.%Y'; # SET date_format='%m-%d-%Y'; # +# --error ER_WRONG_VALUE_FOR_VAR +# SET datetime_format= NULL; # set datetime_format= '%Y%m%d%H%i%s'; # set datetime_format= '%Y-%m-%d %H:%i:%s'; # set datetime_format= '%m-%d-%y %H:%i:%s.%f'; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 559a8b76280..4b1d7604b9d 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -394,6 +394,35 @@ WHERE tmp.b; SELECT * FROM ( SELECT 100 a, subsel.b FROM ( SELECT 200 b ) subsel ) tmp WHERE tmp.a; + +--echo # +--echo # MDEV-5356: Server crashes in Item_equal::contains on 2nd +--echo # execution of a PS +--echo # +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(3,4); + +CREATE TABLE t2 (c INT); +INSERT INTO t2 VALUES (5),(6); + +CREATE TABLE t3 (d INT); +INSERT INTO t3 VALUES (7),(8); + +CREATE PROCEDURE pr() + UPDATE t3, + (SELECT c FROM + (SELECT 1 FROM t1 WHERE a=72 AND NOT b) sq, + t2 + ) sq2 + SET d=sq2.c; + +CALL pr(); +CALL pr(); +CALL pr(); + +drop procedure pr; +drop table t1,t2,t3; + --echo # End of 5.3 tests --echo # diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 3da58d8ae23..61e11cebad4 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1591,6 +1591,118 @@ EXPLAIN EXTENDED SELECT a FROM v1 WHERE a > 100 ORDER BY b; DROP VIEW v1; DROP TABLE t1; +# +# MDEV-5414: RAND() in a subselect : different behavior in MariaDB and MySQL +# +CREATE TABLE IF NOT EXISTS `galleries` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(100) NOT NULL, + `year` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `pictures` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(100) NOT NULL, + `width` float DEFAULT NULL, + `height` float DEFAULT NULL, + `year` int(4) DEFAULT NULL, + `technique` varchar(50) DEFAULT NULL, + `comment` varchar(2000) DEFAULT NULL, + `gallery_id` int(11) NOT NULL, + `type` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `gallery_id` (`gallery_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; + +ALTER TABLE `pictures` + ADD CONSTRAINT `pictures_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`); + +INSERT INTO `galleries` (`id`, `name`, `year`) VALUES +(1, 'Quand le noir et blanc invite le taupe', 2013), +(2, 'Une touche de couleur', 2012), +(3, 'Éclats', 2011), +(4, 'Gris béton', 2010), +(5, 'Expression du spalter', 2010), +(6, 'Zénitude', 2009), +(7, 'La force du rouge', 2008), +(8, 'Sphères', NULL), +(9, 'Centre', 2009), +(10, 'Nébuleuse', NULL); + +INSERT INTO `pictures` (`id`, `name`, `width`, `height`, `year`, `technique`, `comment`, `gallery_id`, `type`) VALUES +(1, 'Éclaircie', 72.5, 100, NULL, NULL, NULL, 1, 1), +(2, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1), +(3, 'Nouveau souffle', 72.5, 100, NULL, NULL, NULL, 1, 1), +(4, 'Échanges (2)', 89, 116, NULL, NULL, NULL, 1, 1), +(5, 'Échanges', 89, 116, NULL, NULL, NULL, 1, 1), +(6, 'Fenêtre de vie', 81, 116, NULL, NULL, NULL, 1, 1), +(7, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1), +(8, 'Nouveau souffle (2)', 72.5, 100, NULL, NULL, NULL, 1, 1), +(9, 'Fluidité', 89, 116, NULL, NULL, NULL, 1, 1), +(10, 'Nouveau Monde', 89, 125, NULL, NULL, NULL, 1, 1), +(11, 'Mirage', 73, 100, NULL, NULL, NULL, 1, 1), +(12, 'Équilibre', 72.5, 116, NULL, NULL, NULL, 2, 1), +(13, 'Fusion', 72.5, 116, NULL, NULL, NULL, 2, 1), +(14, 'Étincelles', NULL, NULL, NULL, NULL, NULL, 3, 1), +(15, 'Régénérescence', NULL, NULL, NULL, NULL, NULL, 3, 1), +(16, 'Chaleur', 80, 80, NULL, NULL, NULL, 4, 1), +(17, 'Création', 90, 90, NULL, NULL, NULL, 4, 1), +(18, 'Horizon', 92, 73, NULL, NULL, NULL, 4, 1), +(19, 'Labyrinthe', 81, 100, NULL, NULL, NULL, 4, 1), +(20, 'Miroir', 80, 116, NULL, NULL, NULL, 5, 1), +(21, 'Libération', 81, 116, NULL, NULL, NULL, 5, 1), +(22, 'Éclats', 81, 116, NULL, NULL, NULL, 5, 1), +(23, 'Zénitude', 116, 89, NULL, NULL, NULL, 6, 1), +(24, 'Écritures lointaines', 90, 90, NULL, NULL, NULL, 7, 1), +(25, 'Émergence', 80, 80, NULL, NULL, NULL, 7, 1), +(26, 'Liberté', 50, 50, NULL, NULL, NULL, 7, 1), +(27, 'Silhouettes amérindiennes', 701, 70, NULL, NULL, NULL, 7, 1), +(28, 'Puissance', 81, 100, NULL, NULL, NULL, 8, 1), +(29, 'Source', 73, 116, NULL, NULL, NULL, 8, 1), +(30, 'Comme une ville qui prend vie', 50, 100, 2008, NULL, NULL, 9, 1), +(31, 'Suspension azur', 80, 80, NULL, NULL, NULL, 9, 1), +(32, 'Nébuleuse', 70, 70, NULL, NULL, NULL, 10, 1), +(33, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(34, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(35, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(36, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(37, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(38, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2); + +# Now we only lest explain to be sure that table materialized. If +# in the future merged derived table will be processed in a way that +# rand() can be called only once then other way of testing correctness +# of this query should be put here. +explain +SELECT g.id AS gallery_id, + g.name AS gallery_name, + p.id AS picture_id, + p.name AS picture_name, + g.p_random AS r1, + g.p_random AS r2, + g.p_random AS r3 +FROM +( + SELECT gal.id, + gal.name, + ( + SELECT pi.id + FROM pictures pi + WHERE pi.gallery_id = gal.id + ORDER BY RAND() + LIMIT 1 + ) AS p_random + FROM galleries gal +) g +LEFT JOIN pictures p + ON p.id = g.p_random +ORDER BY gallery_name ASC +; + +drop table galleries, pictures; + --echo # --echo # end of 5.3 tests --echo # diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 71643a25c5a..952609be7e0 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -750,4 +750,42 @@ INSERT INTO t1 VALUES (1111, 2222), (3333, 4444); SELECT DISTINCT CONCAT(a,b) AS c FROM t1 ORDER BY 1; DROP TABLE t1; +--echo # +--echo # Bug#16539979 BASIC SELECT COUNT(DISTINCT ID) IS BROKEN. +--echo # Bug#17867117 ERROR RESULT WHEN "COUNT + DISTINCT + CASE WHEN" NEED MERGE_WALK +--echo # + +SET @tmp_table_size_save= @@tmp_table_size; +SET @@tmp_table_size= 1024; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO t1 SELECT a+8 FROM t1; +INSERT INTO t1 SELECT a+16 FROM t1; +INSERT INTO t1 SELECT a+32 FROM t1; +INSERT INTO t1 SELECT a+64 FROM t1; +INSERT INTO t1 VALUE(NULL); +SELECT COUNT(DISTINCT a) FROM t1; +SELECT COUNT(DISTINCT (a+0)) FROM t1; +DROP TABLE t1; + +create table tb( +id int auto_increment primary key, +v varchar(32)) +engine=myisam charset=gbk; +insert into tb(v) values("aaa"); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); + +update tb set v=concat(v, id); +select count(distinct case when id<=64 then id end) from tb; +select count(distinct case when id<=63 then id end) from tb; +drop table tb; + +SET @@tmp_table_size= @tmp_table_size_save; + --echo End of 5.5 tests diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test index 4fba43b2cce..39070cc90d7 100644 --- a/mysql-test/t/dyncol.test +++ b/mysql-test/t/dyncol.test @@ -248,8 +248,10 @@ select column_get(column_create(1, "20010203"), 1 as datetime); select column_get(column_create(1, 0), 1 as datetime); select column_get(column_create(1, "2001021"), 1 as datetime); +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); select column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime); select column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime); +SET timestamp=DEFAULT; set @@sql_mode="allow_invalid_dates"; select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime); diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test index ea7dd12fb9e..58ead1e3ced 100644 --- a/mysql-test/t/error_simulation.test +++ b/mysql-test/t/error_simulation.test @@ -103,7 +103,7 @@ CREATE TABLE t2(f1 INT); INSERT INTO t1 VALUES (1),(2); INSERT INTO t2 VALUES (1),(2); SET SESSION debug_dbug="+d,bug11747970_raise_error"; ---error 1105 +--error ER_QUERY_INTERRUPTED INSERT IGNORE INTO t2 SELECT f1 FROM t1 a WHERE NOT EXISTS (SELECT 1 FROM t2 b WHERE a.f1 = b.f1); SET SESSION debug_dbug = DEFAULT; DROP TABLE t1,t2; diff --git a/mysql-test/t/events_trans.test b/mysql-test/t/events_trans.test index 4cf2583ac96..6d829379fea 100644 --- a/mysql-test/t/events_trans.test +++ b/mysql-test/t/events_trans.test @@ -146,3 +146,4 @@ SELECT * FROM t2; ROLLBACK WORK TO SAVEPOINT A; DROP TABLE t1, t2; +DROP EVENT e1; diff --git a/mysql-test/t/flush-innodb-notembedded.test b/mysql-test/t/flush-innodb-notembedded.test new file mode 100644 index 00000000000..d08a0647ff5 --- /dev/null +++ b/mysql-test/t/flush-innodb-notembedded.test @@ -0,0 +1,69 @@ +--source include/have_innodb.inc +--source include/not_embedded.inc + +--echo # Test 7: Check privileges required. +--echo # + +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT) engine= InnoDB; +GRANT RELOAD, SELECT, LOCK TABLES ON *.* TO user1@localhost; +GRANT CREATE, DROP ON *.* TO user2@localhost; +GRANT RELOAD, SELECT ON *.* TO user3@localhost; +GRANT SELECT, LOCK TABLES ON *.* TO user4@localhost; +GRANT RELOAD, LOCK TABLES ON *.* TO user5@localhost; + +--echo # Connection con1 as user1 +--connect(con1, localhost, user1) +FLUSH TABLE db1.t1 FOR EXPORT; +UNLOCK TABLES; +--disconnect con1 +--source include/wait_until_disconnected.inc + +--echo # Connection default +--connection default + +--echo # Connection con1 as user2 +--connect(con1, localhost, user2) +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +FLUSH TABLE db1.t1 FOR EXPORT; +--disconnect con1 +--source include/wait_until_disconnected.inc + +--echo # Connection default +--connection default + +--echo # Connection con1 as user3 +--connect(con1, localhost, user3) +--error ER_DBACCESS_DENIED_ERROR +FLUSH TABLE db1.t1 FOR EXPORT; +--disconnect con1 +--source include/wait_until_disconnected.inc + +--echo # Connection default +--connection default + +--echo # Connection con1 as user4 +--connect(con1, localhost, user4) +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +FLUSH TABLE db1.t1 FOR EXPORT; +--disconnect con1 +--source include/wait_until_disconnected.inc + +--echo # Connection default +--connection default + +--echo # Connection con1 as user5 +--connect(con1, localhost, user5) +--error ER_TABLEACCESS_DENIED_ERROR +FLUSH TABLE db1.t1 FOR EXPORT; +--disconnect con1 +--source include/wait_until_disconnected.inc + +--echo # Connection default +--connection default +DROP USER user1@localhost, user2@localhost, user3@localhost, + user4@localhost, user5@localhost; +DROP TABLE db1.t1; +DROP DATABASE db1; + +--echo # End of 5.6 tests diff --git a/mysql-test/t/flush-innodb.test b/mysql-test/t/flush-innodb.test index 207032b1acb..7a877b977ce 100644 --- a/mysql-test/t/flush-innodb.test +++ b/mysql-test/t/flush-innodb.test @@ -7,3 +7,472 @@ CREATE TABLE t1 ( m MEDIUMTEXT ) ENGINE=InnoDB; INSERT INTO t1 VALUES ( REPEAT('i',1048576) ); DROP TABLE t1; + +--echo +--echo # +--echo # WL#6168: FLUSH TABLES ... FOR EXPORT -- parser +--echo # +--echo + +--echo # Requires innodb_file_per_table +SET @old_innodb_file_per_table= @@GLOBAL.innodb_file_per_table; +SET GLOBAL innodb_file_per_table= 1; + +--echo # new "EXPORT" keyword is a valid user variable name: + +SET @export = 10; + +--echo # new "EXPORT" keyword is a valid SP parameter name: + +CREATE PROCEDURE p1(export INT) BEGIN END; +DROP PROCEDURE p1; + +--echo # new "EXPORT" keyword is a valid local variable name: + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + DECLARE export INT; +END| +DELIMITER ;| +DROP PROCEDURE p1; + +--echo # new "EXPORT" keyword is a valid SP name: + +CREATE PROCEDURE export() BEGIN END; +DROP PROCEDURE export; + +--echo # new FLUSH TABLES ... FOR EXPORT syntax: + +--error ER_PARSE_ERROR +FLUSH TABLES FOR EXPORT; +--error ER_PARSE_ERROR +FLUSH TABLES WITH EXPORT; + + +CREATE TABLE t1 (i INT) engine=InnoDB; +CREATE TABLE t2 LIKE t1; + +--error ER_PARSE_ERROR +FLUSH TABLES t1,t2 WITH EXPORT; + +FLUSH TABLES t1, t2 FOR EXPORT; +UNLOCK TABLES; + +--echo # case check +FLUSH TABLES t1, t2 for ExPoRt; +UNLOCK TABLES; +--echo # With LOCAL keyword +FLUSH LOCAL TABLES t1, t2 FOR EXPORT; +UNLOCK TABLES; +--echo # Tables with fully qualified names +FLUSH LOCAL TABLES test.t1, test.t2 for ExPoRt; +UNLOCK TABLES; + +DROP TABLES t1, t2; + +--echo # new "EXPORT" keyword is a valid table name: + +CREATE TABLE export (i INT) engine=InnoDB; + +--echo # it's ok to lock the "export" table for export: + +FLUSH TABLE export FOR EXPORT; +UNLOCK TABLES; + +DROP TABLE export; + + +--echo # +--echo # WL#6169 FLUSH TABLES ... FOR EXPORT -- runtime +--echo # + +--echo # Test 1: Views, temporary tables, non-existent tables +--echo # + +CREATE VIEW v1 AS SELECT 1; +CREATE TEMPORARY TABLE t1 (a INT); + +--error ER_WRONG_OBJECT +FLUSH TABLES v1 FOR EXPORT; +--error ER_NO_SUCH_TABLE +FLUSH TABLES t1 FOR EXPORT; +--error ER_NO_SUCH_TABLE +FLUSH TABLES non_existent FOR EXPORT; + +DROP TEMPORARY TABLE t1; +DROP VIEW v1; + +--echo # Test 2: Blocked by update transactions, blocks updates. +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) engine= InnoDB; +CREATE TABLE t2 (a INT) engine= InnoDB; + +--echo # Connection con1 +--connect (con1, localhost, root) +START TRANSACTION; +INSERT INTO t1 VALUES (1, 1); + +--echo # Connection default +--connection default +--echo # Should be blocked +--echo # Sending: +--send FLUSH TABLES t1 FOR EXPORT + +--echo # Connection con1 +--connection con1 +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "FLUSH TABLES t1 FOR EXPORT"; +--source include/wait_condition.inc +COMMIT; + +--echo # Connection default +--connection default +--echo # Reaping: FLUSH TABLES t1 FOR EXPORT +--reap + +--echo # Connection con1 +--connection con1 +--echo # Should not be blocked +INSERT INTO t2 VALUES (1); +--echo # Should be blocked +--echo # Sending: +--send INSERT INTO t1 VALUES (2, 2) + +--echo # Connection default +--connection default +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "INSERT INTO t1 VALUES (2, 2)"; +--source include/wait_condition.inc +UNLOCK TABLES; + +--echo # Connection con1 +--connection con1 +--echo # Reaping: INSERT INTO t1 VALUES (2, 2); +--reap + +--echo # Test 3: Read operations should not be affected. +--echo # + +START TRANSACTION; +SELECT * FROM t1; + +--echo # Connection default +--connection default +--echo # Should not be blocked +FLUSH TABLES t1 FOR EXPORT; + +--echo # Connection con1 +--connection con1 +COMMIT; +--echo # Should not be blocked +SELECT * FROM t1; + +--echo # Connection default +--connection default +UNLOCK TABLES; + +--echo # Test 4: Blocked by DDL, blocks DDL. +--echo # + +START TRANSACTION; +SELECT * FROM t1; + +--echo # Connection con2 +--connect (con2, localhost, root) +--echo # Sending: +--send ALTER TABLE t1 ADD INDEX i1(b) + +--echo # Connection con1 +--connection con1 +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "ALTER TABLE t1 ADD INDEX i1(b)"; +--source include/wait_condition.inc +--echo # Should be blocked +--send FLUSH TABLE t1 FOR EXPORT + +--echo # Connection default +--connection default +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "FLUSH TABLE t1 FOR EXPORT"; +--source include/wait_condition.inc +COMMIT; + +--echo # Connection con2 +--connection con2 +--echo # Reaping ALTER TABLE ... +--reap + +--echo # Connection con1 +--connection con1 +--echo # Reaping FLUSH TABLE t1 FOR EXPORT +--reap +UNLOCK TABLES; + +--echo # Connection default +--connection default +FLUSH TABLE t1 FOR EXPORT; + +--echo # Connection con2 +--connection con2 +--echo # Should be blocked +--send DROP TABLE t1 + +--echo # Connection default +--connection default +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "DROP TABLE t1"; +--source include/wait_condition.inc +UNLOCK TABLES; + +--echo # Connection con2 +--connection con2 +--echo # Reaping DROP TABLE t1 +--reap +--disconnect con2 +--source include/wait_until_disconnected.inc + +--echo # Connection default +--connection default +DROP TABLE t2; + +--echo # Test 5: Compatibilty with FLUSH TABLES WITH READ LOCK +--echo # + +CREATE TABLE t1(a INT) engine= InnoDB; +FLUSH TABLES WITH READ LOCK; + +--echo # Connection con1 +--connection con1 +--echo # This should not block +FLUSH TABLE t1 FOR EXPORT; +UNLOCK TABLES; + +--echo # Connection default +--connection default +UNLOCK TABLES; +DROP TABLE t1; + +--echo # Test 6: Unsupported storage engines. +--echo # + +CREATE TABLE t1(a INT) engine= MEMORY; +--error ER_ILLEGAL_HA +FLUSH TABLE t1 FOR EXPORT; +DROP TABLE t1; + +--echo # Connection con1 +--connection con1 +--disconnect con1 +--source include/wait_until_disconnected.inc + +--echo # Connection defalt +--connection default + +--echo # Test 7: Check privileges required. +--echo # in flush-innodb-notembedded.test + +--echo # Test 8: FLUSH TABLE <table_list> FOR EXPORT is incompatible +--echo # with itself (to avoid race conditions in metadata +--echo # file handling). +--echo # + +CREATE TABLE t1 (a INT) engine= InnoDB; +CREATE TABLE t2 (a INT) engine= InnoDB; + +--echo # Connection con1 +--connect (con1, localhost, root) +FLUSH TABLE t1 FOR EXPORT; + +--echo # Connection default +--connection default +--echo # This should not block +FLUSH TABLE t2 FOR EXPORT; +UNLOCK TABLES; +--echo # This should block +--echo # Sending: +--send FLUSH TABLE t1 FOR EXPORT + +--echo # Connection con1 +--connection con1 +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "FLUSH TABLE t1 FOR EXPORT"; +--source include/wait_condition.inc +UNLOCK TABLES; + +--echo # Connection default +--connection default +--echo # Reaping: FLUSH TABLE t1 FOR EXPORT +--reap +UNLOCK TABLES; + +--echo # Test 9: LOCK TABLES ... READ is not affected +--echo # + +LOCK TABLE t1 READ; + +--echo # Connection con1 +--connection con1 +--echo # Should not block +FLUSH TABLE t1 FOR EXPORT; +UNLOCK TABLES; + +--echo # Connection default +--connection default +UNLOCK TABLES; +FLUSH TABLE t1 FOR EXPORT; + +--echo # Connection con1 +--connection con1 +--echo # Should not block +LOCK TABLE t1 READ; +UNLOCK TABLES; + +--echo # Connection default +--connection default +UNLOCK TABLES; + +--echo # Connection con1 +--connection con1 +--disconnect con1 +--source include/wait_until_disconnected.inc + +--echo # Connection default +--connection default +DROP TABLE t1, t2; + +--echo # Test 10: Lock is released if transaction is started after doing +--echo # 'flush table..' in same session + +CREATE TABLE t1 ( i INT ) ENGINE = Innodb; +FLUSH TABLE t1 FOR EXPORT; +--echo # error as active locks already exist +--error ER_LOCK_OR_ACTIVE_TRANSACTION +FLUSH TABLE t1 FOR EXPORT; +--echo # active locks will be released due to start transaction +START TRANSACTION; +--echo # passes as start transaction released ealier locks +FLUSH TABLE t1 FOR EXPORT; +UNLOCK TABLES; +DROP TABLE t1; + +--echo # Test 11: Test 'flush table with fully qualified table names +--echo # and with syntax local/NO_WRITE_TO_BINLOG + +--echo # Connection con1 +--connect (con1, localhost, root) + +--echo # Connection default +--connection default +CREATE TABLE t1 ( i INT ) ENGINE = Innodb; +INSERT INTO t1 VALUES (100),(200); +FLUSH LOCAL TABLES test.t1 FOR EXPORT; +--echo # Connection con1 +--connection con1 +--echo # Should be blocked +--echo # Sending: +--send FLUSH LOCAL TABLES t1 FOR EXPORT + +--echo # Connection default +--connection default +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "FLUSH LOCAL TABLES t1 FOR EXPORT"; +--source include/wait_condition.inc +UNLOCK TABLE; + +--echo # Connection con1 +--connection con1 +--echo # Reaping: FLUSH LOCAL TABLES t1 FOR EXPORT +--reap +SELECT * FROM t1 ORDER BY i; + +--echo # Connection default +--connection default +--echo # Should be blocked +--echo # Sending: +--send FLUSH NO_WRITE_TO_BINLOG TABLES test.t1 FOR EXPORT + +--echo # Connection con1 +--connection con1 +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "FLUSH NO_WRITE_TO_BINLOG TABLES test.t1 FOR EXPORT"; +--source include/wait_condition.inc +UNLOCK TABLES; + +--echo # Connection default +--connection default +--echo # Reaping: FLUSH NO_WRITE_TO_BINLOG TABLES test.t1 FOR EXPORT +--reap +SELECT * FROM t1 ORDER BY i; +UNLOCK TABLE; +DROP TABLE t1; + +--echo # Test 12: Active transaction get committed if user execute +--echo # "FLUSH TABLE ... FOR EXPORT" or "LOCK TABLE.." + +--echo # Connection default +--connection default +CREATE TABLE t1 ( i INT ) ENGINE = Innodb; +INSERT INTO t1 VALUES (100),(200); +START TRANSACTION; +INSERT INTO t1 VALUES (300); +--echo # 'flush table..' commit active transaction from same session +FLUSH LOCAL TABLES test.t1 FOR EXPORT; +ROLLBACK; +SELECT * FROM t1 ORDER BY i; +START TRANSACTION; +INSERT INTO t1 VALUES (400); +--echo # 'lock table ..' commit active transaction from same session +LOCK TABLES test.t1 READ; +ROLLBACK; +SELECT * FROM t1 ORDER BY i; +UNLOCK TABLES; +DROP TABLE t1; + +--echo # Test 13: Verify "FLUSH TABLE ... FOR EXPORT" and "LOCK TABLE.." +--echo # in same session +--echo # Connection default + +--connection default +CREATE TABLE t1 ( i INT ) ENGINE = Innodb; +--echo # Lock table +LOCK TABLES test.t1 WRITE; +--echo # 'lock table ..' completes even if table lock is acquired +--echo # in same session using 'lock table'. Previous locks are released. +LOCK TABLES test.t1 READ; +--echo # 'flush table ..' gives error if table lock is acquired +--echo # in same session using 'lock table ..' +--error ER_LOCK_OR_ACTIVE_TRANSACTION +FLUSH TABLES test.t1 FOR EXPORT; +--echo # 'lock table ..' completes even if table lock is acquired +--echo # in same session using 'flush table'. Previous locks are released. +LOCK TABLES test.t1 WRITE; +UNLOCK TABLES; +DROP TABLE t1; + +--connection con1 +--disconnect con1 +--source include/wait_until_disconnected.inc +--connection default + +--echo # Reset innodb_file_per_table +SET GLOBAL innodb_file_per_table= @old_innodb_file_per_table; + +--echo # End of 5.6 tests diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 936b93b49c9..7643676ea61 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -49,9 +49,9 @@ select grp,group_concat(c order by grp desc) from t1 group by grp order by grp; # Test transfer to real values -select grp, group_concat(a separator "")+0 from t1 group by grp;
-select grp, group_concat(a separator "")+0.0 from t1 group by grp;
-select grp, ROUND(group_concat(a separator "")) from t1 group by grp;
+select grp, group_concat(a separator "")+0 from t1 group by grp; +select grp, group_concat(a separator "")+0.0 from t1 group by grp; +select grp, ROUND(group_concat(a separator "")) from t1 group by grp; drop table t1; # Test NULL values diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index cf5f00c3ee1..363f089e8d7 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -981,10 +981,13 @@ DROP TABLE D; # # Bug #39656: Behaviour different for agg functions with & without where - # ONLY_FULL_GROUP_BY +# MDEV-5617 mysqld crashes when running a query with ONLY_FULL_GROUP_BY # CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (1,1), (1,2), (1,3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); SET SQL_MODE='ONLY_FULL_GROUP_BY'; @@ -1000,11 +1003,18 @@ SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a; SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) FROM t1 outr; +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +SELECT COUNT(*) FROM t1 outr, (SELECT b, count(*) FROM t2) as t3; + +SELECT COUNT(*) FROM t1 outr where (1,1) in (SELECT a, count(*) FROM t2); + SELECT COUNT(*) FROM t1 a JOIN t1 outr ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a); +SELECT * FROM (SELECT a FROM t1 GROUP BY a) sq JOIN t2 ON a = b; + SET SQL_MODE=default; -DROP TABLE t1; +DROP TABLE t1,t2; ### diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 22ebb6248e2..a7ae99cd2d0 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -298,6 +298,14 @@ DROP TABLE t1; SELECT INET_NTOA(0); SELECT '1' IN ('1', INET_NTOA(0)); +# +# MDEV-5655 Server crashes on NAME_CONST containing AND/OR expressions +# +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('a', -(1 OR 2)) OR 1; +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('a', -(1 AND 2)) AND 1; +SELECT NAME_CONST('a', -(1)) OR 1; --echo # --echo # Bug #52165: Assertion failed: file .\dtoa.c, line 465 @@ -549,6 +557,3 @@ select release_lock(repeat('a', 193)); --echo # End of 5.5 tests --echo # ---echo # ---echo # End of tests ---echo # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 9b1d71db10d..c889dec927e 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1103,7 +1103,9 @@ drop table t1; # # lp:731815 Crash/valgrind warning Item::send with 5.1-micro # +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow'); +SET timestamp=DEFAULT; # # lp:736370 Datetime functions in subquery context cause wrong result and bogus warnings in mysql-5.1-micr @@ -1425,12 +1427,14 @@ SHOW COLUMNS FROM t1; SELECT * FROM t1; DROP TABLE t1; +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); CREATE TABLE t1 AS SELECT TIMESTAMP('00:00:00','10:10:10'), TIMESTAMP(TIME('00:00:00'),'10:10:10'); SHOW COLUMNS FROM t1; SELECT * FROM t1; DROP TABLE t1; +SET timestamp=DEFAULT; --echo # --echo # MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1) @@ -1440,6 +1444,7 @@ SELECT MAKETIME(0, 0, -0.1); --echo # --echo # MDEV-4857 Wrong result of HOUR('1 00:00:00') --echo # +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); SELECT HOUR('1 02:00:00'), HOUR('26:00:00'); SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00'); SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00')); @@ -1449,7 +1454,26 @@ SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00'); SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00'); SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00')); SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00')); +SET timestamp=DEFAULT; +--echo # +--echo # MDEV-5458 RQG hits 'sql/tztime.cc:799: my_time_t sec_since_epoch(int, int, int, int, int, int): Assertion `mon > 0 && mon < 13' failed.' +--echo # +SET TIMESTAMP=UNIX_TIMESTAMP('2014-01-22 18:19:20'); +CREATE TABLE t1 (t TIME); +INSERT INTO t1 VALUES ('03:22:30'),('18:30:05'); +SELECT CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00') FROM t1; +SELECT GREATEST(t, CURRENT_DATE()) FROM t1; +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; + +--echo # +--echo # MDEV-5504 Server crashes in String::length on SELECT with MONTHNAME, GROUP BY, ROLLUP +--echo # +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP; +DROP TABLE t1; --echo # --echo # MDEV-4838 Wrong metadata for DATE_ADD('string', INVERVAL) @@ -1457,3 +1481,33 @@ SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00')); --enable_metadata SELECT DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE); --disable_metadata + +--echo # +--echo # MDEV-5450 Assertion `cached_field_ type == MYSQL_TYPE_STRING || ltime.time_type == MYSQL_TIMESTAMP_NONE || mysql_type_to_time_type(cached_field_type) == ltime.time_type' fails with IF, ISNULL, ADDDATE +--echo # + +CREATE TABLE t1 (a DATETIME, b DATE); +INSERT INTO t1 VALUES (NULL, '2012-12-21'); +SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; +SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1; +SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +DROP TABLE t1; + +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +CREATE TABLE t1 (a DATETIME, b TIME); +INSERT INTO t1 VALUES (NULL, '00:20:12'); +SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; +SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1; +SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +DROP TABLE t1; +SET timestamp=DEFAULT; diff --git a/mysql-test/t/gis-precise.test b/mysql-test/t/gis-precise.test index d021bb0b53d..0c6410b5a75 100644 --- a/mysql-test/t/gis-precise.test +++ b/mysql-test/t/gis-precise.test @@ -325,3 +325,6 @@ SELECT ST_NUMPOINTS(ST_EXTERIORRING(ST_BUFFER( POLYGONFROMTEXT( 'POLYGON( ( 0.0 0.0 -3.0 ))' ), 3 ))); +# MDEV-5615 crash in Gcalc_function::add_operation +select astext(buffer(st_linestringfromwkb(linestring(point(-1,1), point(-1,-2))),-1)); + diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index c1153d5071d..c38706959e4 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1434,6 +1434,24 @@ SELECT 1 FROM g1 WHERE a >= ANY DROP TABLE g1; --echo # +--echo # Bug#16451878 GEOMETRY QUERY CRASHES SERVER +--echo # + +--echo # should not crash +SELECT ASTEXT(0x0100000000030000000100000000000010); + +--echo #should not crash +SELECT ENVELOPE(0x0100000000030000000100000000000010); + +--echo #should not crash +SELECT + GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffff0000, 1); + +--echo #should not crash +SELECT + GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1); + +--echo # --echo # MDEV-3819 missing constraints for spatial column types --echo # diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index f88b6fbb2fe..8ee17d2b2d3 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1,5 +1,3 @@ ---source include/have_innodb.inc - # Initialise --disable_warnings drop table if exists t1,t2,t3; @@ -1336,7 +1334,7 @@ INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), (11),(12),(13),(14),(15),(16),(17),(18),(19),(20); let $query0=SELECT col1 AS field1, col1 AS field2 - FROM t1 GROUP BY field1, field2; + FROM t1 GROUP BY field1, field2+0; # Needs to be range to exercise bug --eval EXPLAIN $query0; @@ -1366,7 +1364,9 @@ LIMIT 3; explain select col1 f1, col1 f2 from t1 order by f2, f1; -select col1 f1, col1 f2 from t1 order by f2, f1; +explain +select col1 f1, col1 f2 from t1 order by f2, f1+0; +select col1 f1, col1 f2 from t1 order by f2, f1+0; explain select col1 f1, col1 f2 from t1 group by f2 order by f2, f1; @@ -1386,12 +1386,12 @@ INSERT INTO t2(col1, col2) VALUES (11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1); explain -select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3; -select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3; +select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0; +select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0; explain -select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3; -select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3; +select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0; +select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0; DROP VIEW v1; DROP TABLE t1, t2; @@ -1644,30 +1644,6 @@ FROM t1 JOIN t2 ON c = b GROUP BY b WITH ROLLUP; DROP TABLE t1,t2; --echo # ---echo # Test of MDEV-4002 ---echo # - -CREATE TABLE t1 ( - pk INT NOT NULL PRIMARY KEY, - d1 DOUBLE, - d2 DOUBLE, - i INT NOT NULL DEFAULT '0', - KEY (i) -) ENGINE=InnoDB; - -INSERT INTO t1 VALUES (1,1.0,1.1,1),(2,2.0,2.2,2); - -PREPARE stmt FROM " -SELECT DISTINCT i, GROUP_CONCAT( d1, d2 ORDER BY d1, d2 ) -FROM t1 a1 NATURAL JOIN t1 a2 GROUP BY i WITH ROLLUP -"; - -EXECUTE stmt; -EXECUTE stmt; - -DROP TABLE t1; - ---echo # --echo # Bug #58782 --echo # Missing rows with SELECT .. WHERE .. IN subquery --echo # with full GROUP BY and no aggr diff --git a/mysql-test/t/group_by_innodb.test b/mysql-test/t/group_by_innodb.test index 0d5e5e9ae30..df213cc189f 100644 --- a/mysql-test/t/group_by_innodb.test +++ b/mysql-test/t/group_by_innodb.test @@ -4,6 +4,8 @@ --source include/have_innodb.inc +set @save_ext_key_optimizer_switch=@@optimizer_switch; + --echo # --echo # MDEV-3992 Server crash or valgrind errors in test_if_skip_sort_order/test_if_cheaper_ordering --echo # on GROUP BY with indexes on InnoDB table @@ -15,6 +17,8 @@ CREATE TABLE t1 ( KEY (pk) ) ENGINE=InnoDB; +set optimizer_switch='extended_keys=on'; + INSERT INTO t1 VALUES (1,'a'),(2,'b'); EXPLAIN @@ -35,4 +39,31 @@ WHERE a = 'r' OR pk = 183 GROUP BY field1, field1; drop table t1; +set optimizer_switch=@save_ext_key_optimizer_switch; + +--echo # +--echo # MDEV-4002 Server crash or valgrind errors in Item_func_group_concat::setup and Item_func_group_concat::add +--echo # + +CREATE TABLE t1 ( + pk INT NOT NULL PRIMARY KEY, + d1 DOUBLE, + d2 DOUBLE, + i INT NOT NULL DEFAULT '0', + KEY (i) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1,1.0,1.1,1),(2,2.0,2.2,2); + +PREPARE stmt FROM " +SELECT DISTINCT i, GROUP_CONCAT( d1, d2 ORDER BY d1, d2 ) +FROM t1 a1 NATURAL JOIN t1 a2 GROUP BY i WITH ROLLUP +"; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1; + --echo End of 5.5 tests + diff --git a/mysql-test/t/group_by_null.test b/mysql-test/t/group_by_null.test new file mode 100644 index 00000000000..b3fa2a003ec --- /dev/null +++ b/mysql-test/t/group_by_null.test @@ -0,0 +1,7 @@ +# +# MDEV-5461 Assertion `length <= column->length' fails in write_block_record with functions in select list, GROUP BY, ORDER BY +# +create table t1 (a int); +insert into t1 values (1),(2); +select max('foo') from t1 group by values(a), extractvalue('bar','qux') order by "v"; +drop table t1; diff --git a/mysql-test/t/information_schema-big.test b/mysql-test/t/information_schema-big.test index 5e73c867143..717c22f8f6a 100644 --- a/mysql-test/t/information_schema-big.test +++ b/mysql-test/t/information_schema-big.test @@ -14,9 +14,10 @@ DROP VIEW IF EXISTS v1; --echo # ---echo # Bug#18925: subqueries with MIN/MAX functions on INFORMARTION_SCHEMA +--echo # Bug#18925: subqueries with MIN/MAX functions on INFORMATION_SCHEMA --echo # +--sorted_result SELECT t.table_name, c1.column_name FROM information_schema.tables t INNER JOIN @@ -32,6 +33,7 @@ SELECT t.table_name, c1.column_name c2.column_name LIKE '%SCHEMA%' ) AND t.table_name NOT LIKE 'innodb%'; +--sorted_result SELECT t.table_name, c1.column_name FROM information_schema.tables t INNER JOIN diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index ed0be41c279..a1ca0bce1d1 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1796,5 +1796,61 @@ drop database mysqltest; --echo # End of 5.5 tests --echo # +--echo # +--echo # MDEV-5723: mysqldump -uroot unusable for multi-database operations, checks all databases +--echo # + +--disable_warnings +drop database if exists db1; +--enable_warnings + +connect (con1,localhost,root,,); +connection con1; + +create database db1; +use db1; +create table t1 (a int); +create table t2 (a int); +create table t3 (a int); + +create database mysqltest; +use mysqltest; +create table t1 (a int); +create table t2 (a int); +create table t3 (a int); + +flush tables; +flush status; + +SELECT + LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA +FROM + INFORMATION_SCHEMA.FILES +WHERE + FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND + LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME + FROM INFORMATION_SCHEMA.FILES + WHERE + FILE_TYPE = 'DATAFILE' AND + TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME + FROM INFORMATION_SCHEMA.PARTITIONS + WHERE TABLE_SCHEMA IN ('db1') + ) + ) +GROUP BY + LOGFILE_GROUP_NAME, FILE_NAME, ENGINE +ORDER BY + LOGFILE_GROUP_NAME; + +--echo # This must have Opened_tables=3, not 6. +show status like 'Opened_tables'; + +drop database mysqltest; +drop database db1; + +connection default; +disconnect con1; + # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test index d62217dd54f..9f3a89ff948 100644 --- a/mysql-test/t/innodb_ext_key.test +++ b/mysql-test/t/innodb_ext_key.test @@ -663,6 +663,34 @@ ORDER BY rev_timestamp ASC LIMIT 10; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-5424 SELECT using ORDER BY DESC and LIMIT produces unexpected +--echo # results (InnoDB/XtraDB) +--echo # + +create table t1 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = myisam default character set utf8; +create table t2 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = innodb default character set utf8; + +insert into t1 (b) values (null), (null), (null); +insert into t2 (b) values (null), (null), (null); + +set optimizer_switch='extended_keys=on'; +explain select a from t1 where b is null order by a desc limit 2; +select a from t1 where b is null order by a desc limit 2; +explain select a from t2 where b is null order by a desc limit 2; +select a from t2 where b is null order by a desc limit 2; +set optimizer_switch='extended_keys=off'; +explain select a from t2 where b is null order by a desc limit 2; +select a from t2 where b is null order by a desc limit 2; + +explain select a from t2 where b is null order by a desc; +select a from t2 where b is null order by a desc; + +explain select a from t2 where b is null order by a desc,a,a; +select a from t2 where b is null order by a desc,a,a; + +drop table t1, t2; + set optimizer_switch=@save_optimizer_switch; set optimizer_switch=@save_ext_key_optimizer_switch; diff --git a/mysql-test/t/innodb_icp.test b/mysql-test/t/innodb_icp.test index d6caa36a88e..acb8238e01f 100644 --- a/mysql-test/t/innodb_icp.test +++ b/mysql-test/t/innodb_icp.test @@ -45,6 +45,34 @@ ORDER BY e; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-5337: Wrong result in mariadb 5.5.32 with ORDER BY + LIMIT when index_condition_pushdown=on +--echo # MDEV-5512: Wrong result (WHERE clause ignored) with multiple clauses using Percona-XtraDB engine +--echo # + +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2 (pk int primary key, + key1 char(32), + key2 char(32), + key(key1), + key(key2) +) engine=innodb; + +insert into t2 select + A.a+10*B.a+100*C.a, + concat('rare-', A.a+10*B.a), + concat('rare-', A.a+10*B.a) +from + t1 A, t1 B, t1 C; +update t2 set key1='frequent-val' where pk between 100 and 350; +select * from t2 ignore key(PRIMARY) +where key1='frequent-val' and key2 between 'rare-400' and 'rare-450' order by pk limit 2; + +drop table t1, t2; + + set optimizer_switch=@innodb_icp_tmp; set storage_engine= @save_storage_engine; diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index f9b9fcf266d..ff8396fd7fd 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -555,3 +555,21 @@ INSERT IGNORE t1 (a, a) SELECT 1,1; INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2; DROP TABLE t1; + +--echo # +--echo # MDEV-5168: Ensure that we can disable duplicate key warnings +--echo # from INSERT IGNORE +--echo # + +create table t1 (f1 int unique, f2 int unique); +insert into t1 values (1,12); +insert into t1 values (2,13); +--error ER_DUP_ENTRY +insert into t1 values (1,12); +insert ignore into t1 values (1,12); +set @@old_mode="NO_DUP_KEY_WARNINGS_WITH_IGNORE"; +insert ignore into t1 values (1,12); +insert ignore into t1 values (1,12) on duplicate key update f2=13; +set @@old_mode=""; +insert ignore into t1 values (1,12); +DROP TABLE t1; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 54b2a3c82ea..e07a3665920 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -1185,3 +1185,18 @@ SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-5635: join of a const table with non-const tables +--echo # + +CREATE TABLE t1 (a varchar(3) NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'); + +CREATE TABLE t2 (b varchar(3), c varchar(3), INDEX(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('bar', 'bar'),( 'qux', 'qux'); + +SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2 + WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a ); + +DROP TABLE t1,t2; diff --git a/mysql-test/t/lowercase_table2.test b/mysql-test/t/lowercase_table2.test index 30433d15be4..2fcb38c229e 100644 --- a/mysql-test/t/lowercase_table2.test +++ b/mysql-test/t/lowercase_table2.test @@ -251,7 +251,7 @@ let $MYSQLD_DATADIR= `select @@datadir`; --echo # Check that still there is an entry for table in TDC. show open tables like 't_bug44738_%'; --echo # So attempt to create table with the same name should fail. ---error ER_FILE_NOT_FOUND +--error ER_TABLE_EXISTS_ERROR create table t_bug44738_UPPERCASE (i int); --echo # And should succeed after FLUSH TABLES. flush tables; diff --git a/mysql-test/t/lowercase_table4-master.opt b/mysql-test/t/lowercase_table4-master.opt index c0a1981fa7c..ac4d3211e89 100644 --- a/mysql-test/t/lowercase_table4-master.opt +++ b/mysql-test/t/lowercase_table4-master.opt @@ -1 +1 @@ ---lower-case-table-names=2
+--lower-case-table-names=2 diff --git a/mysql-test/t/lowercase_table4.test b/mysql-test/t/lowercase_table4.test index d13b1a16be1..435ff0dae66 100644 --- a/mysql-test/t/lowercase_table4.test +++ b/mysql-test/t/lowercase_table4.test @@ -1,108 +1,108 @@ ---source include/have_case_insensitive_file_system.inc
---source include/have_innodb.inc
-
---echo #
---echo # Bug#46941 crash with lower_case_table_names=2 and
---echo # foreign data dictionary confusion
---echo #
-
-CREATE DATABASE XY;
-USE XY;
-
-#
-# Logs are disabled, since the number of creates tables
-# and subsequent select statements may vary between
-# versions
-#
---disable_query_log
---disable_result_log
-
-let $tcs = `SELECT @@table_open_cache + 1`;
-
-let $i = $tcs;
-
-while ($i)
-{
- eval CREATE TABLE XY.T_$i (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT,
- primary key(a, b), unique(b)) ENGINE=InnoDB;
- dec $i;
-}
-
-eval ALTER TABLE XY.T_$tcs ADD INDEX I1 (c, b),
- ADD CONSTRAINT C1 FOREIGN KEY (c, b) REFERENCES XY.T_1 (a, b);
-
-eval ALTER TABLE XY.T_$tcs ADD INDEX I2 (b),
- ADD CONSTRAINT C2 FOREIGN KEY (b) REFERENCES XY.T_1(a);
-
-let $i = $tcs;
-while ($i)
-{
- eval SELECT * FROM XY.T_$i LIMIT 1;
- dec $i;
-}
-
-DROP DATABASE XY;
-CREATE DATABASE XY;
-USE XY;
-eval CREATE TABLE XY.T_$tcs (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT,
- PRIMARY KEY(a, b), UNIQUE(b)) ENGINE=InnoDB;
-#
-# The bug causes this SELECT to err
-eval SELECT * FROM XY.T_$tcs LIMIT 1;
-
---enable_query_log
---enable_result_log
-DROP DATABASE XY;
-USE TEST;
-
---echo #
---echo # Bug55222 Mysqldump table names case bug in REFERENCES clause
---echo # InnoDB did not handle lower_case_table_names=2 for
---echo # foreign_table_names and referenced_table_names.
---echo #
-
-SHOW VARIABLES LIKE 'lower_case_table_names';
-
---disable_warnings
-DROP TABLE IF EXISTS `Table2`;
-DROP TABLE IF EXISTS `Table1`;
---disable_warnings
-
-CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB;
-CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB;
-ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1);
-query_vertical SHOW CREATE TABLE `Table2`;
-query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test';
-DROP TABLE `Table2`;
-DROP TABLE `Table1`;
-
---disable_warnings
-DROP TABLE IF EXISTS Product_Order;
-DROP TABLE IF EXISTS Product;
-DROP TABLE IF EXISTS Customer;
---enable_warnings
-
-CREATE TABLE Product (Category INT NOT NULL, Id INT NOT NULL,
- Price DECIMAL, PRIMARY KEY(Category, Id)) ENGINE=InnoDB;
-CREATE TABLE Customer (Id INT NOT NULL, PRIMARY KEY (Id)) ENGINE=InnoDB;
-CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT,
- Product_Category INT NOT NULL,
- Product_Id INT NOT NULL,
- Customer_Id INT NOT NULL,
- PRIMARY KEY(No),
- INDEX (Product_Category, Product_Id),
- FOREIGN KEY (Product_Category, Product_Id)
- REFERENCES Product(Category, Id) ON UPDATE CASCADE ON DELETE RESTRICT,
- INDEX (Customer_Id),
- FOREIGN KEY (Customer_Id)
- REFERENCES Customer(Id)
- ) ENGINE=INNODB;
-
-query_vertical SHOW CREATE TABLE Product_Order;
-query_vertical SHOW CREATE TABLE Product;
-query_vertical SHOW CREATE TABLE Customer;
-query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test';
-DROP TABLE Product_Order;
-DROP TABLE Product;
-DROP TABLE Customer;
-
+--source include/have_case_insensitive_file_system.inc +--source include/have_innodb.inc + +--echo # +--echo # Bug#46941 crash with lower_case_table_names=2 and +--echo # foreign data dictionary confusion +--echo # + +CREATE DATABASE XY; +USE XY; + +# +# Logs are disabled, since the number of creates tables +# and subsequent select statements may vary between +# versions +# +--disable_query_log +--disable_result_log + +let $tcs = `SELECT @@table_open_cache + 1`; + +let $i = $tcs; + +while ($i) +{ + eval CREATE TABLE XY.T_$i (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT, + primary key(a, b), unique(b)) ENGINE=InnoDB; + dec $i; +} + +eval ALTER TABLE XY.T_$tcs ADD INDEX I1 (c, b), + ADD CONSTRAINT C1 FOREIGN KEY (c, b) REFERENCES XY.T_1 (a, b); + +eval ALTER TABLE XY.T_$tcs ADD INDEX I2 (b), + ADD CONSTRAINT C2 FOREIGN KEY (b) REFERENCES XY.T_1(a); + +let $i = $tcs; +while ($i) +{ + eval SELECT * FROM XY.T_$i LIMIT 1; + dec $i; +} + +DROP DATABASE XY; +CREATE DATABASE XY; +USE XY; +eval CREATE TABLE XY.T_$tcs (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT, + PRIMARY KEY(a, b), UNIQUE(b)) ENGINE=InnoDB; +# +# The bug causes this SELECT to err +eval SELECT * FROM XY.T_$tcs LIMIT 1; + +--enable_query_log +--enable_result_log +DROP DATABASE XY; +USE TEST; + +--echo # +--echo # Bug55222 Mysqldump table names case bug in REFERENCES clause +--echo # InnoDB did not handle lower_case_table_names=2 for +--echo # foreign_table_names and referenced_table_names. +--echo # + +SHOW VARIABLES LIKE 'lower_case_table_names'; + +--disable_warnings +DROP TABLE IF EXISTS `Table2`; +DROP TABLE IF EXISTS `Table1`; +--disable_warnings + +CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB; +ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1); +query_vertical SHOW CREATE TABLE `Table2`; +query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test'; +DROP TABLE `Table2`; +DROP TABLE `Table1`; + +--disable_warnings +DROP TABLE IF EXISTS Product_Order; +DROP TABLE IF EXISTS Product; +DROP TABLE IF EXISTS Customer; +--enable_warnings + +CREATE TABLE Product (Category INT NOT NULL, Id INT NOT NULL, + Price DECIMAL, PRIMARY KEY(Category, Id)) ENGINE=InnoDB; +CREATE TABLE Customer (Id INT NOT NULL, PRIMARY KEY (Id)) ENGINE=InnoDB; +CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT, + Product_Category INT NOT NULL, + Product_Id INT NOT NULL, + Customer_Id INT NOT NULL, + PRIMARY KEY(No), + INDEX (Product_Category, Product_Id), + FOREIGN KEY (Product_Category, Product_Id) + REFERENCES Product(Category, Id) ON UPDATE CASCADE ON DELETE RESTRICT, + INDEX (Customer_Id), + FOREIGN KEY (Customer_Id) + REFERENCES Customer(Id) + ) ENGINE=INNODB; + +query_vertical SHOW CREATE TABLE Product_Order; +query_vertical SHOW CREATE TABLE Product; +query_vertical SHOW CREATE TABLE Customer; +query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test'; +DROP TABLE Product_Order; +DROP TABLE Product; +DROP TABLE Customer; + diff --git a/mysql-test/t/myisam_optimize.test b/mysql-test/t/myisam_optimize.test index 2d630f7dbd0..5f0b8fc7666 100644 --- a/mysql-test/t/myisam_optimize.test +++ b/mysql-test/t/myisam_optimize.test @@ -44,4 +44,5 @@ disconnect con2; connection default; drop table t1; +set debug_sync='reset'; diff --git a/mysql-test/t/mysql_tzinfo_to_sql_symlink.test b/mysql-test/t/mysql_tzinfo_to_sql_symlink.test index d29d40f5679..1ba4e91be3c 100644 --- a/mysql-test/t/mysql_tzinfo_to_sql_symlink.test +++ b/mysql-test/t/mysql_tzinfo_to_sql_symlink.test @@ -8,8 +8,32 @@ --exec mkdir $MYSQLTEST_VARDIR/zoneinfo --exec ln -s $MYSQLTEST_VARDIR/zoneinfo $MYSQLTEST_VARDIR/zoneinfo/posix --copy_file std_data/zoneinfo/GMT $MYSQLTEST_VARDIR/zoneinfo/GMT +--copy_file std_data/words.dat $MYSQLTEST_VARDIR/zoneinfo/garbage +--copy_file std_data/words.dat $MYSQLTEST_VARDIR/zoneinfo/ignored.tab +--echo # Verbose run +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec $MYSQL_TZINFO_TO_SQL --verbose $MYSQLTEST_VARDIR/zoneinfo 2>&1 + +--echo # Silent run --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --exec $MYSQL_TZINFO_TO_SQL $MYSQLTEST_VARDIR/zoneinfo 2>&1 +--echo # +--echo # Testing with explicit timezonefile +--echo # + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec $MYSQL_TZINFO_TO_SQL $MYSQLTEST_VARDIR/zoneinfo/GMT XXX 2>&1 + +--echo # +--echo # Testing --leap +--echo # + +--exec $MYSQL_TZINFO_TO_SQL --leap $MYSQLTEST_VARDIR/zoneinfo/GMT 2>&1 + +# +# Cleanup +# + --exec rm -rf $MYSQLTEST_VARDIR/zoneinfo diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index efb1551150f..0261928ac08 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -41,6 +41,11 @@ GRANT ALL ON *.* TO mysqltest1@'%'; DROP USER mysqltest1@'%'; +# +# check that we get proper error messages if wrong user + +--error 1 +--exec $MYSQL_UPGRADE --force --user=mysqltest1 --password=sakila 2>&1 # # Bug #26639 mysql_upgrade exits successfully even if external command failed diff --git a/mysql-test/t/mysqld--help.test b/mysql-test/t/mysqld--help.test index ae75069d103..2104159b3e9 100644 --- a/mysql-test/t/mysqld--help.test +++ b/mysql-test/t/mysqld--help.test @@ -21,7 +21,8 @@ perl; @skipvars=qw/basedir open-files-limit general-log-file log plugin-dir log-slow-queries pid-file slow-query-log-file log-basename datadir slave-load-tmpdir tmpdir socket thread-pool-size - wsrep-node-name/; + large-files-support lower-case-file-system system-time-zone + wsrep-node-name version.*/; # Plugins which may or may not be there: @plugins=qw/innodb ndb archive blackhole federated partition ndbcluster diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 1ddce574a88..6e717e85122 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2440,6 +2440,21 @@ SET @@global.log_output= @old_log_output_state; SET @@global.slow_query_log= @old_slow_query_log_state; SET @@global.general_log= @old_general_log_state; +--echo # MDEV-5481 mysqldump fails to dump geometry types properly + +create table t1 (g GEOMETRY) CHARSET koi8r; +create table t2 (g GEOMETRY) CHARSET koi8r; +insert into t1 values (point(1,1)), (point(2,2)); +--exec $MYSQL_DUMP --hex-blob --character-sets-dir=$MYSQL_SHAREDIR/charsets --tab=$MYSQLTEST_VARDIR/tmp/ test t1 +--echo ################################################## +--cat_file $file +--echo ################################################## +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET koi8r +--remove_file $file +select astext(g) from t2; +drop table t1, t2; + --echo # --echo # End of 5.1 tests --echo # diff --git a/mysql-test/t/not_embedded_server.test b/mysql-test/t/not_embedded_server.test index 5b99c37ee29..0878a78b885 100644 --- a/mysql-test/t/not_embedded_server.test +++ b/mysql-test/t/not_embedded_server.test @@ -18,10 +18,8 @@ SHOW VARIABLES like 'slave_skip_errors'; --echo # Bug#58026: massive recursion and crash in regular expression handling --echo # ---disable_result_log ---error ER_STACK_OVERRUN_NEED_MORE +--error ER_STACK_OVERRUN_NEED_MORE,ER_REGEXP_ERROR SELECT '1' RLIKE RPAD('1', 10000, '('); ---enable_result_log # End of 5.1 tests diff --git a/mysql-test/t/old-mode.test b/mysql-test/t/old-mode.test index 182c166ebbc..483549886db 100644 --- a/mysql-test/t/old-mode.test +++ b/mysql-test/t/old-mode.test @@ -25,3 +25,42 @@ drop table t1,t2; --replace_result "Writing to net" "NULL" --replace_regex /localhost[:0-9]*/localhost/ SHOW PROCESSLIST; + +--echo # +--echo # MDEV-5372 Make "CAST(time_expr AS DATETIME)" compatible with the SQL Standard) +--echo # +set @@old_mode=zero_date_time_cast; +SELECT CAST(TIME'-10:30:30' AS DATETIME); +SELECT CAST(TIME'10:20:30' AS DATETIME); +SELECT CAST(TIME'830:20:30' AS DATETIME); +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES (TIME'-10:20:30'); +INSERT INTO t1 VALUES (TIME'10:20:30'); +INSERT INTO t1 VALUES (TIME'830:20:30'); +SELECT * FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES (TIME'-10:20:30'); +INSERT INTO t1 VALUES (TIME'10:20:30'); +INSERT INTO t1 VALUES (TIME'830:20:30'); +SELECT * FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES (TIME'-10:20:30'); +INSERT INTO t1 VALUES (TIME'10:20:30'); +INSERT INTO t1 VALUES (TIME'830:20:30'); +SELECT a, CAST(a AS DATETIME), TO_DAYS(a) FROM t1; +DROP TABLE t1; +# Note, it was actually a bug that TO_DAYS('830:20:30') returned NULL +# for a column, while 3 for an expression. We won't fix this, +# it's "old_mode" anyway. +SELECT TO_DAYS(TIME'-10:20:30'); +SELECT TO_DAYS(TIME'10:20:30'); +SELECT TO_DAYS(TIME'830:20:30'); + +# This is to cover Item_temporal_hybrid_func::fix_temporal_type in old_mode: +CREATE TABLE t1 (a DATETIME, b TIME); +INSERT INTO t1 VALUES (NULL, '00:20:12'); +INSERT INTO t1 VALUES (NULL, '-00:20:12'); +SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index d573c3b5b8a..c4a85e4b111 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -261,6 +261,9 @@ desc,b desc; explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; explain select * from t1 where a = 1 order by b desc; +explain select * from t1 where a = 2 and b > 0 order by a desc,b desc,b,a; +explain select * from t1 where a = 2 and b < 2 order by a desc,a,b desc,a,b; + select * from t1 where a = 1 order by b desc; # # Test things when we don't have NULL keys @@ -1913,6 +1916,35 @@ SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 100; DROP TABLE t1,t2; +--echo # +--echo # MDEV-4974 memory leak in 5.5.32-MariaDB-1~wheezy-log +--echo # +set sort_buffer_size=default; +set max_sort_length=default; +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2 (b int, + col1 varchar(255), + col2 varchar(255) + ) character set utf8; + +insert into t2 select + A.a+10*B.a, + concat('wow-wow-col1-value-', A.a+10*B.a+100*C.a), + concat('wow-wow-col2-value-', A.a+10*B.a+100*C.a) +from + t1 A, t1 B, t1 C where C.a < 8; + +create table t3 as +select distinct A.col1 as XX, B.col1 as YY +from + t2 A, t2 B +where A.b = B.b +order by A.col2, B.col2 limit 10, 1000000; + +drop table t1,t2,t3; + --echo End of 5.5 tests diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 713b3ed7347..7eb541ab331 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -2530,6 +2530,338 @@ ALTER TABLE t1 ADD PARTITION PARTITIONS 2; SELECT * from t1 order by i; DROP TABLE t1; +--echo # +--echo # MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703) +--echo # +create table t1 ( + a int not null, + b int not null, + pk int not null, + primary key (pk), + key(a), + key(b) +) partition by hash(pk) partitions 10; + +insert into t1 values (1,2,4); # both +insert into t1 values (1,0,17); # left +insert into t1 values (1,2,25); # both + +insert into t1 values (10,20,122); +insert into t1 values (10,20,123); + +# Now, fill in some data so that the optimizer choses index_merge +create table t2 (a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C; + +insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a, + 10+A.a + 10*B.a + 100*C.a + 1000*D.a, + 2000 + A.a + 10*B.a + 100*C.a + 1000*D.a + from t2 A, t2 B, t2 C ,t2 D; + +# This should show index_merge, using intersect +explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ; +# 794 rows in output +create temporary table t3 as +select * from t1 where a=1 and b=2 and pk between 1 and 999 ; +select count(*) from t3; +drop table t3; + +# 802 rows in output +create temporary table t3 as +select * from t1 ignore index(a,b) where a=1 and b=2 and pk between 1 and 999 ; +select count(*) from t3; +drop table t3; + +drop table t1,t2; + +--echo # +--echo # MDEV-5555: Incorrect index_merge on BTREE indices +--echo # + +CREATE TABLE t1 ( + id bigint(20) unsigned NOT NULL, + id2 bigint(20) unsigned NOT NULL, + dob date DEFAULT NULL, + address char(100) DEFAULT NULL, + city char(35) DEFAULT NULL, + hours_worked_per_week smallint(5) unsigned DEFAULT NULL, + weeks_worked_last_year tinyint(3) unsigned DEFAULT NULL, + KEY dob (dob), + KEY address (address), + KEY city (city), + KEY hours_worked_per_week (hours_worked_per_week), + KEY weeks_worked_last_year (weeks_worked_last_year) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +PARTITION BY KEY (id) PARTITIONS 5; + +--echo # Insert some rows +--disable_query_log +INSERT INTO t1 VALUES (123,123,'2001-04-14','address123','city123',40,51), +(127,127,'1977-03-30','address127','city127',0,0), +(131,131,'1985-07-29','address131','city131',50,52), +(135,135,'1997-01-20','address135','city135',0,0), +(139,139,'1963-04-27','address139','city139',27,52), +(143,143,'1979-01-28','address143','city143',40,52), +(147,147,'1985-08-28','address147','city147',0,0), +(151,151,'1997-01-24','address151','city151',40,52), +(156,156,'1975-02-19','address156','city156',46,52), +(158,158,'1996-07-06','address158','city158',46,8), +(164,164,'1925-12-30','address164','city164',0,0), +(166,166,'2010-12-30','address166','city166',0,0), +(172,172,'1996-08-15','address172','city172',0,0), +(174,174,'2008-05-20','address174','city174',40,52), +(180,180,'1969-09-05','address180','city180',25,52), +(182,182,'1977-08-11','address182','city182',40,8), +(188,188,'2012-03-29','address188','city188',0,0), +(190,190,'1978-02-19','address190','city190',0,0), +(215,215,'1982-02-07','address215','city215',40,36), +(223,223,'2005-02-11','address223','city223',55,52), +(247,247,'2005-07-02','address247','city247',30,51), +(255,255,'1997-08-15','address255','city255',0,0), +(2,2,'1973-05-05','address2','city2',25,52), +(4,4,'2012-07-21','address4','city4',40,12), +(6,6,'1982-07-15','address6','city6',0,0), +(8,8,'1979-02-16','address8','city8',0,0), +(10,10,'1955-10-06','address10','city10',60,52), +(12,12,'1977-08-09','address12','city12',40,52), +(14,14,'2005-03-28','address14','city14',0,0), +(16,16,'1949-11-07','address16','city16',40,52), +(18,18,'2012-01-04','address18','city18',0,0), +(20,20,'2011-01-23','address20','city20',0,0), +(22,22,'1954-10-14','address22','city22',20,52), +(24,24,'2010-01-22','address24','city24',0,0), +(26,26,'1999-08-15','address26','city26',0,0), +(28,28,'1964-07-05','address28','city28',10,20), +(30,30,'2004-10-13','address30','city30',0,0), +(32,32,'2007-06-08','address32','city32',0,0), +(34,34,'1977-02-23','address34','city34',40,52), +(36,36,'2007-06-11','address36','city36',75,52), +(38,38,'1932-04-12','address38','city38',0,0), +(40,40,'1968-11-16','address40','city40',0,0), +(42,42,'1996-10-01','address42','city42',38,52), +(44,44,'1977-08-23','address44','city44',18,6), +(46,46,'1978-11-23','address46','city46',0,0), +(48,48,'1998-02-27','address48','city48',0,0), +(50,50,'1923-09-08','address50','city50',40,52), +(52,52,'1964-09-09','address52','city52',0,0), +(55,55,'2001-01-27','address55','city55',40,40), +(56,56,'1938-08-28','address56','city56',48,52), +(59,59,'1967-12-19','address59','city59',40,52), +(60,60,'1969-06-30','address60','city60',40,46), +(63,63,'2002-04-05','address63','city63',0,0), +(64,64,'1972-11-21','address64','city64',48,52), +(67,67,'1988-04-04','address67','city67',0,0), +(68,68,'1964-07-14','address68','city68',4,16), +(71,71,'1998-03-09','address71','city71',0,0), +(72,72,'1960-10-28','address72','city72',35,52), +(75,75,'1968-04-14','address75','city75',40,52), +(76,76,'1977-05-13','address76','city76',0,0), +(79,79,'1982-12-19','address79','city79',0,0), +(80,80,'1966-01-07','address80','city80',15,12), +(83,83,'1947-02-09','address83','city83',10,18), +(84,84,'1976-06-23','address84','city84',40,52), +(87,87,'2000-10-24','address87','city87',25,4), +(88,88,'2002-05-23','address88','city88',20,52), +(91,91,'2000-11-28','address91','city91',60,52), +(92,92,'1965-07-17','address92','city92',40,52), +(95,95,'1977-09-16','address95','city95',30,52), +(96,96,'1994-09-26','address96','city96',0,0), +(99,99,'2008-02-19','address99','city99',0,0), +(100,100,'1953-01-07','address100','city100',0,0), +(103,103,'2010-12-29','address103','city103',0,0), +(104,104,'1990-12-03','address104','city104',40,52), +(107,107,'2003-10-27','address107','city107',0,0), +(108,108,'1998-03-05','address108','city108',40,17), +(111,111,'2002-10-18','address111','city111',0,0), +(112,112,'1960-04-02','address112','city112',0,0), +(115,115,'1989-05-28','address115','city115',40,52), +(116,116,'1985-10-25','address116','city116',15,52), +(119,119,'1974-04-15','address119','city119',0,0), +(120,120,'1926-03-21','address120','city120',0,0), +(157,157,'1972-03-23','address157','city157',0,0), +(159,159,'2002-11-08','address159','city159',0,0), +(165,165,'1998-07-10','address165','city165',0,0), +(167,167,'1973-11-16','address167','city167',0,0), +(173,173,'1966-06-26','address173','city173',0,0), +(175,175,'1957-02-02','address175','city175',0,0), +(181,181,'1964-11-16','address181','city181',45,26), +(183,183,'1943-12-02','address183','city183',0,0), +(189,189,'1986-06-30','address189','city189',0,0), +(191,191,'2005-05-14','address191','city191',0,0), +(196,196,'1961-03-23','address196','city196',0,0), +(197,197,'1955-07-13','address197','city197',0,0), +(198,198,'2006-11-26','address198','city198',0,0), +(199,199,'1978-02-06','address199','city199',0,0), +(208,208,'2012-04-13','address208','city208',48,52), +(210,210,'1989-08-18','address210','city210',0,0), +(211,211,'1982-08-17','address211','city211',40,52), +(212,212,'1919-08-29','address212','city212',0,0), +(213,213,'1987-03-25','address213','city213',0,0), +(228,228,'1988-05-05','address228','city228',40,52), +(229,229,'1936-10-15','address229','city229',0,0), +(230,230,'1973-08-19','address230','city230',40,52), +(231,231,'2002-06-18','address231','city231',50,52), +(240,240,'2011-10-17','address240','city240',60,52), +(242,242,'1981-07-24','address242','city242',0,0), +(243,243,'1978-10-12','address243','city243',0,0), +(244,244,'2003-01-15','address244','city244',0,0), +(245,245,'1950-09-26','address245','city245',0,0), +(125,125,'1939-08-02','address125','city125',28,32), +(126,126,'1984-02-10','address126','city126',0,0), +(129,129,'1992-01-20','address129','city129',0,0), +(130,130,'1992-09-18','address130','city130',0,0), +(133,133,'1996-05-07','address133','city133',24,20), +(134,134,'1987-07-13','address134','city134',0,0), +(137,137,'2004-03-10','address137','city137',0,0), +(138,138,'1989-02-10','address138','city138',0,0), +(141,141,'1970-03-21','address141','city141',0,0), +(142,142,'1984-05-25','address142','city142',40,50), +(145,145,'1959-05-24','address145','city145',0,0), +(146,146,'1946-07-28','address146','city146',35,16), +(149,149,'1993-09-16','address149','city149',0,0), +(150,150,'1975-12-18','address150','city150',0,0), +(153,153,'1993-12-20','address153','city153',0,0), +(155,155,'1934-10-29','address155','city155',0,0), +(161,161,'1969-11-04','address161','city161',50,50), +(163,163,'1976-05-03','address163','city163',40,52), +(169,169,'1982-12-19','address169','city169',0,0), +(171,171,'1976-07-01','address171','city171',0,0), +(177,177,'2002-11-16','address177','city177',0,0), +(179,179,'1964-02-05','address179','city179',40,32), +(185,185,'1981-02-06','address185','city185',0,0), +(187,187,'1962-06-04','address187','city187',40,52), +(216,216,'1996-05-21','address216','city216',48,52), +(248,248,'1963-09-06','address248','city248',0,0), +(256,256,'1966-07-14','address256','city256',0,0), +(53,53,'1992-05-25','address53','city53',0,0), +(57,57,'2003-11-12','address57','city57',25,20), +(61,61,'1953-01-29','address61','city61',0,0), +(65,65,'1975-05-02','address65','city65',10,10), +(69,69,'1938-03-20','address69','city69',0,0), +(73,73,'1969-05-05','address73','city73',0,0), +(77,77,'1996-05-19','address77','city77',0,0), +(81,81,'1985-06-22','address81','city81',0,0), +(85,85,'2002-10-10','address85','city85',0,0), +(89,89,'1958-06-16','address89','city89',0,0), +(93,93,'1962-06-16','address93','city93',0,0), +(97,97,'1964-10-08','address97','city97',0,0), +(101,101,'1986-06-11','address101','city101',40,52), +(105,105,'1999-05-14','address105','city105',40,45), +(109,109,'2000-05-23','address109','city109',0,0), +(113,113,'1960-08-03','address113','city113',8,15), +(117,117,'1982-02-15','address117','city117',50,36), +(121,121,'1998-10-18','address121','city121',24,52), +(192,192,'1964-07-24','address192','city192',40,52), +(193,193,'1973-05-03','address193','city193',0,0), +(194,194,'1980-01-14','address194','city194',40,52), +(195,195,'1975-07-15','address195','city195',45,52), +(200,200,'2006-03-09','address200','city200',0,0), +(201,201,'2008-05-20','address201','city201',3,28), +(202,202,'2000-06-30','address202','city202',12,52), +(203,203,'1992-07-08','address203','city203',50,52), +(204,204,'1988-07-05','address204','city204',14,40), +(205,205,'1950-10-29','address205','city205',0,0), +(206,206,'1962-11-25','address206','city206',0,0), +(207,207,'1946-06-03','address207','city207',0,0), +(214,214,'1973-12-14','address214','city214',0,0), +(217,217,'1945-11-06','address217','city217',40,36), +(218,218,'2007-07-20','address218','city218',0,0), +(219,219,'1979-10-05','address219','city219',0,0), +(220,220,'1992-06-20','address220','city220',10,12), +(221,221,'2007-03-26','address221','city221',50,52), +(222,222,'1989-12-24','address222','city222',0,0), +(224,224,'1975-07-14','address224','city224',0,0), +(225,225,'1976-02-23','address225','city225',20,52), +(226,226,'1974-06-22','address226','city226',0,0), +(227,227,'2004-01-16','address227','city227',0,0), +(232,232,'1958-01-01','address232','city232',0,0), +(233,233,'1966-08-03','address233','city233',40,32), +(234,234,'1975-10-22','address234','city234',40,52), +(235,235,'1983-10-25','address235','city235',0,0), +(236,236,'1974-03-07','address236','city236',0,0), +(237,237,'1965-12-31','address237','city237',45,20), +(238,238,'1971-10-16','address238','city238',0,0), +(239,239,'1989-07-19','address239','city239',0,0), +(246,246,'1960-07-08','address246','city246',0,0), +(249,249,'1943-07-01','address249','city249',40,30), +(250,250,'1983-10-15','address250','city250',30,52), +(251,251,'1979-07-03','address251','city251',0,0), +(252,252,'1985-10-04','address252','city252',15,4), +(253,253,'1966-10-24','address253','city253',0,0), +(254,254,'1956-02-02','address254','city254',0,0), +(1,1,'2003-11-23','address1','city1',40,52), +(3,3,'1938-01-23','address3','city3',0,0), +(5,5,'2006-12-27','address5','city5',40,48), +(7,7,'1969-04-09','address7','city7',0,0), +(9,9,'2006-06-14','address9','city9',0,0), +(11,11,'1999-01-12','address11','city11',40,52), +(13,13,'1968-01-13','address13','city13',50,12), +(15,15,'1960-04-11','address15','city15',0,0), +(17,17,'2006-10-13','address17','city17',40,52), +(19,19,'1950-08-19','address19','city19',0,0), +(21,21,'2000-05-01','address21','city21',40,30), +(23,23,'1952-06-09','address23','city23',40,52), +(25,25,'1934-12-08','address25','city25',32,40), +(27,27,'1995-04-19','address27','city27',40,45), +(29,29,'1986-01-14','address29','city29',44,52), +(31,31,'1978-04-19','address31','city31',10,20), +(33,33,'1989-11-23','address33','city33',25,10), +(35,35,'2012-01-02','address35','city35',8,48), +(37,37,'2005-08-24','address37','city37',40,42), +(39,39,'1973-11-02','address39','city39',40,52), +(41,41,'2011-10-12','address41','city41',20,30), +(43,43,'1960-12-24','address43','city43',0,0), +(45,45,'1990-04-17','address45','city45',35,40), +(47,47,'1964-04-02','address47','city47',0,0), +(49,49,'1957-01-25','address49','city49',40,52), +(51,51,'1970-10-20','address51','city51',0,0), +(54,54,'1987-09-30','address54','city54',0,0), +(58,58,'1975-05-07','address58','city58',0,0), +(62,62,'1972-08-03','address62','city62',40,52), +(66,66,'1995-11-04','address66','city66',0,0), +(70,70,'1985-10-19','address70','city70',40,52), +(74,74,'1969-06-09','address74','city74',0,0), +(78,78,'2003-01-16','address78','city78',66,52), +(82,82,'2012-04-29','address82','city82',50,30), +(86,86,'2008-02-03','address86','city86',0,0), +(90,90,'1973-05-15','address90','city90',35,12), +(94,94,'1987-10-28','address94','city94',40,50), +(98,98,'1973-06-10','address98','city98',65,50), +(102,102,'2009-09-13','address102','city102',0,0), +(106,106,'1986-07-03','address106','city106',0,0), +(110,110,'1982-06-10','address110','city110',35,52), +(114,114,'1963-10-08','address114','city114',48,52), +(118,118,'1948-03-07','address118','city118',0,0), +(122,122,'1997-12-19','address122','city122',0,0), +(124,124,'1966-03-25','address124','city124',0,0), +(128,128,'1968-08-13','address128','city128',0,0), +(132,132,'1989-09-25','address132','city132',20,20), +(136,136,'1993-09-02','address136','city136',0,0), +(140,140,'1981-05-31','address140','city140',48,52), +(144,144,'1960-09-15','address144','city144',0,0), +(148,148,'1945-02-13','address148','city148',40,38), +(152,152,'2010-11-13','address152','city152',20,52), +(154,154,'1950-11-07','address154','city154',55,52), +(160,160,'1981-01-17','address160','city160',0,0), +(162,162,'2001-03-19','address162','city162',0,0), +(168,168,'2003-03-28','address168','city168',0,0), +(170,170,'1977-06-18','address170','city170',50,52), +(176,176,'1967-04-15','address176','city176',30,50), +(178,178,'1989-10-25','address178','city178',60,12), +(184,184,'2004-04-21','address184','city184',0,0), +(186,186,'1952-11-08','address186','city186',50,48), +(209,209,'1943-03-15','address209','city209',40,30), +(241,241,'1979-12-02','address241','city241',0,0), +(257,257,'2010-03-06','address257','city257',40,47); +--enable_query_log + +select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21'; +select * from t1 IGNORE INDEX(dob, weeks_worked_last_year, hours_worked_per_week) where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21'; + +drop table t1; + # # Test ALTER TABLE ADD/DROP PARTITION IF EXISTS # diff --git a/mysql-test/t/partition_debug_sync.test b/mysql-test/t/partition_debug_sync.test index 3ca21c2185a..90f1d4173ff 100644 --- a/mysql-test/t/partition_debug_sync.test +++ b/mysql-test/t/partition_debug_sync.test @@ -131,5 +131,6 @@ UNLOCK TABLES; --echo # Default connection default; +SET DEBUG_SYNC = 'RESET'; DROP TABLE t1, t2; diff --git a/mysql-test/t/partition_exchange.test b/mysql-test/t/partition_exchange.test index d7dfd6f543e..e538bee16cd 100644 --- a/mysql-test/t/partition_exchange.test +++ b/mysql-test/t/partition_exchange.test @@ -439,7 +439,7 @@ CREATE TABLE t LIKE general_log; ALTER TABLE t PARTITION BY RANGE (UNIX_TIMESTAMP(event_time) DIV 1) (PARTITION p0 VALUES LESS THAN (123456789), PARTITION pMAX VALUES LESS THAN MAXVALUE); ---error ER_WRONG_USAGE +--error ER_BAD_LOG_STATEMENT ALTER TABLE t EXCHANGE PARTITION p0 WITH TABLE general_log; ALTER TABLE general_log ENGINE = CSV; SET @@global.general_log = @old_general_log_state; diff --git a/mysql-test/t/perror.test b/mysql-test/t/perror.test index 2b9907c0542..69f1cb3257e 100644 --- a/mysql-test/t/perror.test +++ b/mysql-test/t/perror.test @@ -27,7 +27,7 @@ enable_query_log; --exec $MY_PERROR 1062 2>&1 # test errors that contain characters to escape in the text. ---exec $MY_PERROR 1076 2>&1 +--exec $MY_PERROR 1408 2>&1 --exec $MY_PERROR 1459 2>&1 --exec $MY_PERROR 1461 2>&1 diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index eda70dafc30..fb608ee5bf8 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -121,7 +121,7 @@ SET @OLD_SQL_MODE=@@SQL_MODE; SET SQL_MODE='IGNORE_BAD_TABLE_OPTIONS'; --echo #illegal value fixed -CREATE TABLE t1 (a int) ENGINE=example ULL=10000000000000000000 one_or_two='ttt' YESNO=SSS; +CREATE TABLE t1 (a int, b int) ENGINE=example ULL=10000000000000000000 one_or_two='ttt' YESNO=SSS; show create table t1; --echo #alter table diff --git a/mysql-test/t/plugin_vars.test b/mysql-test/t/plugin_vars.test new file mode 100644 index 00000000000..8ba8fe2ec0e --- /dev/null +++ b/mysql-test/t/plugin_vars.test @@ -0,0 +1,56 @@ +--echo # +--echo # MDEV-5345 - Deadlock between mysql_change_user(), SHOW VARIABLES and +--echo # INSTALL PLUGIN +--echo # + +# Prepare test +delimiter |; +CREATE PROCEDURE p_install(x INT) +BEGIN + DECLARE CONTINUE HANDLER FOR 1126 BEGIN END; + WHILE x DO + SET x= x - 1; + INSTALL PLUGIN no_such_plugin SONAME 'no_such_object'; + END WHILE; +END| + +CREATE PROCEDURE p_show_vars(x INT) +WHILE x DO + SET x= x - 1; + SHOW VARIABLES; +END WHILE| +delimiter ;| + +connect(con1, localhost, root,,); +connect(con2, localhost, root,,); + +# Start test +connection con1; +--send CALL p_install(100) + +connection con2; +--send CALL p_show_vars(100) + +connection default; + +disable_result_log; +let $i= 100; +while ($i) +{ + change_user; + dec $i; +} + +# Cleanup +connection con1; +reap; +connection con2; +reap; +connection default; +enable_result_log; + +disconnect con1; +disconnect con2; +USE test; +DROP PROCEDURE p_install; +DROP PROCEDURE p_show_vars; diff --git a/mysql-test/t/processlist.test b/mysql-test/t/processlist.test index c7b775cf992..7a2b33699d5 100644 --- a/mysql-test/t/processlist.test +++ b/mysql-test/t/processlist.test @@ -32,4 +32,4 @@ connection default; select command, time < 5 from information_schema.processlist where id != connection_id(); disconnect con1; - +set debug_sync='reset'; diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 5f4c815a192..9775a8dc28e 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3594,4 +3594,42 @@ EXECUTE stmt; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-5369: Wrong result (0 instead of NULL) on 2nd execution of +--echo # PS with LEFT JOIN, TEMPTABLE view +--echo # + + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0),(8); + +CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; + +SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk; + +PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +DROP VIEW v2; +DROP TABLE t1, t2; + +--echo # End of 5.3 tests + +--echo # +--echo # MDEV-5505: Assertion `! is_set()' fails on PREPARE SELECT +--echo # with out of range in GROUP BY +--echo # +CREATE TABLE t1 (a INT); + +--error ER_DATA_OUT_OF_RANGE +PREPARE stmt FROM "SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1"; +--error ER_DATA_OUT_OF_RANGE +SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1; + +drop table t1; + --echo # End of 5.3 tests diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test index c34800976c7..21355ca42b7 100644 --- a/mysql-test/t/ps_ddl.test +++ b/mysql-test/t/ps_ddl.test @@ -1610,7 +1610,7 @@ call p_verify_reprepare_count(0); # Base table with name of table to be created exists --error ER_TABLE_EXISTS_ERROR execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); --error ER_TABLE_EXISTS_ERROR execute stmt; call p_verify_reprepare_count(0); @@ -1622,7 +1622,7 @@ execute stmt; call p_verify_reprepare_count(0); --error ER_TABLE_EXISTS_ERROR execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); drop temporary table t2; --error ER_TABLE_EXISTS_ERROR execute stmt; @@ -1641,7 +1641,7 @@ drop table t2; create view t2 as select 1; --error ER_TABLE_EXISTS_ERROR,9999 execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); --error ER_TABLE_EXISTS_ERROR,9999 execute stmt; call p_verify_reprepare_count(0); diff --git a/mysql-test/t/quick_select_4161.test b/mysql-test/t/quick_select_4161.test index 1e746754b41..87323087622 100644 --- a/mysql-test/t/quick_select_4161.test +++ b/mysql-test/t/quick_select_4161.test @@ -50,4 +50,5 @@ connection default; disconnect killee; drop table t1; +set debug_sync='reset'; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index e346511db42..5d744dba77a 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1639,3 +1639,24 @@ SELECT * FROM v3; DROP TABLE t1; DROP VIEW v3; +--echo # +--echo # MDEV-5606: range optimizer: "x < y" is sargable, while "y > x" is not +--echo # +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +alter table t2 add key(a); +--echo # Should have "range checked for each table" for second table: +explain select * from t1, t2 where t2.a < t1.a; +--echo # Should have "range checked for each table" for second table: +explain select * from t1, t2 where t1.a > t2.a; + +create table t3 (a int primary key, b int); +insert into t3 select a,a from t1; +--echo # The second table should use 'range': +explain select * from t3, t2 where t2.a < t3.b and t3.a=1; +--echo # The second table should use 'range': +explain select * from t3, t2 where t3.b > t2.a and t3.a=1; +drop table t1,t2,t3; + diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test index fb8fd778559..7ecca454f4c 100644 --- a/mysql-test/t/range_vs_index_merge.test +++ b/mysql-test/t/range_vs_index_merge.test @@ -454,6 +454,8 @@ SELECT * FROM City WHERE Name LIKE 'Pa%'; # index merge retrievals over: # 2. key1 and key3 # 3. key2 and key3 +set @tmp_range_vs_index_merge=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; EXPLAIN SELECT * FROM City @@ -578,6 +580,7 @@ SELECT * FROM City DROP INDEX Population ON City; DROP INDEX Name ON City; +set optimizer_switch=@tmp_range_vs_index_merge; # The pattern of the WHERE condition used in the following query is # (key1|2_p1=c AND range(key1_p2)) OR (key1|2_p1=c AND range(key2_p2)) diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test index 6536c052019..5b78a352863 100644 --- a/mysql-test/t/repair.test +++ b/mysql-test/t/repair.test @@ -190,3 +190,23 @@ set @@autocommit= 0; repair table t1, t2; set @@autocommit= default; drop tables t1, t2; + +--echo # +--echo # Check that we have decent error messages when using crashed +--echo # .frm file from MySQL 3.23 +--echo # + +--echo # Test with a saved table from 3.23 +let $MYSQLD_DATADIR= `select @@datadir`; +--copy_file std_data/host_old.frm $MYSQLD_DATADIR/test/t1.frm +--copy_file std_data/host_old.MYD $MYSQLD_DATADIR/test/t1.MYD +--copy_file std_data/host_old.MYI $MYSQLD_DATADIR/test/t1.MYI + +--error ER_GET_ERRNO +select count(*) from t1; +check table t1; +repair table t1; +repair table t1 use_frm; +select count(*) from t1; +check table t1; +drop table t1; diff --git a/mysql-test/t/repair_symlink-5543.test b/mysql-test/t/repair_symlink-5543.test new file mode 100644 index 00000000000..bad65a4175a --- /dev/null +++ b/mysql-test/t/repair_symlink-5543.test @@ -0,0 +1,26 @@ +# +# MDEV-5543 MyISAM repair unsafe usage of TMD files +# +--source include/have_symlink.inc +--source include/not_windows.inc +--source include/have_maria.inc + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval create table t1 (a int) engine=myisam data directory='$MYSQL_TMP_DIR'; +insert t1 values (1); +--system ln -s $MYSQL_TMP_DIR/foobar5543 $MYSQL_TMP_DIR/t1.TMD +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +repair table t1; +drop table t1; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval create table t2 (a int) engine=aria data directory='$MYSQL_TMP_DIR'; +insert t2 values (1); +--system ln -s $MYSQL_TMP_DIR/foobar5543 $MYSQL_TMP_DIR/t2.TMD +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +repair table t2; +drop table t2; + +--list_files $MYSQL_TMP_DIR foobar5543 +--system rm $MYSQL_TMP_DIR/t1.TMD $MYSQL_TMP_DIR/t2.TMD + diff --git a/mysql-test/t/rpl_mysqldump_slave.test b/mysql-test/t/rpl_mysqldump_slave.test index 3f39fe19c0a..77fc4a050cc 100644 --- a/mysql-test/t/rpl_mysqldump_slave.test +++ b/mysql-test/t/rpl_mysqldump_slave.test @@ -27,4 +27,13 @@ connection slave; # Execute mysqldump with --dump-slave ,--apply-slave-statements and --include-master-host-port --exec $MYSQL_DUMP_SLAVE --compact --dump-slave --apply-slave-statements --include-master-host-port test +# +# MDEV-5624 mysqldump --dump-slave option does not restart the replication if the dump has failed +# +start slave; +--replace_regex /MASTER_LOG_POS=[0-9]+/MASTER_LOG_POS=BINLOG_START/ +--error 2 +--exec $MYSQL_DUMP_SLAVE --compact --dump-slave no_such_db +start slave; + --source include/rpl_end.inc diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test index e043ec4d143..d6bca6b19b1 100644 --- a/mysql-test/t/select_found.test +++ b/mysql-test/t/select_found.test @@ -195,3 +195,16 @@ SELECT FOUND_ROWS(); DROP TABLE t1; # End of 4.1 tests + +# +# MDEV-5549 Wrong row counter in found_rows() result +# +create table t1 (f1 int primary key, f2 tinyint) engine=myisam; +insert t1 values (10,3),(11,2),(12,3); +create table t2 (f3 int primary key) engine=myisam; +insert t2 values (11),(12),(13); +#explain select f1 from t1,t2 where f1=f3 and f2=3 order by f1; +select f1 from t1,t2 where f1=f3 and f2=3 order by f1; +select found_rows(); +drop table t1, t2; + diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 6b38e8594ee..b67a15667e8 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -749,6 +749,26 @@ SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE SQL_MODE != ''; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; --echo # +--echo # Bug mdev-5630: always true conjunctive condition +--echo # when optimizer_use_condition_selectivity=3 +--echo # + +set use_stat_tables = 'preferably'; +set optimizer_use_condition_selectivity = 3; + +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (10); + +CREATE TABLE t2 (id int, flag char(1), INDEX(id)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (100,'0'),(101,'1'); + +ANALYZE TABLE t1, t2; + +SELECT * FROM t1, t2 WHERE id = a AND ( a = 16 OR flag AND a != 6 ); + +DROP TABLE t1,t2; + +--echo # --echo # Bug mdev-4429: join with range condition whose selectivity == 0 --echo # when optimizer_use_condition_selectivity=3 --echo # diff --git a/mysql-test/t/sp-bugs.test b/mysql-test/t/sp-bugs.test index 1ec154f1c69..8e6a25709aa 100644 --- a/mysql-test/t/sp-bugs.test +++ b/mysql-test/t/sp-bugs.test @@ -238,3 +238,59 @@ DROP FUNCTION testf_bug11763507; --echo #END OF BUG#11763507 test. +--echo # +--echo # MDEV-5531 double call procedure in one session +--echo # + +CREATE TABLE `t1` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `create_ts` int(10) unsigned DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; + +DELIMITER $$; + +CREATE PROCEDURE test_5531 (IN step TINYINT(1)) +BEGIN + DECLARE counts INT DEFAULT 0; + DECLARE cur1 CURSOR FOR + + SELECT ct.id + FROM (SELECT NULL) AS z + JOIN ( + SELECT id + FROM `t1` + LIMIT 10 + ) AS ct + JOIN (SELECT NULL) AS x ON( + EXISTS( + SELECT 1 + FROM `t1` + WHERE id=ct.id + LIMIT 1 + ) + ); + + IF step=1 THEN + TRUNCATE t1; + REPEAT + INSERT INTO `t1` + (create_ts) VALUES + (UNIX_TIMESTAMP()); + + SET counts=counts+1; + UNTIL counts>150 END REPEAT; + + SET max_sp_recursion_depth=1; + + CALL test_5531(2); + SET max_sp_recursion_depth=2; + CALL test_5531(2); + ELSEIF step=2 THEN + OPEN cur1; CLOSE cur1; + END IF; +END $$ +DELIMITER ;$$ +CALL test_5531(1); +DROP PROCEDURE test_5531; +DROP TABLE t1; diff --git a/mysql-test/t/stat_tables_par.test b/mysql-test/t/stat_tables_par.test index 6c4e1be6e48..7305d1453a8 100644 --- a/mysql-test/t/stat_tables_par.test +++ b/mysql-test/t/stat_tables_par.test @@ -144,7 +144,7 @@ disconnect con1; disconnect con2; set debug_sync='RESET'; -select * from mysql.index_stats where table_name='lineitem' order by index_name; +select * from mysql.index_stats where table_name='lineitem' order by index_name, prefix_arity; # # Test for parallel statistics collection and update (innodb) @@ -217,7 +217,7 @@ connection con2; set debug_sync='open_and_process_table WAIT_FOR parker'; set debug_sync='statistics_read_start SIGNAL go1 WAIT_FOR go2'; use dbt3_s001; ---send select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68 +--send select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68 order by prefix_arity; connection con1; --disable_result_log diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index a114af6a66b..d56b3df3505 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1850,6 +1850,41 @@ SELECT * FROM t1 AS t WHERE a='r' AND (c,b) NOT IN (SELECT c,b FROM t2 WHERE (c,b)=(t.c,t.b)); DROP TABLE t1, t2; + +--echo # +--echo # MDEV-5468: assertion failure with a simplified condition in subselect +--echo # + +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1); + +CREATE TABLE t2 ( pk int PRIMARY KEY, c INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,4), (2,6); + +SELECT ( SELECT MAX(b) FROM t1, t2 WHERE pk = a AND b < from_sq.c ) AS select_sq, + COUNT( DISTINCT from_sq.c ) +FROM ( SELECT DISTINCT t2_1.* FROM t2 AS t2_1, t2 AS t2_2 ) AS from_sq +GROUP BY select_sq ; + +DROP TABLE t1,t2; + + +CREATE TABLE t1 (id int, a2 char(2), a3 char(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'BE','BEL'); + +CREATE TABLE t2 (id int, a2 char(2), a3 char(3)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'BE','BEL'), (2,'MX','MEX'); +CREATE VIEW v2 AS SELECT DISTINCT * FROM t2; + +SELECT * FROM t1 AS outer_t1, v2 +WHERE v2.a3 = outer_t1.a3 + AND EXISTS ( SELECT * FROM t1 WHERE a2 < v2.a2 AND id = outer_t1.id ) + AND outer_t1.a3 < 'J' +ORDER BY v2.id; + +DROP VIEW v2; +DROP TABLE t1,t2; + --echo # --echo # MDEV-3899 Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT --echo # diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 536606175bb..1b0076fd1e6 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2489,6 +2489,24 @@ INSERT INTO t1 VALUES ('mysql'),('information_schema'); SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA); DROP TABLE t1; +--echo # +--echo # MDEV-5581: Server crashes in in JOIN::prepare on 2nd execution of PS with materialization+semijoin +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (2),(3); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (8),(9); + +CREATE TABLE t3 (c INT, INDEX(c)); +INSERT INTO t2 VALUES (5),(6); + +PREPARE stmt FROM +"SELECT * FROM t1 WHERE ( 9, 5 ) IN ( SELECT b, COUNT(*) FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) )"; +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1,t2,t3; --echo # --echo # MySQL Bug#13340270: assertion table->sort.record_pointers == __null @@ -2634,6 +2652,7 @@ INSERT INTO t2 VALUES ('x'); CREATE TABLE t3 (c3 VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t3 VALUES ('x'),('d'); +--sorted_result SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1; --echo # This should show that "t1 left join t3" is still in the semi-join nest: diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index 18221c90bc0..9d9e7848415 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -1245,6 +1245,24 @@ SELECT * FROM t1 WHERE ( 1, 1 ) IN ( DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin +--echo # +CREATE TABLE t1 (a INT) engine=innodb; +INSERT INTO t1 VALUES (8),(9); + +CREATE TABLE t2 (b INT) engine=innodb; +INSERT INTO t2 VALUES (2),(3); + +CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb; +INSERT INTO t2 VALUES (4),(5); + +explain +SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); +SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); + +DROP TABLE t1,t2,t3; + --source include/have_innodb.inc --disable_warnings diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 58831c4ffb3..52a73d24822 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1725,6 +1725,26 @@ INSERT INTO t1 VALUES (1,3,5),(2,4,6); SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b); DROP TABLE t1; +--echo # +--echo # MDEV-5368: Server crashes in Item_in_subselect::optimize on 2nd +--echo # execution of PS with IN subqueries, materialization+semijoin +--echo # +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(3); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +INSERT INTO t2 VALUES (8),(9); + +PREPARE stmt FROM " +SELECT * FROM t1 WHERE 1 IN ( SELECT b FROM v2 WHERE 2 IN ( SELECT MAX(a) FROM t1 ) ) +"; + +EXECUTE stmt; +EXECUTE stmt; +DROP TABLE t1, t2; +DROP VIEW v2; + --echo # End of 5.3 tests @@ -1750,5 +1770,25 @@ WHERE ( alias2.c2, alias1.c1 ) IN ( SELECT c4, c3 FROM t2 ) AND alias1.c1 IN ( S DROP TABLE t1,t2; set join_cache_level=@tmp_mdev5056; +--echo # +--echo # MDEV-5368: Server crashes in Item_in_subselect::optimize on 2nd +--echo # execution of PS with IN subqueries, materialization+semijoin +--echo # +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(3); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +INSERT INTO t2 VALUES (8),(9); + +PREPARE stmt FROM " +SELECT * FROM t1 WHERE 1 IN ( SELECT b FROM v2 WHERE 2 IN ( SELECT MAX(a) FROM t1 ) ) +"; + +EXECUTE stmt; +EXECUTE stmt; +DROP TABLE t1, t2; +DROP VIEW v2; + --echo # End of 5.5 tests diff --git a/mysql-test/t/temporal_literal.test b/mysql-test/t/temporal_literal.test index a8380fd95da..4bb7eb8d609 100644 --- a/mysql-test/t/temporal_literal.test +++ b/mysql-test/t/temporal_literal.test @@ -195,6 +195,7 @@ DROP TABLE t1; --echo # --echo # TIME literals in no-zero date context --echo # +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); SELECT TO_DAYS(TIME'00:00:00'); SELECT TO_SECONDS(TIME'00:00:00'); SELECT DAYOFYEAR(TIME'00:00:00'); @@ -204,6 +205,7 @@ SELECT WEEKDAY(TIME'00:00:00'); SELECT CONVERT_TZ(TIME'00:00:00','+00:00','+01:00'); SELECT DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR); SELECT TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00'); +SET timestamp=DEFAULT; --echo # --echo # Testing Item_func::fix_fields() diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test index 7764b39bf33..7a38610ad95 100644 --- a/mysql-test/t/timezone2.test +++ b/mysql-test/t/timezone2.test @@ -294,8 +294,16 @@ DROP TABLE t1; --echo # MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5') --echo # +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5'); SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5'); +SET timestamp=DEFAULT; + +--echo # +--echo # MDEV-5506 safe_mutex: Trying to lock unitialized mutex at safemalloc.c on server shutdown after SELECT with CONVERT_TZ +--echo # +SELECT CONVERT_TZ('2001-10-08 00:00:00', MAKE_SET(0,'+01:00'), '+00:00' ); + --echo # --echo # End of 5.3 tests diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index 9ecd5d22a81..1daeec03a08 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -94,10 +94,12 @@ DROP TABLE t1; --echo # Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values --echo # +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); CREATE TABLE t1(f1 TIME); INSERT INTO t1 VALUES ('23:38:57'); SELECT TIMESTAMP(f1,'1') FROM t1; DROP TABLE t1; +SET timestamp=DEFAULT; --echo End of 5.1 tests @@ -126,7 +128,9 @@ drop table t1; --echo # --echo # MDEV-4634 Crash in CONVERT_TZ --echo # +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); SELECT CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5'); +SET timestamp=DEFAULT; --echo # --echo # MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))) diff --git a/mysql-test/t/udf_debug_sync.test b/mysql-test/t/udf_debug_sync.test new file mode 100644 index 00000000000..593500c1e18 --- /dev/null +++ b/mysql-test/t/udf_debug_sync.test @@ -0,0 +1,40 @@ +--source include/have_debug_sync.inc +--source include/have_udf.inc + +# +# MDEV-5616 - Deadlock between CREATE/DROP FUNCTION and SELECT from view +# +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO"; +CREATE VIEW v1 AS SELECT myfunc_int(1); +connect(con1, localhost, root,,); + +connection con1; +SET debug_sync='mysql_create_function_after_lock SIGNAL locked WAIT_FOR go'; +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +send_eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_SO"; + +connection default; +SET debug_sync='now WAIT_FOR locked'; +SET debug_sync='find_udf_before_lock SIGNAL go'; +SELECT * FROM v1; +FLUSH TABLES; + +connection con1; +reap; +SET debug_sync='mysql_drop_function_after_lock SIGNAL locked WAIT_FOR go'; +send DROP FUNCTION myfunc_double; + +connection default; +SET debug_sync='now WAIT_FOR locked'; +SET debug_sync='find_udf_before_lock SIGNAL go'; +SELECT * FROM v1; + +connection con1; +reap; +disconnect con1; + +connection default; +SET debug_sync='RESET'; +DROP VIEW v1; +DROP FUNCTION myfunc_int; diff --git a/mysql-test/t/union_crash-714.test b/mysql-test/t/union_crash-714.test new file mode 100644 index 00000000000..6c31a2202cb --- /dev/null +++ b/mysql-test/t/union_crash-714.test @@ -0,0 +1,9 @@ +# +# MDEV-714 LP:1020645 - crash (sig 11) with union query +# +--source include/have_debug.inc +create table t1 (i tinyint); +set debug_dbug='+d,bug11747970_raise_error'; +--error ER_QUERY_INTERRUPTED +insert into t1 (i) select i from t1 union select i from t1; +drop table t1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 5cb9d920c0c..633624bf4bf 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -141,7 +141,7 @@ drop view v100; drop view t1; # try to drop VIEW with DROP TABLE --- error ER_BAD_TABLE_ERROR +-- error ER_IT_IS_A_VIEW drop table v1; # try to drop table with DROP VIEW @@ -4873,6 +4873,51 @@ deallocate prepare stmt; drop view v1; drop table t1,t2; +# +# MDEV-5414: RAND() in a subselect : different behavior in MariaDB and MySQL +# +create table t1 (a int); +insert into t1 values (1),(2); + +create view v1 (a,r) as select a,rand() from t1; + + +create table t2 select a, r as r1, r as r2, r as r3 from v1; + +select a, r1 = r2, r2 = r3 from t2; + +drop view v1; +drop table t1,t2; + +--echo # +--echo # MDEV-5515: 2nd execution of a prepared statement returns wrong results +--echo # +CREATE TABLE t1 (i1 INT, j1 INT NOT NULL, PRIMARY KEY (i1)); + +INSERT INTO t1 VALUES (30,300),(40,400); + +CREATE TABLE t2 (i2 INT); +INSERT INTO t2 VALUES (50),(60); + +CREATE TABLE t3 (c3 VARCHAR(20), i3 INT); +INSERT INTO t3 VALUES ('a',10),('b',2); + +CREATE TABLE t4 (i4 INT); +INSERT INTO t4 VALUES (1),(2); + +DROP VIEW IF EXISTS v1; +CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 ); + +CREATE VIEW v2 AS select v1_field1 from t4 join v1; + +prepare my_stmt from "select v1_field1 from v2"; +execute my_stmt; +execute my_stmt; +deallocate prepare my_stmt; + +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3,t4; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.3 tests. --echo # ----------------------------------------------------------------- diff --git a/mysql-test/t/xml.test b/mysql-test/t/xml.test index cdc6bd6f91e..e84e3ac247f 100644 --- a/mysql-test/t/xml.test +++ b/mysql-test/t/xml.test @@ -673,6 +673,11 @@ SELECT UPDATEXML('<a><c><a>x</a></c></a>','(a)/a','<b />'); SELECT UPDATEXML('<a><c><a>x</a></c></a>','(a)//a','<b />'); SELECT ExtractValue('<a><a>aa</a><b>bb</b></a>','(a)/a|(a)/b'); +# +# MDEV-5689 ExtractValue(xml, 'substring(/x,/y)') crashes +# MySQL bug#12428404 MYSQLD.EXE CRASHES WHEN EXTRACTVALUE() IS CALLED WITH MALFORMED XPATH EXP +# +SELECT ExtractValue('<a><b>abc</b><c>2</c><d>1</d></a>','substring(/a/b,..)'); --echo # --echo # Bug#62429 XML: ExtractValue, UpdateXML max arg length 127 chars |