summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/binlog_row_blackhole.result11
-rw-r--r--mysql-test/r/binlog_stm_blackhole.result11
-rw-r--r--mysql-test/r/endspace.result2
-rw-r--r--mysql-test/r/func_in.result44
-rw-r--r--mysql-test/r/innodb.result2
-rw-r--r--mysql-test/r/insert_select.result13
-rw-r--r--mysql-test/r/insert_update.result17
-rw-r--r--mysql-test/r/keywords.result13
-rw-r--r--mysql-test/r/mix2_myisam.result2
-rw-r--r--mysql-test/r/myisam.result2
-rw-r--r--mysql-test/r/ndb_basic.result2
-rw-r--r--mysql-test/r/ndb_blob.result4
-rw-r--r--mysql-test/r/ndb_lock.result2
-rw-r--r--mysql-test/r/no-threads.result6
-rw-r--r--mysql-test/r/one_thread_per_connection.require2
-rw-r--r--mysql-test/r/partition_innodb.result54
-rw-r--r--mysql-test/r/ps.result6
-rw-r--r--mysql-test/r/select.result148
-rw-r--r--mysql-test/r/subselect3.result22
-rw-r--r--mysql-test/r/type_blob.result4
-rw-r--r--mysql-test/r/update.result19
21 files changed, 362 insertions, 24 deletions
diff --git a/mysql-test/r/binlog_row_blackhole.result b/mysql-test/r/binlog_row_blackhole.result
index a02aea4ea49..f370232e2c3 100644
--- a/mysql-test/r/binlog_row_blackhole.result
+++ b/mysql-test/r/binlog_row_blackhole.result
@@ -122,6 +122,17 @@ master-bin.000001 # Query 1 # use `test`; alter table t1 add b int
master-bin.000001 # Query 1 # use `test`; alter table t1 drop b
master-bin.000001 # Query 1 # use `test`; create table t3 like t1
drop table t1,t2,t3;
+CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE;
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 ADD INDEX(a);
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 DROP INDEX a;
+ALTER TABLE t1 ADD UNIQUE INDEX(a);
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 DROP INDEX a;
+ALTER TABLE t1 ADD PRIMARY KEY(a);
+DELETE FROM t1 WHERE a=10;
+DROP TABLE t1;
reset master;
create table t1 (a int) engine=blackhole;
set autocommit=0;
diff --git a/mysql-test/r/binlog_stm_blackhole.result b/mysql-test/r/binlog_stm_blackhole.result
index 309f45aed49..d382c94fba9 100644
--- a/mysql-test/r/binlog_stm_blackhole.result
+++ b/mysql-test/r/binlog_stm_blackhole.result
@@ -123,6 +123,17 @@ master-bin.000001 # Query 1 # use `test`; create table t3 like t1
master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t3
master-bin.000001 # Query 1 # use `test`; replace into t1 select * from t3
drop table t1,t2,t3;
+CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE;
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 ADD INDEX(a);
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 DROP INDEX a;
+ALTER TABLE t1 ADD UNIQUE INDEX(a);
+DELETE FROM t1 WHERE a=10;
+ALTER TABLE t1 DROP INDEX a;
+ALTER TABLE t1 ADD PRIMARY KEY(a);
+DELETE FROM t1 WHERE a=10;
+DROP TABLE t1;
reset master;
create table t1 (a int) engine=blackhole;
set autocommit=0;
diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result
index 0e68418a80f..003ee7ffd5e 100644
--- a/mysql-test/r/endspace.result
+++ b/mysql-test/r/endspace.result
@@ -98,7 +98,7 @@ concat('|', text1, '|')
|teststring |
explain select concat('|', text1, '|') from t1 where text1='teststring ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range key1 key1 22 NULL 2 Using where
+1 SIMPLE t1 ref key1 key1 22 const 2 Using where
select concat('|', text1, '|') from t1 where text1 like 'teststring_%';
concat('|', text1, '|')
|teststring |
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index cf44c56f061..9ec621b7f35 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -355,6 +355,50 @@ some_id
1
2
drop table t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);
+CREATE TABLE t3 (a int PRIMARY KEY);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+CREATE TABLE t4 (a int PRIMARY KEY,b int);
+INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
+(1003,1003),(1004,1004);
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t3
+JOIN t1 ON t3.a=t1.a
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
+SELECT STRAIGHT_JOIN * FROM t3
+JOIN t1 ON t3.a=t1.a
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+a a b a b a b
+3 3 1 3 2 1 1
+3 3 1 3 2 2 2
+4 4 1 4 2 1 1
+4 4 1 4 2 2 2
+EXPLAIN SELECT STRAIGHT_JOIN
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
+SELECT STRAIGHT_JOIN
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+3
+3
+DROP TABLE t1,t2,t3,t4;
End of 5.0 tests
create table t1(f1 char(1));
insert into t1 values ('a'),('b'),('1');
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 621599df34e..642c9ff2bef 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1984,7 +1984,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c c 11 const # Using where; Using index
explain select count(*) from t1 where t='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range t t 13 NULL # Using where
+1 SIMPLE t1 ref t t 13 const # Using where
explain select count(*) from t1 where v like 'a%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index fdb59c02b9d..18a0ed1a1cb 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -717,3 +717,16 @@ select * from t1;
f1 f2
1 2
drop table t1;
+CREATE TABLE t1 (f1 INT, f2 INT );
+CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT);
+INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
+INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
+INSERT INTO t2 (f1, f2)
+SELECT f1, f1 FROM t2 src WHERE f1 < 2
+ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
+SELECT * FROM t2;
+f1 f2
+101 1
+2 2
+10 10
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index baf3f71c4f7..f608e0e7895 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -219,3 +219,20 @@ SELECT * FROM t1;
a b
45 2
DROP TABLE t1;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+INSERT INTO t1 SELECT 1, j;
+ERROR 42S22: Unknown column 'j' in 'field list'
+DROP TABLE t1;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, c INT);
+INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3
+ON DUPLICATE KEY UPDATE j= a;
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t2 VALUES (1), (3);
+INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
+ERROR 42S22: Unknown column 'a' in 'field list'
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/keywords.result b/mysql-test/r/keywords.result
index 597983dab7e..5f338ad6a62 100644
--- a/mysql-test/r/keywords.result
+++ b/mysql-test/r/keywords.result
@@ -32,3 +32,16 @@ end while contributors;
end|
drop procedure p1;
drop procedure p2;
+create table t1 (connection int, b int);
+create procedure p1()
+begin
+declare connection int;
+select max(t1.connection) into connection from t1;
+select concat("max=",connection) 'p1';
+end|
+insert into t1 (connection) values (1);
+call p1();
+p1
+max=1
+drop procedure p1;
+drop table t1;
diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result
index 45b4784251a..569eefb34ec 100644
--- a/mysql-test/r/mix2_myisam.result
+++ b/mysql-test/r/mix2_myisam.result
@@ -1532,7 +1532,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c c 11 const # Using where; Using index
explain select count(*) from t1 where t='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range t t 13 NULL # Using where
+1 SIMPLE t1 ref t t 13 const # Using where
explain select count(*) from t1 where v like 'a%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 8560efd4ee1..bb666b2e499 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -1071,7 +1071,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c c 11 const # Using where; Using index
explain select count(*) from t1 where t='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range t t 13 NULL # Using where
+1 SIMPLE t1 ref t t 13 const # Using where
explain select count(*) from t1 where v like 'a%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result
index b4a55641e80..7717e9fe72a 100644
--- a/mysql-test/r/ndb_basic.result
+++ b/mysql-test/r/ndb_basic.result
@@ -568,7 +568,7 @@ t1
insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2),(6,1),(7,1);
explain select * from t1 where a12345678901234567890123456789a1234567890=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a12345678901234567890123456789a1234567890 a12345678901234567890123456789a1234567890 5 const # Using where
+1 SIMPLE t1 ref a12345678901234567890123456789a1234567890 a12345678901234567890123456789a1234567890 5 const # Using where with pushed condition
select * from t1 where a12345678901234567890123456789a1234567890=2;
a1234567890123456789012345678901234567890 a12345678901234567890123456789a1234567890
5 2
diff --git a/mysql-test/r/ndb_blob.result b/mysql-test/r/ndb_blob.result
index 04f2cea6250..ad33c7994d1 100644
--- a/mysql-test/r/ndb_blob.result
+++ b/mysql-test/r/ndb_blob.result
@@ -242,7 +242,7 @@ insert into t1 values(9,'b9',999,'dd9');
commit;
explain select * from t1 where c >= 100 order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c c 4 NULL # Using where; Using filesort
+1 SIMPLE t1 range c c 4 NULL # Using where with pushed condition; Using filesort
select * from t1 where c >= 100 order by a;
a b c d
1 b1 111 dd1
@@ -278,7 +278,7 @@ insert into t1 values(2,@b2,222,@d2);
commit;
explain select * from t1 where c >= 100 order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c c 4 NULL # Using where; Using filesort
+1 SIMPLE t1 range c c 4 NULL # Using where with pushed condition; Using filesort
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where c >= 100 order by a;
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
diff --git a/mysql-test/r/ndb_lock.result b/mysql-test/r/ndb_lock.result
index 44eb5c8b3f3..9057731c3f4 100644
--- a/mysql-test/r/ndb_lock.result
+++ b/mysql-test/r/ndb_lock.result
@@ -121,7 +121,7 @@ select * from t1 where x = 1 for update;
x y z
1 one 1
select * from t1 where x = 2 for update;
-ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+Got one of the listed errors
rollback;
commit;
begin;
diff --git a/mysql-test/r/no-threads.result b/mysql-test/r/no-threads.result
new file mode 100644
index 00000000000..50e52138be8
--- /dev/null
+++ b/mysql-test/r/no-threads.result
@@ -0,0 +1,6 @@
+select 1+1;
+1+1
+2
+select 1+2;
+1+2
+3
diff --git a/mysql-test/r/one_thread_per_connection.require b/mysql-test/r/one_thread_per_connection.require
new file mode 100644
index 00000000000..b2efbca6441
--- /dev/null
+++ b/mysql-test/r/one_thread_per_connection.require
@@ -0,0 +1,2 @@
+@@thread_handling
+one-thread-per-connection
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 4449d105db0..a9e43653506 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -74,3 +74,57 @@ t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION p0 VALUES IN (0) ENGINE = InnoDB) */
drop table t1;
+create table t1
+(
+id int unsigned auto_increment,
+time datetime not null,
+first_name varchar(40),
+last_name varchar(50),
+primary key (id, time),
+index first_index (first_name),
+index last_index (last_name)
+) engine=Innodb partition by range (to_days(time)) (
+partition p1 values less than (to_days('2007-02-07')),
+partition p2 values less than (to_days('2007-02-08')),
+partition p3 values less than MAXVALUE
+);
+insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert'),
+('2007-02-07', 'Mark', 'Nate'), ('2007-02-07', 'Nate', 'Oscar'),
+('2007-02-07', 'Zack', 'Alice'), ('2007-02-07', 'Jack', 'Kathy'),
+('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Brian', 'Charles'),
+('2007-02-06', 'Charles', 'David'), ('2007-02-06', 'David', 'Eric'),
+('2007-02-07', 'Hector', 'Isaac'), ('2007-02-07', 'Oscar', 'Patricia'),
+('2007-02-07', 'Patricia', 'Q'), ('2007-02-07', 'X', 'Yuri'),
+('2007-02-07', 'Robert', 'Shawn'), ('2007-02-07', 'Kathy', 'Lois'),
+('2007-02-07', 'Eric', 'Francis'), ('2007-02-06', 'Shawn', 'Theron'),
+('2007-02-06', 'U', 'Vincent'), ('2007-02-06', 'Francis', 'George'),
+('2007-02-06', 'George', 'Hector'), ('2007-02-06', 'Vincent', 'Walter'),
+('2007-02-06', 'Walter', 'X'), ('2007-02-07', 'Lois', 'Mark'),
+('2007-02-07', 'Yuri', 'Zack'), ('2007-02-07', 'Isaac', 'Jack'),
+('2007-02-07', 'Sharon', 'Mark'), ('2007-02-07', 'Michael', 'Michelle'),
+('2007-02-07', 'Derick', 'Nathan'), ('2007-02-07', 'Peter', 'Xavier'),
+('2007-02-07', 'Fred', 'Harold'), ('2007-02-07', 'Katherine', 'Lisa'),
+('2007-02-07', 'Tom', 'Rina'), ('2007-02-07', 'Jerry', 'Victor'),
+('2007-02-07', 'Alexander', 'Terry'), ('2007-02-07', 'Justin', 'John'),
+('2007-02-07', 'Greg', 'Ernest'), ('2007-02-07', 'Robert', 'Q'),
+('2007-02-07', 'Nate', 'Mark'), ('2007-02-07', 'Oscar', 'Nate'),
+('2007-02-07', 'Alice', 'Zack'), ('2007-02-07', 'Kathy', 'Jack'),
+('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Charles', 'Brian'),
+('2007-02-06', 'David', 'Charles'), ('2007-02-06', 'Eric', 'David'),
+('2007-02-07', 'Isaac', 'Hector'), ('2007-02-07', 'Patricia', 'Oscar'),
+('2007-02-07', 'Q', 'Patricia'), ('2007-02-07', 'Yuri', 'X'),
+('2007-02-07', 'Shawn', 'Robert'), ('2007-02-07', 'Lois', 'Kathy'),
+('2007-02-07', 'Francis', 'Eric'), ('2007-02-06', 'Theron', 'Shawn'),
+('2007-02-06', 'Vincent', 'U'), ('2007-02-06', 'George', 'Francis'),
+('2007-02-06', 'Hector', 'George'), ('2007-02-06', 'Walter', 'Vincent'),
+('2007-02-06', 'X', 'Walter'), ('2007-02-07', 'Mark', 'Lois'),
+('2007-02-07', 'Zack', 'Yuri'), ('2007-02-07', 'Jack', 'Isaac'),
+('2007-02-07', 'Mark', 'Sharon'), ('2007-02-07', 'Michelle', 'Michael'),
+('2007-02-07', 'Nathan', 'Derick'), ('2007-02-07', 'Xavier', 'Peter'),
+('2007-02-07', 'Harold', 'Fred'), ('2007-02-07', 'Lisa', 'Katherine'),
+('2007-02-07', 'Rina', 'Tom'), ('2007-02-07', 'Victor', 'Jerry'),
+('2007-02-07', 'Terry', 'Alexander'), ('2007-02-07', 'John', 'Justin'),
+('2007-02-07', 'Ernest', 'Greg');
+SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake';
+id time first_name last_name
+drop table t1;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index a99fdb16868..7b19492faec 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -2157,11 +2157,11 @@ drop view v1;
drop table t1;
create procedure proc_1() install plugin my_plug soname 'some_plugin.so';
call proc_1();
-ERROR HY000: Can't open shared library
+Got one of the listed errors
call proc_1();
-ERROR HY000: Can't open shared library
+Got one of the listed errors
call proc_1();
-ERROR HY000: Can't open shared library
+Got one of the listed errors
drop procedure proc_1;
create function func_1() returns int begin install plugin my_plug soname '/tmp/plugin'; return 1; end|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index ec4ac0d6079..e9fda0c2ad6 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3785,4 +3785,152 @@ case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,
coalesce(cast(1111111111111111111 as unsigned), 1) co;
i c co
1111111111111111111 1111111111111111111 1111111111111111111
+CREATE TABLE t1 (name varchar(255));
+CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+name n
+bb 1
+aa 2
+cc 3
+cc  4
+cc 5
+bb 6
+cc 7
+SELECT * FROM t2 ORDER BY name;
+name n
+aa 2
+bb 1
+bb 6
+cc  4
+cc 3
+cc 5
+cc 7
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+name LENGTH(name) n
+aa 2 2
+bb 2 1
+bb 3 6
+cc  4 4
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref name name 6 const 3 Using where
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+name LENGTH(name) n
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range name name 6 NULL 3 Using where
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+name LENGTH(name) n
+cc 5 3
+cc  4 4
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+name LENGTH(name) n
+cc  4 4
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5
+1 SIMPLE t2 ref name name 6 test.t1.name 2
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+name name n
+ccc NULL NULL
+bb bb 1
+bb bb 6
+cc cc 3
+cc cc 5
+cc cc 7
+aa aa 2
+aa aa 2
+DROP TABLE t1,t2;
+CREATE TABLE t1 (name text);
+CREATE TABLE t2 (name text, n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+name n
+bb 1
+aa 2
+cc 3
+cc  4
+cc 5
+bb 6
+cc 7
+SELECT * FROM t2 ORDER BY name;
+name n
+aa 2
+bb 1
+bb 6
+cc  4
+cc 3
+cc 5
+cc 7
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+name LENGTH(name) n
+aa 2 2
+bb 2 1
+bb 3 6
+cc  4 4
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref name name 6 const 3 Using where
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+name LENGTH(name) n
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range name name 6 NULL 3 Using where
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+name LENGTH(name) n
+cc 5 3
+cc  4 4
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+name LENGTH(name) n
+cc  4 4
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5
+1 SIMPLE t2 ref name name 6 test.t1.name 2
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+name name n
+ccc NULL NULL
+bb bb 1
+bb bb 6
+cc cc 3
+cc cc 5
+cc cc 7
+aa aa 2
+aa aa 2
+DROP TABLE t1,t2;
End of 5.0 tests
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 42dd87a82de..03c35d51045 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -29,7 +29,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
explain extended
select a, oref from t2
@@ -38,7 +38,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
select a, oref, a in (
select max(ie) from t1 where oref=t2.oref group by grp union
@@ -91,7 +91,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2`
flush status;
select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
@@ -156,7 +156,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where
Warnings:
-Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
drop table t1, t2, t3;
create table t1 (a int NOT NULL, b int NOT NULL, key(a));
@@ -184,7 +184,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 100.00 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where
Warnings:
-Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
drop table t1,t2,t3;
create table t1 (oref int, grp int);
@@ -240,7 +240,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
a b oref Z
@@ -257,7 +257,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
select a,b, oref,
(a,b) in (select a,b from t1,t4 where c=t2.oref) Z
@@ -302,7 +302,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 8 100.00 Using where
2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10))
drop table t1, t2;
create table t1 (oref char(4), grp int, ie int);
@@ -432,7 +432,7 @@ alter table t1 add index idx(oref,ie);
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key
+2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 t2.oref,func 4 Using where; Using index; Full scan on NULL key
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
oref a Z
ee NULL NULL
@@ -457,7 +457,7 @@ group by grp having min(ie) > 1) Z
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 t2.oref 2 Using where; Using temporary; Using filesort
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
@@ -572,7 +572,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00
2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 Using where; Full scan on NULL key
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2`
drop table t1,t2;
create table t1 (oref char(4), grp int, ie int primary key);
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index e5adad14267..5e5bc7682d3 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -610,12 +610,12 @@ create table t1 (id integer primary key auto_increment, txt text, index txt_inde
insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL);
select * from t1 where txt='Chevy' or txt is NULL;
id txt
-3 NULL
1 Chevy
2 Chevy
+3 NULL
explain select * from t1 where txt='Chevy' or txt is NULL;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range txt_index txt_index 23 NULL 2 Using where
+1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where
select * from t1 where txt='Chevy ';
id txt
1 Chevy
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 5b73cb9501a..a40d3451a62 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -434,3 +434,22 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
DROP TABLE t1;
+CREATE TABLE t1 (
+a INT(11),
+quux decimal( 31, 30 ),
+UNIQUE KEY bar (a),
+KEY quux (quux)
+);
+INSERT INTO
+t1 ( a, quux )
+VALUES
+( 1, 1 ),
+( 2, 0.1 );
+INSERT INTO t1( a )
+SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
+SELECT * FROM t1;
+a quux
+1 1.000000000000000000000000000000
+2 0.100000000000000000000000000000
+3 NULL
+DROP TABLE t1;
View Lorry Controller Status