summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/ctype_binary.result2
-rw-r--r--mysql-test/r/ctype_cp1251.result2
-rw-r--r--mysql-test/r/ctype_latin1.result2
-rw-r--r--mysql-test/r/ctype_ucs.result2
-rw-r--r--mysql-test/r/ctype_utf8.result2
-rw-r--r--mysql-test/r/derived_view.result40
-rw-r--r--mysql-test/r/failed_auth_unixsocket.result12
-rw-r--r--mysql-test/r/func_compress.result20
-rw-r--r--mysql-test/r/func_misc.result16
-rw-r--r--mysql-test/r/locked_temporary-5955.result2
-rw-r--r--mysql-test/r/partition.result39
-rw-r--r--mysql-test/r/partition_innodb.result39
-rw-r--r--mysql-test/r/partition_order.result6
-rw-r--r--mysql-test/r/subselect4.result22
-rw-r--r--mysql-test/r/subselect_mat.result14
-rw-r--r--mysql-test/r/subselect_sj_mat.result14
-rw-r--r--mysql-test/r/view.result16
17 files changed, 203 insertions, 47 deletions
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index 9b38776e1ec..3dfcaf18120 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -740,7 +740,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `c1` varbinary(10) NOT NULL DEFAULT ''
+ `c1` varbinary(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
index b6114ba6f59..7030a77b6b2 100644
--- a/mysql-test/r/ctype_cp1251.result
+++ b/mysql-test/r/ctype_cp1251.result
@@ -1132,7 +1132,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
+ `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index 630288ebcb7..b1507a3222b 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -1212,7 +1212,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `c1` varchar(10) NOT NULL DEFAULT ''
+ `c1` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 5ce58fd7617..6e5ea46fde1 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -2212,7 +2212,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
+ `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 4d558b2902c..ba96712653d 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -3089,7 +3089,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
+ `c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 80d2d64dced..64501ab5b76 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2366,6 +2366,46 @@ id select_type table type possible_keys key key_len ref rows Extra
3 DEPENDENT SUBQUERY pi ref gallery_id gallery_id 4 test.gal.id 4 Using temporary; Using filesort
drop table galleries, pictures;
#
+# MDEV-5740: Assertion
+#`!derived->first_select()->exclude_from_table_unique_test ||
+#derived->outer_select()-> exclude_from_table_unique_test'
+#failed on 2nd execution of PS with derived_merge
+#
+set @save_optimizer_switch5740=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+PREPARE stmt FROM '
+ INSERT INTO t1 SELECT * FROM t2 UNION SELECT * FROM (SELECT * FROM t1) AS sq
+';
+EXECUTE stmt;
+select * from t1;
+a
+1
+2
+3
+4
+1
+2
+EXECUTE stmt;
+select * from t1;
+a
+1
+2
+3
+4
+1
+2
+3
+4
+1
+2
+deallocate prepare stmt;
+drop table t1,t2;
+set optimizer_switch=@save_optimizer_switch5740;
+#
# end of 5.3 tests
#
set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/r/failed_auth_unixsocket.result b/mysql-test/r/failed_auth_unixsocket.result
new file mode 100644
index 00000000000..3cf58ca5bec
--- /dev/null
+++ b/mysql-test/r/failed_auth_unixsocket.result
@@ -0,0 +1,12 @@
+update mysql.user set plugin='unix_socket';
+flush privileges;
+connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
+ERROR HY000: Plugin 'unix_socket' is not loaded
+ERROR HY000: Plugin 'unix_socket' is not loaded
+install plugin unix_socket soname 'auth_socket.so';
+connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
+ERROR 28000: Access denied for user 'USER'@'localhost'
+ERROR 28000: Access denied for user 'USER'@'localhost'
+update mysql.user set plugin='';
+flush privileges;
+uninstall plugin unix_socket;
diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result
index 011dec4d555..22b75de8c60 100644
--- a/mysql-test/r/func_compress.result
+++ b/mysql-test/r/func_compress.result
@@ -127,3 +127,23 @@ NULL 825307441
EXPLAIN EXTENDED SELECT * FROM (SELECT UNCOMPRESSED_LENGTH(c1) FROM t1) AS s;
DROP TABLE t1;
End of 5.0 tests
+#
+# Start of 5.3 tests
+#
+#
+# MDEV-5783 Assertion `0' failed in make_sortkey(SORTPARAM*, uchar*, uchar*) on ORDER BY HEX(UNCOMPRESSED_LENGTH(pk))
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+SELECT UNCOMPRESSED_LENGTH(pk) FROM t1;
+UNCOMPRESSED_LENGTH(pk)
+NULL
+NULL
+Warnings:
+Warning 1259 ZLIB: Input data corrupted
+Warning 1259 ZLIB: Input data corrupted
+SELECT * FROM t1 ORDER BY HEX(UNCOMPRESSED_LENGTH(pk));
+DROP TABLE t1;
+#
+# End of 5.3 tests
+#
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index 1d1300a96db..0c4d22f89b7 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -272,6 +272,22 @@ SELECT NAME_CONST('a', -(1)) OR 1;
NAME_CONST('a', -(1)) OR 1
1
#
+#MDEV-5446: Assertion `!table || (!table->read_set ||
+#bitmap_is_set(table->read_set, field_index))' fails on
+#EXPLAIN EXTENDED with VALUES function
+#
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,10);
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN EXTENDED SELECT VALUES(b) FROM v1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select values(10) AS `VALUES(b)` from dual
+drop view v1;
+drop table t1;
+End of 5.3 tests
+#
# Bug #52165: Assertion failed: file .\dtoa.c, line 465
#
CREATE TABLE t1 (a SET('a'), b INT);
diff --git a/mysql-test/r/locked_temporary-5955.result b/mysql-test/r/locked_temporary-5955.result
new file mode 100644
index 00000000000..8999bdd39c4
--- /dev/null
+++ b/mysql-test/r/locked_temporary-5955.result
@@ -0,0 +1,2 @@
+CREATE TEMPORARY TABLE tmp (i INT) ENGINE=InnoDB;
+LOCK TABLES tmp AS p WRITE;
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 5ea037df759..b8011656415 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2535,45 +2535,6 @@ i
4
DROP TABLE t1;
#
-# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)
-#
-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);
-insert into t1 values (1,0,17);
-insert into t1 values (1,2,25);
-insert into t1 values (10,20,122);
-insert into t1 values (10,20,123);
-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;
-explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref PRIMARY,a,b b 4 const 982 Using where
-create temporary table t3 as
-select * from t1 where a=1 and b=2 and pk between 1 and 999 ;
-select count(*) from t3;
-count(*)
-802
-drop table t3;
-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;
-count(*)
-802
-drop table t3;
-drop table t1,t2;
-#
# MDEV-5555: Incorrect index_merge on BTREE indices
#
CREATE TABLE t1 (
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 9a837cb4b5b..07364bd1370 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -654,4 +654,43 @@ col1 col2 col3
1 2 2013-03-11 16:33:04
1 2 2013-03-11 16:33:24
DROP TABLE t1;
+#
+# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)
+#
+create table t1 (
+a int not null,
+b int not null,
+pk int not null,
+primary key (pk),
+key(a),
+key(b)
+) engine=innodb partition by hash(pk) partitions 10;
+insert into t1 values (1,2,4);
+insert into t1 values (1,0,17);
+insert into t1 values (1,2,25);
+insert into t1 values (10,20,122);
+insert into t1 values (10,20,123);
+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;
+explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge PRIMARY,a,b b,a 4,4 NULL # Using intersect(b,a); Using where; Using index
+create temporary table t3 as
+select * from t1 where a=1 and b=2 and pk between 1 and 999 ;
+select count(*) from t3;
+count(*)
+802
+drop table t3;
+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;
+count(*)
+802
+drop table t3;
+drop table t1,t2;
set global default_storage_engine=default;
diff --git a/mysql-test/r/partition_order.result b/mysql-test/r/partition_order.result
index 06c1b63a382..cecfc90eefb 100644
--- a/mysql-test/r/partition_order.result
+++ b/mysql-test/r/partition_order.result
@@ -734,8 +734,8 @@ a b
7 1
35 2
3 3
-2 4
30 4
+2 4
4 5
6 6
select * from t1 force index (b) where b < 10 ORDER BY b;
@@ -744,16 +744,16 @@ a b
7 1
35 2
3 3
-2 4
30 4
+2 4
4 5
6 6
select * from t1 force index (b) where b < 10 ORDER BY b DESC;
a b
6 6
4 5
-30 4
2 4
+30 4
3 3
35 2
7 1
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 66d3fa6214a..a909230121d 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2331,6 +2331,28 @@ id a2 a3 id a2 a3
DROP VIEW v2;
DROP TABLE t1,t2;
#
+# MDEV-5686: degenerate disjunct in NOT IN subquery
+#
+CREATE TABLE t1 (a int, b int, c varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1,'CAN'),(2,2,'AUS');
+CREATE TABLE t2 (f int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3);
+EXPLAIN EXTENDED
+SELECT * FROM t2
+WHERE f NOT IN (SELECT b FROM t1
+WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select 3 AS `f` from dual where (not(<expr_cache><3>(<in_optimizer>(3,<exists>(select `test`.`t1`.`b` from `test`.`t1` where (((`test`.`t1`.`c` = 'USA') or (`test`.`t1`.`c` <> 'USA')) and trigcond(((<cache>(3) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))) and (`test`.`t1`.`b` = `test`.`t1`.`a`)) having trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))))
+SELECT * FROM t2
+WHERE f NOT IN (SELECT b FROM t1
+WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b);
+f
+3
+DROP TABLE t1,t2;
+#
# MDEV-3899 Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT
#
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index cd78d9f93fb..6cc627ad16c 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2055,6 +2055,20 @@ EXECUTE stmt;
a
DROP TABLE t1, t2;
DROP VIEW v2;
+#
+# MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON
+#
+SET @tmp_mdev5811= @@big_tables;
+SET big_tables = ON;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+SELECT * FROM t1 AS t1_1, t1 AS t1_2
+WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 );
+a a
+DROP TABLE t1,t2;
+SET big_tables=@tmp_mdev5811;
# End of 5.3 tests
#
# MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 46972a79a05..95dfc34777b 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2095,6 +2095,20 @@ EXECUTE stmt;
a
DROP TABLE t1, t2;
DROP VIEW v2;
+#
+# MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON
+#
+SET @tmp_mdev5811= @@big_tables;
+SET big_tables = ON;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+SELECT * FROM t1 AS t1_1, t1 AS t1_2
+WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 );
+a a
+DROP TABLE t1,t2;
+SET big_tables=@tmp_mdev5811;
# End of 5.3 tests
#
# MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 3817df4f440..a382cb86869 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -5000,6 +5000,22 @@ v1_field1
deallocate prepare my_stmt;
DROP VIEW v1,v2;
DROP TABLE t1,t2,t3,t4;
+#
+#MDEV-5717: Server crash with insert statement containing DEFAULT into
+#view
+#
+CREATE TABLE t1 (
+`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+`test` tinyint(3) unsigned NOT NULL DEFAULT '0',
+PRIMARY KEY (`id`)
+);
+CREATE VIEW v1 AS (select t1.id AS id, t1.test AS test from t1);
+INSERT INTO v1 SET test = DEFAULT;
+select * from v1;
+id test
+1 0
+drop view v1;
+drop table t1;
# -----------------------------------------------------------------
# -- End of 5.3 tests.
# -----------------------------------------------------------------
View Lorry Controller Status