diff options
Diffstat (limited to 'mysql-test/t')
35 files changed, 761 insertions, 43 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index bf16757efd6..143ba80c53b 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1755,6 +1755,17 @@ DROP TABLE t2; DROP TABLE t1; --echo # +--echo # MDEV-6390 CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET. +--echo # + +CREATE TABLE t1 (id int(11) NOT NULL, a int(11) NOT NULL, b int(11)) + ENGINE=InnoDB DEFAULT CHARSET=latin1; +SHOW CREATE TABLE t1; +ALTER TABLE t1 CONVERT TO CHARACTER SET utf8; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # --echo # Start of 10.1 tests --echo # diff --git a/mysql-test/t/check_constraint_show.test b/mysql-test/t/check_constraint_show.test new file mode 100644 index 00000000000..4d57f247b5b --- /dev/null +++ b/mysql-test/t/check_constraint_show.test @@ -0,0 +1,8 @@ +# Table with 2 column-level check constraints and 1 table-level check constraint +create or replace table t1( c1 int check( c1 > 0 ), c2 int check( c2 > 0 ), c3 int, constraint `range` check( ( c3 >= c1 ) and ( c3 <= c2 ) ), primary key( c1 ) ); +show create table t1; + +# Show all constraints, including check constraints +select * from information_schema.table_constraints where table_name = 't1'; + +drop table t1; diff --git a/mysql-test/t/ctype_upgrade.test b/mysql-test/t/ctype_upgrade.test index cc59d1e4401..62567c5afe4 100644 --- a/mysql-test/t/ctype_upgrade.test +++ b/mysql-test/t/ctype_upgrade.test @@ -191,6 +191,7 @@ SELECT GROUP_CONCAT(a ORDER BY BINARY a) FROM maria100004_xxx_croatian_ci GROUP SHOW CREATE TABLE mysql050614_xxx_croatian_ci; SELECT GROUP_CONCAT(a ORDER BY BINARY a) FROM mysql050614_xxx_croatian_ci GROUP BY a; +remove_file $MYSQLD_DATADIR/mysql_upgrade_info; DROP TABLE maria050313_ucs2_croatian_ci_def; DROP TABLE maria050313_utf8_croatian_ci; DROP TABLE maria050533_xxx_croatian_ci; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 121e274ceeb..d30a6d7fd9e 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -875,6 +875,29 @@ SELECT Customer, Success, SUM(OrderSize) DROP TABLE example1463; set sql_mode= @save_sql_mode; +--echo # +--echo # MDEV-9028: SELECT DISTINCT constant column of derived table +--echo # used as the second operand of LEFT JOIN +--echo # + +create table t1 (id int, data varchar(255)); +insert into t1 values (1,'yes'),(2,'yes'); + +select distinct t1.id, tt.id, tt.data + from t1 + left join + (select t1.id, 'yes' as data from t1) as tt + on t1.id = tt.id; + +select distinct t1.id, tt.id, tt.data + from t1 + left join + (select t1.id, 'yes' as data from t1 where id > 1) as tt + on t1.id = tt.id; + +drop table t1; + + --echo # end of 5.5 --echo # diff --git a/mysql-test/t/drop_bad_db_type.test b/mysql-test/t/drop_bad_db_type.test index 69e1a889b18..00b79dabddf 100644 --- a/mysql-test/t/drop_bad_db_type.test +++ b/mysql-test/t/drop_bad_db_type.test @@ -15,6 +15,12 @@ insert t1 values (1),(2),(3); flush tables; uninstall soname 'ha_archive'; +--vertical_results +select table_schema, table_name from information_schema.tables where table_name like 't1'; +select table_schema, table_name, engine, version from information_schema.tables where table_name like 't1'; +select table_schema, table_name, engine, row_format from information_schema.tables where table_name like 't1'; +--horizontal_results + install soname 'ha_archive'; --list_files $mysqld_datadir/test drop table t1; diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test index 7c343ed6566..beaaf603754 100644 --- a/mysql-test/t/error_simulation.test +++ b/mysql-test/t/error_simulation.test @@ -148,3 +148,17 @@ SELECT a FROM t1 ORDER BY rand(1); --echo #cleanup DROP TABLE t1, pid_table; + + + +--echo # +--echo # MDEV-12416 OOM in create_virtual_tmp_table() makes the server crash +--echo # + +CREATE FUNCTION f1(a INT) RETURNS INT RETURN a; +SET SESSION debug_dbug="+d,simulate_create_virtual_tmp_table_out_of_memory"; +# May fail with either ER_OUT_OF_RESOURCES or EE_OUTOFMEMORY +--error ER_OUT_OF_RESOURCES, 5 +SELECT f1(1); +DROP FUNCTION f1; +SET SESSION debug_dbug=DEFAULT; diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 1d86a64fc59..f18e50561b2 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1462,12 +1462,12 @@ SHOW CREATE TABLE information_schema.spatial_ref_sys; create table t1(g GEOMETRY, pt POINT); create table t2(g LINESTRING, pl POLYGON); -select * from information_schema.geometry_columns; +select * from information_schema.geometry_columns where f_table_schema='test'; drop table t1, t2; --echo 10.1 tests create table t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101, pt POINT(8,2), pg GEOMETRY REF_SYSTEM_ID=102); -SELECT SRID from information_schema.geometry_columns WHERE G_TABLE_NAME='t1'; +SELECT SRID from information_schema.geometry_columns WHERE f_table_schema='test' and G_TABLE_NAME='t1'; drop table t1; -- echo # Expect an int(1) column to be created diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index b816afed851..c945e818181 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -2116,6 +2116,68 @@ DROP USER mysqltest_u1@localhost; --echo # End of Bug#38347. --echo + +--echo # +--echo # BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES +--echo # DIFFERENTLY'. +--echo # +--disable_warnings +drop database if exists mysqltest_db1; +--enable_warnings +create database mysqltest_db1; +create user mysqltest_u1; +--echo # Both GRANT statements below should fail with the same error. +--error ER_SP_DOES_NOT_EXIST +grant execute on function mysqltest_db1.f1 to mysqltest_u1; +--error ER_SP_DOES_NOT_EXIST +grant execute on procedure mysqltest_db1.p1 to mysqltest_u1; +--echo # Let us show that GRANT behaviour for routines is consistent +--echo # with GRANT behaviour for tables. Attempt to grant privilege +--echo # on non-existent table also results in an error. +--error ER_NO_SUCH_TABLE +grant select on mysqltest_db1.t1 to mysqltest_u1; +show grants for mysqltest_u1; +drop database mysqltest_db1; +drop user mysqltest_u1; + + +--echo # +--echo # Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY - +--echo # REQUIRES FLUSH PRIVILEGES +--echo # + +CREATE USER foo@'127.0.0.1'; +GRANT ALL ON *.* TO foo@'127.0.0.1'; + +--echo # First attempt, should connect successfully +connect (conn1, '127.0.0.1', foo,,test); +SELECT user(), current_user(); + +--echo # Rename the user +RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0'; + +--echo # Second attempt, should connect successfully as its valid mask +--echo # This was failing without fix +connect (conn2, '127.0.0.1', foo,,test); +SELECT user(), current_user(); + +--echo # Rename the user back to original +RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1'; + +--echo # Third attempt, should connect successfully +connect (conn3, '127.0.0.1', foo,,test); +SELECT user(), current_user(); + +--echo # Clean-up +connection default; +disconnect conn1; +disconnect conn2; +disconnect conn3; +DROP USER foo@'127.0.0.1'; + +--echo # End of Bug#12766319 + + --echo # --echo # Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS --echo # DATABASE SECURITY @@ -2146,29 +2208,6 @@ disconnect con1; DROP USER untrusted@localhost; DROP DATABASE secret; ---echo # ---echo # BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES ---echo # DIFFERENTLY'. ---echo # ---disable_warnings -drop database if exists mysqltest_db1; ---enable_warnings -create database mysqltest_db1; -create user mysqltest_u1; ---echo # Both GRANT statements below should fail with the same error. ---error ER_SP_DOES_NOT_EXIST -grant execute on function mysqltest_db1.f1 to mysqltest_u1; ---error ER_SP_DOES_NOT_EXIST -grant execute on procedure mysqltest_db1.p1 to mysqltest_u1; ---echo # Let us show that GRANT behaviour for routines is consistent ---echo # with GRANT behaviour for tables. Attempt to grant privilege ---echo # on non-existent table also results in an error. ---error ER_NO_SUCH_TABLE -grant select on mysqltest_db1.t1 to mysqltest_u1; -show grants for mysqltest_u1; -drop database mysqltest_db1; -drop user mysqltest_u1; - set GLOBAL sql_mode=default; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 7b7d9236835..e60b7827f75 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -1309,5 +1309,74 @@ LEFT JOIN t4 AS alias5 JOIN t5 ON alias5.f5 ON alias2.f3 ON alias1.f2; DROP TABLE t1,t2,t3,t4,t5; -set optimizer_search_depth= @tmp_mdev621; +--echo # +--echo # MDEV-7992: Nested left joins + 'not exists' optimization +--echo # + +CREATE TABLE t1( + K1 INT PRIMARY KEY, + Name VARCHAR(15) +); + +INSERT INTO t1 VALUES + (1,'T1Row1'), (2,'T1Row2'); + + +CREATE TABLE t2( + K2 INT PRIMARY KEY, + K1r INT, + rowTimestamp DATETIME, + Event VARCHAR(15) +); + +INSERT INTO t2 VALUES + (1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'), + (2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'), + (3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3'); + +let $q1= +SELECT t1a.*, t2a.*, + t2i.K2 AS K2B, t2i.K1r AS K1rB, + t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB +FROM + t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 + LEFT JOIN + ( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) + ON (t1i.K1 = 1) AND + (((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR + (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) + OR (t2i.K2 IS NULL)) +WHERE +t2a.K1r = 1 AND t2i.K2 IS NULL; + +eval $q1; +eval EXPLAIN EXTENDED $q1; + +CREATE VIEW v1 AS + SELECT t2i.* + FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1 + WHERE t1i.K1 = 1 ; + +let $q2= +SELECT + t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB, + t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB +FROM + t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1 + LEFT JOIN + v1 as t2b + ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR + (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)) + OR (t2b.K2 IS NULL) +WHERE + t1a.K1 = 1 AND + t2b.K2 IS NULL; + +eval $q2; +eval EXPLAIN EXTENDED $q2; + +DROP VIEW v1; +DROP TABLE t1,t2; + +set optimizer_search_depth= @tmp_mdev621; diff --git a/mysql-test/t/log_tables_upgrade.test b/mysql-test/t/log_tables_upgrade.test index d08d74174db..36802fb38ad 100644 --- a/mysql-test/t/log_tables_upgrade.test +++ b/mysql-test/t/log_tables_upgrade.test @@ -24,4 +24,5 @@ RENAME TABLE test.bug49823 TO general_log; DROP TABLE general_log; RENAME TABLE renamed_general_log TO general_log; SET GLOBAL general_log = @saved_general_log; +remove_file $MYSQLD_DATADIR/mysql_upgrade_info; USE test; diff --git a/mysql-test/t/long_tmpdir-master.sh b/mysql-test/t/long_tmpdir-master.sh index 7bcbee26105..318955fbcca 100755..100644 --- a/mysql-test/t/long_tmpdir-master.sh +++ b/mysql-test/t/long_tmpdir-master.sh @@ -1,4 +1,3 @@ -#!/bin/sh d="$MYSQLTEST_VARDIR/tmp/long_temporary_directory_path_123456789_123456789_123456789_123456789_123456789_123456789_123456789_123456789" test -d "$d" || mkdir "$d" rm -f "$d"/* diff --git a/mysql-test/t/lowercase_mixed_tmpdir-master.sh b/mysql-test/t/lowercase_mixed_tmpdir-master.sh index 9330d0581ee..95c26e3aa02 100755..100644 --- a/mysql-test/t/lowercase_mixed_tmpdir-master.sh +++ b/mysql-test/t/lowercase_mixed_tmpdir-master.sh @@ -1,4 +1,3 @@ -#!/bin/sh # This test requires a non-lowercase tmpdir directory on a case-sensitive # filesystem. diff --git a/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh index 95c26e3aa02..95c26e3aa02 100755..100644 --- a/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh +++ b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh diff --git a/mysql-test/t/mysql_upgrade_noengine.test b/mysql-test/t/mysql_upgrade_noengine.test new file mode 100644 index 00000000000..cfc3a1dc406 --- /dev/null +++ b/mysql-test/t/mysql_upgrade_noengine.test @@ -0,0 +1,57 @@ +# +# MDEV-11942 BLACKHOLE is no longer active in 10.1 by default, mysql_upgrade not handling the situation +# +source include/have_innodb.inc; +source include/not_embedded.inc; + +if (!$HA_BLACKHOLE_SO) { + skip Need blackhole plugin; +} +if (!$HA_ARCHIVE_SO) { + skip Need Archive plugin; +} + +let $datadir= `select @@datadir`; + +install soname 'ha_blackhole'; +install soname 'ha_archive'; + +vertical_results; +create table t1 (a int) engine=blackhole; +create table t2 (a int) engine=archive; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; + +flush tables; +uninstall plugin blackhole; +uninstall plugin archive; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; + +# upgrade from 10.1 - engines aren't enabled +exec $MYSQL_UPGRADE 2>&1; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; + +# pretend it's an upgrade from 10.0 +alter table mysql.user drop column default_role, drop column max_statement_time; + +# but mysql_upgrade_info tells otherwise +remove_file $datadir/mysql_upgrade_info; +write_file $datadir/mysql_upgrade_info; +10.1.10-MariaDB +EOF + +# still upgrade from 10.1 +exec $MYSQL_UPGRADE 2>&1; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; + +alter table mysql.user drop column default_role, drop column max_statement_time; +remove_file $datadir/mysql_upgrade_info; + +# upgrade from 10.0 - engines are enabled +exec $MYSQL_UPGRADE 2>&1; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; + +drop table t1, t2; + +remove_file $datadir/mysql_upgrade_info; +uninstall plugin blackhole; +uninstall plugin archive; diff --git a/mysql-test/t/mysql_upgrade_ssl.test b/mysql-test/t/mysql_upgrade_ssl.test index 9049bf73821..daf5fad3df7 100644 --- a/mysql-test/t/mysql_upgrade_ssl.test +++ b/mysql-test/t/mysql_upgrade_ssl.test @@ -8,4 +8,5 @@ --echo # Bug#55672 mysql_upgrade dies with internal error --echo # --exec $MYSQL_UPGRADE --skip-verbose --skip-silent --ssl --force 2>&1 ---exit +--let $datadir= `select @@datadir` +--remove_file $datadir/mysql_upgrade_info diff --git a/mysql-test/t/mysql_upgrade_view.test b/mysql-test/t/mysql_upgrade_view.test index 7afe1ad250d..6a496858d22 100644 --- a/mysql-test/t/mysql_upgrade_view.test +++ b/mysql-test/t/mysql_upgrade_view.test @@ -183,5 +183,6 @@ flush tables; # back to mariadb default drop table mysql.event; rename table mysql.ev_bk to mysql.event; +remove_file $MYSQLD_DATADIR/mysql_upgrade_info; drop view v1,v2,v3; drop table t1; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 090ed45c9f9..ea08f540cf3 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2518,6 +2518,7 @@ if (`select convert(@@version_compile_os using latin1) IN ("Win32","Win64","Wind } --exec $MYSQL_DUMP --routines --compact $shell_ready_db_name DROP DATABASE `a\"'``b`; + #" use test; @@ -2573,3 +2574,11 @@ CREATE VIEW nonunique_table_view_name AS SELECT 1; DROP DATABASE db1; DROP DATABASE db2; + +#" +# MDEV-11505 wrong databasename in mysqldump comment +# +let SEARCH_FILE=$MYSQLTEST_VARDIR/tmp/bug11505.sql; +let SEARCH_PATTERN=Database: mysql; +exec $MYSQL_DUMP mysql func > $SEARCH_FILE; +source include/search_pattern_in_file.inc; diff --git a/mysql-test/t/partition_column.test b/mysql-test/t/partition_column.test index 95a2be36395..be34e4eac63 100644 --- a/mysql-test/t/partition_column.test +++ b/mysql-test/t/partition_column.test @@ -398,7 +398,7 @@ drop table t1; create table t1 as select to_seconds(null) as to_seconds; select data_type from information_schema.columns -where column_name='to_seconds'; +where table_schema='test' and column_name='to_seconds'; drop table t1; --error ER_PARSE_ERROR diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index a05e086d79b..300121e88fd 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -819,6 +819,104 @@ INSERT INTO t1 (d) VALUES ('1991-01-01'); SELECT * FROM t1 WHERE d = '1991-01-01'; DROP TABLE t1; +set global default_storage_engine=default; + +--echo # +--echo # MDEV-9455: [ERROR] mysqld got signal 11 +--echo # + +CREATE TABLE `t1` ( + `DIARY_TOTAL_DAY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + `IMORY_ID` bigint(20) NOT NULL, + `NAME` varchar(75) DEFAULT NULL, + `DATETIME` varchar(10) NOT NULL DEFAULT '', + `DAILY_CALL_CNT` int(11) DEFAULT NULL, + `DAILY_SMS_CNT` int(11) DEFAULT NULL, + `NUMBER` varchar(64) DEFAULT NULL, + `DURATION` varchar(16) DEFAULT NULL, + PRIMARY KEY (`DIARY_TOTAL_DAY_SEQ`,`DATETIME`), + KEY `IDX_t1_01` (`IMORY_ID`,`DATETIME`) +) AUTO_INCREMENT=328702514 DEFAULT CHARSET=utf8mb4 +PARTITION BY RANGE COLUMNS(`DATETIME`) +(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB, + PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB, + PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB, + PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB, + PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB, + PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB, + PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB, + PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB, + PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB, + PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB, + PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) +; + +CREATE TABLE `t2` ( + `DIARY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + `IMORY_ID` bigint(20) NOT NULL, + `CALL_TYPE` varchar(1) DEFAULT NULL, + `DATA_TYPE` varchar(1) DEFAULT NULL, + `FEATURES` varchar(1) DEFAULT NULL, + `NAME` varchar(75) DEFAULT NULL, + `NUMBER` varchar(64) DEFAULT NULL, + `DATETIME` datetime NOT NULL, + `REG_DATE` datetime NOT NULL, + `TITLE` varchar(50) DEFAULT NULL, + `BODY` varchar(4200) DEFAULT NULL, + `MIME_TYPE` varchar(32) DEFAULT NULL, + `DURATION` varchar(16) DEFAULT NULL, + `DEVICE_ID` varchar(64) DEFAULT NULL, + `DEVICE_NAME` varchar(32) DEFAULT NULL, + PRIMARY KEY (`DIARY_SEQ`,`DATETIME`,`REG_DATE`), + KEY `IDX_TB_DIARY_01` (`IMORY_ID`,`DATETIME`,`CALL_TYPE`,`NUMBER`), + KEY `IDX_TB_DIARY_02` (`REG_DATE`) +) AUTO_INCREMENT=688799006 DEFAULT CHARSET=utf8mb4 +PARTITION BY RANGE COLUMNS(REG_DATE) +(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB, + PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB, + PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB, + PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB, + PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB, + PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB, + PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB, + PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB, + PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB, + PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB, + PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) +; + +SELECT + A.IMORY_ID, + A.NUMBER, + A.NAME, + DATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE, + SUM( CASE WHEN A.DATA_TYPE='1' THEN 1 ELSE 0 END) AS CALL_CNT, + SUM( CASE WHEN A.DATA_TYPE IN ('2', '3') THEN 1 ELSE 0 END) AS SMS_CNT, + SUM(CAST(A.DURATION AS INT)) AS DURATION, + ( SELECT COUNT(*) + FROM t1 + WHERE IMORY_ID=A.IMORY_ID + AND NUMBER=A.NUMBER + AND NAME=A.NAME + AND DATETIME = DATE_FORMAT(A.DATETIME, '%Y-%m-%d') + ) STATS_COUNT +FROM t2 A +WHERE A.IMORY_ID = 55094102 + AND A.DATETIME LIKE ( + SELECT CONCAT (DATE_FORMAT(DATETIME, '%Y-%m-%d') ,'%') + FROM t2 + WHERE IMORY_ID=55094102 + AND DIARY_SEQ IN ( 608351221, 608351225, 608351229 ) + group by DATE_FORMAT(DATETIME, '%Y-%m-%d') + ) +GROUP BY A.IMORY_ID, A.NUMBER, A.NAME, DATE_FORMAT(A.DATETIME, '%Y-%m-%d') +; + +drop table t2, t1; + + +set global default_storage_engine='innodb'; + --echo # --echo # MDEV-5963: InnoDB: Assertion failure in file row0sel.cc line 2503, --echo # Failing assertion: 0 with "key ptr now exceeds key end by 762 bytes" diff --git a/mysql-test/t/partition_myisam.test b/mysql-test/t/partition_myisam.test index d07637057e0..4d083c37b68 100644 --- a/mysql-test/t/partition_myisam.test +++ b/mysql-test/t/partition_myisam.test @@ -216,6 +216,28 @@ PARTITION BY RANGE (a) PARTITION pMax VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (1, "Partition p1, first row"); DROP TABLE t1; + +--echo # +--echo # MDEV-10418 Assertion `m_extra_cache' failed +--echo # in ha_partition::late_extra_cache(uint) +--echo # + +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (f2 INT) ENGINE=MyISAM PARTITION BY RANGE(f2) (PARTITION pmax VALUES LESS THAN MAXVALUE); +INSERT INTO t2 VALUES (8); + +CREATE ALGORITHM = MERGE VIEW v AS SELECT f2 FROM t2, t1; + +UPDATE v SET f2 = 1; + +SELECT * FROM t2; + +DROP VIEW v; +DROP TABLE t2; +DROP TABLE t1; + --echo # --echo # bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED --echo # TABLE CORRUPTS MYISAM diff --git a/mysql-test/t/plugin_auth.test b/mysql-test/t/plugin_auth.test index 81b8ba6e0a3..c3c18b7e427 100644 --- a/mysql-test/t/plugin_auth.test +++ b/mysql-test/t/plugin_auth.test @@ -522,6 +522,8 @@ SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA= 'mysql' AND TABLE_NAME= 'user' AND COLUMN_NAME IN ('plugin', 'authentication_string') ORDER BY COLUMN_NAME; +let $datadir= `select @@datadir`; +remove_file $datadir/mysql_upgrade_info; --echo # diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 549814724d2..b6d1b4862a6 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3548,9 +3548,6 @@ EXECUTE s1; DEALLOCATE PREPARE s1; ---echo # ---echo # End of 5.5 tests. - ########################################################################### # # restoring of the Item tree in BETWEEN with dates @@ -3697,6 +3694,40 @@ EXECUTE stmt; deallocate prepare stmt; drop table t1,t2,t3,t4; +--echo # +--echo # MDEV-11859: the plans for the first and the second executions +--echo # of PS are not the same +--echo # + +create table t1 (id int, c varchar(3), key idx(c))engine=myisam; +insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy'); + +prepare stmt1 from +"explain extended + select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from +"select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; +flush status; +execute stmt1; +show status like '%Handler_read%'; +flush status; +execute stmt1; +show status like '%Handler_read%'; +deallocate prepare stmt1; + +prepare stmt2 from +"explain extended + select * from t1 where (1, 2) in ( select 3, 4 )"; +execute stmt2; +execute stmt2; +deallocate prepare stmt2; + +drop table t1; + --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test index 7ecca454f4c..5d12d46c9e9 100644 --- a/mysql-test/t/range_vs_index_merge.test +++ b/mysql-test/t/range_vs_index_merge.test @@ -57,9 +57,9 @@ SELECT * FROM City EXPLAIN SELECT * FROM City WHERE Population > 100000 AND Name LIKE 'Aba%' OR - Country IN ('CAN', 'ARG') AND ID < 3800 OR - Country < 'U' AND Name LIKE 'Zhu%' OR - ID BETWEEN 3800 AND 3810; + Country IN ('CAN', 'ARG') AND ID BETWEEN 120 AND 130 OR + Country <= 'ALB' AND Name LIKE 'L%' OR + ID BETWEEN 3807 AND 3810; # The output of the next 3 commands tells us about selectivities # of the conditions utilized in 2 queries following after them @@ -1206,6 +1206,41 @@ SELECT * FROM t1 DROP TABLE t1; + +--echo # +--echo # MDEV-8603: Wrong result OR/AND condition over index fields +--echo # + +CREATE TABLE t1 ( + id INT NOT NULL, + state VARCHAR(64), + capital VARCHAR(64), + UNIQUE KEY (id), + KEY state (state,id), + KEY capital (capital, id) +); + +INSERT INTO t1 VALUES + (1,'Arizona','Phoenix'), + (2,'Hawaii','Honolulu'), + (3,'Georgia','Atlanta'), + (4,'Florida','Tallahassee'), + (5,'Alaska','Juneau'), + (6,'Michigan','Lansing'), + (7,'Pennsylvania','Harrisburg'), + (8,'Virginia','Richmond') +; + +EXPLAIN +SELECT * FROM t1 FORCE KEY (state,capital) +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 + OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; +SELECT * FROM t1 FORCE KEY (state,capital) +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 + OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; + +DROP TABLE t1; + #the following command must be the last one in the file set session optimizer_switch='index_merge_sort_intersection=default'; diff --git a/mysql-test/t/repair_symlink-5543.test b/mysql-test/t/repair_symlink-5543.test index 58cc810b1a9..4c120334d05 100644 --- a/mysql-test/t/repair_symlink-5543.test +++ b/mysql-test/t/repair_symlink-5543.test @@ -9,7 +9,9 @@ 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 +--echo # Some systems fail with errcode 40, when doing openat, while others +--echo # don't have openat and fail with errcode 20. +--replace_regex / '.*\/t1/ 'MYSQL_TMP_DIR\/t1/ /40/20/ /".*"/"<errmsg>"/ repair table t1; drop table t1; @@ -17,7 +19,7 @@ drop table t1; 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 +--replace_regex / '.*\/t2/ 'MYSQL_TMP_DIR\/t2/ /40/20/ /".*"/"<errmsg>"/ repair table t2; drop table t2; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 08f7fa5035b..3ca210d9904 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9411,6 +9411,46 @@ drop procedure p1; drop table t1,t2,t3,t4,t5,t6; +--echo # +--echo # MDEV-11935: Queries in stored procedures with and +--echo # EXISTS(SELECT * FROM VIEW) crashes and closes hte conneciton. +--echo # + +CREATE TABLE ANY_TABLE ( + ENTITY_UID BIGINT NOT NULL +); +CREATE TABLE SECURITY_PATH( +origid BIGINT UNSIGNED NOT NULL, +destid BIGINT UNSIGNED NOT NULL, +KEY (destid) +); +CREATE VIEW ENTITY_ACCESS ( +ENTITY_UID, +OWNER_UID +) AS +SELECT SP1.origid, + SP2.destid +FROM SECURITY_PATH SP1 +JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid +; +--delimiter // +CREATE PROCEDURE SP_EXAMPLE_SELECT () +BEGIN + SELECT * + FROM ANY_TABLE AT1 + WHERE EXISTS ( SELECT * + FROM ENTITY_ACCESS EA + WHERE AT1.ENTITY_UID = EA.ENTITY_UID + AND EA.OWNER_UID IS NULL ); +END +// +--delimiter ; +CALL SP_EXAMPLE_SELECT (); +CALL SP_EXAMPLE_SELECT (); + +drop procedure SP_EXAMPLE_SELECT; +drop view ENTITY_ACCESS; +drop table ANY_TABLE, SECURITY_PATH; --echo # End of 10.0 test DELIMITER |; diff --git a/mysql-test/t/stat_tables_par.test b/mysql-test/t/stat_tables_par.test index 7305d1453a8..1866eb71d3b 100644 --- a/mysql-test/t/stat_tables_par.test +++ b/mysql-test/t/stat_tables_par.test @@ -44,7 +44,7 @@ select * from mysql.index_stats; # let $Q6= -select sum(l_extendedprice*l_discount) as revenue +select round(sum(l_extendedprice*l_discount),4) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 253160c46ac..d4ccbcf6f66 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1997,6 +1997,41 @@ EXECUTE stmt; drop table t1, t2, t3; +--echo # +--echo # MDEV-11078: NULL NOT IN (non-empty subquery) should never return results +--echo # + +create table t1(a int,b int); +create table t2(a int,b int); +insert into t1 value (1,2); +select (NULL) in (select 1 from t1); +select (null) in (select 1 from t2); +select 1 in (select 1 from t1); +select 1 in (select 1 from t2); +select 1 from dual where null in (select 1 from t1); +select 1 from dual where null in (select 1 from t2); +select (null,null) in (select * from t1); +select (null,null) in (select * from t2); +select 1 from dual where null not in (select 1 from t1); +select 1 from dual where null not in (select 1 from t2); +drop table t1,t2; + + +--echo # +--echo # MDEV-6486: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' +--echo # failed with SELECT SQ, TEXT field +--echo # + +CREATE TABLE t1 (a VARCHAR(8), KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),( 'bar'); + +CREATE TABLE t2 (b VARCHAR(8), c TINYTEXT, KEY(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('baz','baz'),('qux', 'qux'); + +SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT(DISTINCT c) +FROM t2 WHERE b <= 'quux' GROUP BY field; +drop table t1,t2; + SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index af6ec90ba74..b26c5036f3f 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -483,6 +483,26 @@ drop table t1; set optimizer_switch=@subselect_innodb_tmp; --echo # +--echo # MDEV-9635:Server crashes in part_of_refkey or assertion +--echo # `!created && key_to_save < (int)s->keys' failed in +--echo # TABLE::use_index(int) or with join_cache_level>2 +--echo # + +SET join_cache_level=3; + +CREATE TABLE t1 (f1 VARCHAR(1024)) ENGINE=InnoDB; +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +CREATE TABLE t2 (f2 VARCHAR(4)) ENGINE=InnoDB; +INSERT INTO t2 VALUES ('foo'),('bar'); + +SELECT * FROM v1, t2 WHERE ( f1, f2 ) IN ( SELECT f1, f1 FROM t1 ); + +set join_cache_level = default; +drop view v1; +drop table t1,t2; + +--echo # --echo # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding --echo # create table t1(a int) engine=innodb; @@ -535,4 +555,3 @@ from t1; drop table t1,t2; - diff --git a/mysql-test/t/symlink-aria-11902.test b/mysql-test/t/symlink-aria-11902.test new file mode 100644 index 00000000000..a2a266cbb25 --- /dev/null +++ b/mysql-test/t/symlink-aria-11902.test @@ -0,0 +1,6 @@ +# +# MDEV-11902 mi_open race condition +# +source include/have_maria.inc; +set default_storage_engine=Aria; +source symlink-myisam-11902.test; diff --git a/mysql-test/t/symlink-myisam-11902.test b/mysql-test/t/symlink-myisam-11902.test new file mode 100644 index 00000000000..8fd4961d1fb --- /dev/null +++ b/mysql-test/t/symlink-myisam-11902.test @@ -0,0 +1,60 @@ +# +# MDEV-11902 mi_open race condition +# +source include/have_debug_sync.inc; +source include/have_symlink.inc; +source include/not_windows.inc; +call mtr.add_suppression("File.*t1.* not found"); + +create table mysql.t1 (a int, b char(16), index(a)); +insert mysql.t1 values (100, 'test'),(101,'test'); +let $datadir=`select @@datadir`; + +exec mkdir $MYSQLTEST_VARDIR/tmp/foo; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval create table t1 (a int, b char(16), index(a)) + data directory="$MYSQLTEST_VARDIR/tmp/foo"; +insert t1 values (200, 'some'),(201,'some'); +select * from t1; +flush tables; +set debug_sync='mi_open_datafile SIGNAL ok WAIT_FOR go'; +send select * from t1; +connect con1, localhost, root; +set debug_sync='now WAIT_FOR ok'; +exec rm -r $MYSQLTEST_VARDIR/tmp/foo; +exec ln -s $datadir/mysql $MYSQLTEST_VARDIR/tmp/foo; +set debug_sync='now SIGNAL go'; +connection default; +replace_regex / '.*\/tmp\// 'MYSQLTEST_VARDIR\/tmp\// /31/20/ /40/20/ /20.*/20 <errmsg>)/; +error 29; +reap; +flush tables; +drop table if exists t1; +exec rm -r $MYSQLTEST_VARDIR/tmp/foo; + +# same with INDEX DIRECTORY +exec mkdir $MYSQLTEST_VARDIR/tmp/foo; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval create table t1 (a int, b char(16), index (a)) + index directory="$MYSQLTEST_VARDIR/tmp/foo"; +insert t1 values (200, 'some'),(201,'some'); +explain select a from t1; +select a from t1; +flush tables; +set debug_sync='mi_open_kfile SIGNAL waiting WAIT_FOR run'; +send select a from t1; +connection con1; +set debug_sync='now WAIT_FOR waiting'; +exec rm -r $MYSQLTEST_VARDIR/tmp/foo; +exec ln -s $datadir/mysql $MYSQLTEST_VARDIR/tmp/foo; +set debug_sync='now SIGNAL run'; +connection default; +replace_regex / '.*\/test\// '.\/test\// /31/20/ /40/20/ /20.*/20 <errmsg>)/; +error ER_FILE_NOT_FOUND; +reap; +flush tables; +drop table if exists t1; +exec rm -r $MYSQLTEST_VARDIR/tmp/foo; + +drop table mysql.t1; +set debug_sync='RESET'; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 52857f9169f..8de4743b9fd 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -534,12 +534,12 @@ INSERT IGNORE INTO t1 VALUES (0,'g'); CREATE TABLE t3 ( a varchar(1)) ; INSERT IGNORE INTO t3 VALUES ('g'); -CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ; +CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)); +INSERT INTO t2 VALUES (9), (10); create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0; SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); - drop view v1; DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/trigger_no_defaults-11698.test b/mysql-test/t/trigger_no_defaults-11698.test index fab7845ad7d..c10bec68314 100644 --- a/mysql-test/t/trigger_no_defaults-11698.test +++ b/mysql-test/t/trigger_no_defaults-11698.test @@ -23,3 +23,20 @@ insert t1 (b) values (20); insert t1 (b) values (30); select * from t1; drop table t1; +set sql_mode=default; + +# +# MDEV-11842 Fail to insert on a table where a field has no default +# +set sql_mode=''; +create table t1 ( + id int(11) not null auto_increment primary key, + data1 varchar(10) not null, + data2 varchar(10) not null +); +insert into t1 (data2) values ('x'); +create trigger test_trigger before insert on t1 for each row begin end; +insert into t1 (data2) values ('y'); +select * from t1; +drop table t1; +set sql_mode=default; diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index eb0fac57150..dd5311e4be3 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1738,12 +1738,39 @@ SHOW CREATE TABLE t1; DROP TABLE t1; --echo # +--echo # Bug#18408499 UNSIGNED BIGINT HIGH VALUES +--echo # WRONG NUMERICAL COMPARISON RESULTS +--echo # + +CREATE TABLE t1(value DECIMAL(24,0) NOT NULL); +INSERT INTO t1(value) +VALUES('100000000000000000000001'), + ('100000000000000000000002'), + ('100000000000000000000003'); +SELECT * FROM t1 WHERE value = '100000000000000000000002'; +SELECT * FROM t1 WHERE '100000000000000000000002' = value; +SELECT * FROM t1 WHERE value + 0 = '100000000000000000000002'; +SELECT * FROM t1 WHERE value = 100000000000000000000002; +SELECT * FROM t1 WHERE value + 0 = 100000000000000000000002; + +PREPARE stmt FROM 'SELECT * FROM t1 WHERE value = ?'; +set @a="100000000000000000000002"; +EXECUTE stmt using @a; +set @a=100000000000000000000002; +EXECUTE stmt using @a; +DEALLOCATE PREPARE stmt; + +ALTER TABLE t1 ADD INDEX value (value); +SELECT * FROM t1 WHERE value = '100000000000000000000002'; +DROP TABLE t1; + +--echo # --echo # End of 10.1 tests --echo # --echo # --echo # Test CREATE .. SELECT ---echo +--echo # create or replace table t1 as select 1.000000000000000000000000000000000 as a; show create table t1; diff --git a/mysql-test/t/update_innodb.test b/mysql-test/t/update_innodb.test index 67c356c4e2e..0e93e7d9593 100644 --- a/mysql-test/t/update_innodb.test +++ b/mysql-test/t/update_innodb.test @@ -37,3 +37,29 @@ UPDATE t1 a JOIN t2 b ON a.c1 = b.c1 JOIN v1 vw ON b.c2 = vw.c1 JOIN t3 del ON v drop view v1; drop table t1,t2,t3,t4; + +--echo # +--echo # MDEV-10232 Scalar result of subquery changes after adding an outer select stmt +--echo # + +CREATE TABLE t1 ( + a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + b_id INT(20) UNSIGNED NULL DEFAULT NULL, + c_id VARCHAR(255) NULL DEFAULT NULL, + PRIMARY KEY (a_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; + +CREATE TABLE t2 ( + b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + c_id VARCHAR(255) NULL DEFAULT NULL, + PRIMARY KEY (b_id), + INDEX idx_c_id (c_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; + +INSERT INTO t1 (b_id, c_id) VALUES (NULL, NULL); +INSERT INTO t2 (c_id) VALUES (NULL); +INSERT INTO t2 (c_id) VALUES (NULL); + +SELECT * FROM t1; +SELECT t2.b_id FROM t1,t2 WHERE t2.c_id = t1.c_id; +UPDATE t1 SET b_id = (SELECT t2.b_id FROM t2 t2 WHERE t2.c_id = t1.c_id); +SELECT * FROM t1; +drop table t1,t2; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index e5dd22c64c3..eaaebba166c 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -5508,6 +5508,66 @@ SHOW CREATE VIEW v1; drop view v1; drop table t1,t2; + +--echo # +--echo # MDEV-12099: usage of mergeable view with LEFT JOIN +--echo # that can be converted to INNER JOIN +--echo # + +create table t1 (a int, b int, key(a)) engine=myisam; +insert into t1 values + (3,20), (7,10), (2,10), (4,30), (8,70), + (7,70), (9,100), (9,60), (8,80), (7,60); + +create table t2 (c int, d int, key (c)) engine=myisam; +insert into t2 values + (50,100), (20, 200), (10,300), + (150,100), (120, 200), (110,300), + (250,100), (220, 200), (210,300); + +create table t3(e int, f int not null, key(e), unique (f)) engine=myisam; +insert into t3 values + (100, 3), (300, 5), (400, 4), (300,7), + (300,2), (600, 13), (800, 15), (700, 14), + (600, 23), (800, 25), (700, 24); + +create view v1 as + select * from t2 left join t3 on t3.e=t2.d where t3.f is not null; + +select * + from t1 left join v1 on v1.c=t1.b + where t1.a < 5; + +select * + from t1 left join ( t2 left join t3 on t3.e=t2.d ) + on t2.c=t1.b and t3.f is not null + where t1.a < 5; + +explain extended +select * + from t1 left join v1 on v1.c=t1.b + where t1.a < 5; + +explain extended +select * + from t1 left join ( t2 left join t3 on t3.e=t2.d ) + on t2.c=t1.b and t3.f is not null + where t1.a < 5; + +explain extended +select * + from t1 left join v1 on v1.c=t1.b and v1.f=t1.a + where t1.a < 5; + +explain extended +select * + from t1 left join ( t2 left join t3 on t3.e=t2.d ) + on t2.c=t1.b and t3.f=t1.a and t3.f is not null + where t1.a < 5; + +drop view v1; +drop table t1,t2,t3; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.5 tests. --echo # ----------------------------------------------------------------- |