summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/bdb.result2
-rw-r--r--mysql-test/r/blackhole.result11
-rw-r--r--mysql-test/r/endspace.result2
-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/myisam.result2
-rw-r--r--mysql-test/r/select.result148
-rw-r--r--mysql-test/r/subselect.result60
-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
12 files changed, 276 insertions, 26 deletions
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index 33398d1ddd7..91c385112b4 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -1442,7 +1442,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c c 11 const # Using where
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
diff --git a/mysql-test/r/blackhole.result b/mysql-test/r/blackhole.result
index 140d7e73d48..0ee5f326e0f 100644
--- a/mysql-test/r/blackhole.result
+++ b/mysql-test/r/blackhole.result
@@ -123,3 +123,14 @@ 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;
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/innodb.result b/mysql-test/r/innodb.result
index 38d71ac7a42..496c8aa90d2 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1991,7 +1991,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 2e0acf303c2..92b3ea0e42b 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -731,3 +731,16 @@ select @@identity;
@@identity
0
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 6be37450835..f658ff06624 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/myisam.result b/mysql-test/r/myisam.result
index 0f6e0ad537a..83359048a8c 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/select.result b/mysql-test/r/select.result
index f50f4859d48..c3132a1b5f6 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/subselect.result b/mysql-test/r/subselect.result
index 06f8c019265..7177b4e4432 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -224,7 +224,7 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
Warnings:
-Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
select * from t3 where exists (select * from t2 where t2.b=t3.a);
a
@@ -313,8 +313,8 @@ id select_type table type possible_keys key key_len ref rows Extra
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
Warnings:
-Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1
-Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
ERROR 21000: Subquery returns more than 1 row
@@ -330,9 +330,9 @@ patient_uq clinic_uq
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where
-2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Using index
+2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 t6.clinic_uq 1 Using where; Using index
Warnings:
-Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
ERROR 23000: Column 'a' in field list is ambiguous
@@ -868,7 +868,7 @@ explain extended select (select a+1) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
Warnings:
-Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`
select (select a+1) from t1;
@@ -1741,9 +1741,9 @@ Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `tes
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index
+2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 tt.id 1 Using where; Using index
Warnings:
-Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
@@ -2279,7 +2279,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY up ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
Warnings:
-Note 1276 Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
drop table t1;
CREATE TABLE t1 (t1_a int);
@@ -3712,3 +3712,45 @@ bb 2
cc 3
dd 1
DROP TABLE t1,t2,t3;
+CREATE TABLE t1(f1 int);
+CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
+INSERT INTO t1 VALUES (1),(1),(2),(2);
+INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11");
+SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1;
+sq
+2
+4
+SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2;
+tt
+2
+2
+PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1';
+EXECUTE stmt1;
+sq
+2
+4
+EXECUTE stmt1;
+sq
+2
+4
+DEALLOCATE PREPARE stmt1;
+SELECT f2, AVG(f21),
+(SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test
+FROM t2 GROUP BY f2;
+f2 AVG(f21) test
+1 1.0000 2004-02-29 11:11:11
+2 2.0000 2004-02-29 11:11:11
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL);
+INSERT INTO t1 VALUES
+(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),
+(3,2,'k'), (3,1,'l'), (1,9,'m');
+SELECT a, MAX(b),
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test
+FROM t1 GROUP BY a;
+a MAX(b) test
+1 9 m
+2 3 h
+3 4 i
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 29143b9e504..b975ea8cbdc 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 Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 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 Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 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 Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 4
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 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 Extra
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 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 Extra
2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 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 Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 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 Extra
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 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 Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 8 Using where
2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 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 Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 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 73b67a2241e..b28f404c641 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 9e2bc52657a..b3c5760e2c9 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 21
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