# At a local mysql-6.0-bugteam repository of davi
2987 Davi Arnaut 2009-01-29 [merge]
Merge from upstream 6.0-bugteam
added:
mysql-test/suite/binlog/r/binlog_tmp_table.result
mysql-test/suite/binlog/t/binlog_tmp_table.test
modified:
mysql-test/lib/mtr_report.pl
mysql-test/r/subselect.result
mysql-test/r/subselect3.result
mysql-test/r/subselect3_jcl6.result
mysql-test/r/subselect_no_mat.result
mysql-test/r/subselect_no_opts.result
mysql-test/r/subselect_no_semijoin.result
mysql-test/t/subselect.test
mysql-test/t/subselect3.test
mysql-test/t/user_limits.test
sql/item_cmpfunc.cc
sql/sql_delete.cc
sql/sql_select.cc
sql/sql_table.cc
=== modified file 'mysql-test/lib/mtr_report.pl'
--- a/mysql-test/lib/mtr_report.pl 2009-01-06 16:52:32 +0000
+++ b/mysql-test/lib/mtr_report.pl 2009-01-29 13:55:56 +0000
@@ -497,6 +497,11 @@ sub mtr_report_stats ($) {
# this test is expected to print warnings
($testname eq 'main.innodb_bug39438') or
+ # Bug#39886, logs 'Table full' error message
+ (($testname eq 'main.almost_full' or
+ $testname eq 'main.myisam_data_pointer_size_func') and
+ (/The table '.*' is full/
+ )) or
# maria-recovery.test has warning about missing log file
/File '.*maria_log.000.*' not found \(Errcode: 2\)/ or
# and about marked-corrupted table
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2009-01-16 14:28:04 +0000
+++ b/mysql-test/r/subselect.result 2009-01-29 08:50:30 +0000
@@ -4659,7 +4659,6 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
a incorrect
1 1
DROP TABLE t1,t2,t3;
-End of 5.1 tests.
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
@@ -4921,3 +4920,40 @@ ERROR 42000: You have an error in your S
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
DROP TABLE t1, t2;
+#
+# BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
+#
+create table t1(id integer primary key, g integer, v integer, s char(1));
+create table t2(id integer primary key, g integer, v integer, s char(1));
+insert into t1 values
+(10, 10, 10, 'l'),
+(20, 20, 20, 'l'),
+(40, 40, 40, 'l'),
+(41, 40, null, 'l'),
+(50, 50, 50, 'l'),
+(51, 50, null, 'l'),
+(60, 60, 60, 'l'),
+(61, 60, null, 'l'),
+(70, 70, 70, 'l'),
+(90, 90, null, 'l');
+insert into t2 values
+(10, 10, 10, 'r'),
+(30, 30, 30, 'r'),
+(50, 50, 50, 'r'),
+(60, 60, 60, 'r'),
+(61, 60, null, 'r'),
+(70, 70, 70, 'r'),
+(71, 70, null, 'r'),
+(80, 80, 80, 'r'),
+(81, 80, null, 'r'),
+(100,100,null, 'r');
+select *
+from t1
+where v in(select v
+from t2
+where t1.g=t2.g) is unknown;
+id g v s
+51 50 NULL l
+61 60 NULL l
+drop table t1, t2;
+End of 5.1 tests.
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result 2008-12-28 15:00:21 +0000
+++ b/mysql-test/r/subselect3.result 2009-01-28 18:53:58 +0000
@@ -804,6 +804,60 @@ WHERE INNR.varchar_key > 'n{'
);
varchar_nokey
DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (11);
+# 2nd and 3rd columns should be same
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a;
+a ROW(11, 12) = (SELECT a, 22) ROW(11, 12) IN (SELECT a, 22)
+1 0 0
+2 0 0
+11 0 0
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a;
+a ROW(11, 12) = (SELECT a, 12) ROW(11, 12) IN (SELECT a, 12)
+1 0 0
+2 0 0
+11 1 1
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1;
+a ROW(11, 12) = (SELECT a, 22) ROW(11, 12) IN (SELECT a, 22)
+1 0 0
+2 0 0
+11 0 0
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1;
+a ROW(11, 12) = (SELECT a, 12) ROW(11, 12) IN (SELECT a, 12)
+1 0 0
+2 0 0
+11 1 1
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1;
+x ROW(11, 12) = (SELECT MAX(x), 22) ROW(11, 12) IN (SELECT MAX(x), 22)
+1 0 0
+2 0 0
+11 0 0
+# 2nd and 3rd columns should be same for x == 11 only
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
+x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12)
+1 0 1
+2 0 1
+11 1 1
+DROP TABLE t1;
+# both columns should be same
+SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL);
+ROW(1,2) = (SELECT NULL, NULL) ROW(1,2) IN (SELECT NULL, NULL)
+NULL NULL
+SELECT ROW(1,2) = (SELECT 1, NULL), ROW(1,2) IN (SELECT 1, NULL);
+ROW(1,2) = (SELECT 1, NULL) ROW(1,2) IN (SELECT 1, NULL)
+NULL NULL
+SELECT ROW(1,2) = (SELECT NULL, 2), ROW(1,2) IN (SELECT NULL, 2);
+ROW(1,2) = (SELECT NULL, 2) ROW(1,2) IN (SELECT NULL, 2)
+NULL NULL
+SELECT ROW(1,2) = (SELECT NULL, 1), ROW(1,2) IN (SELECT NULL, 1);
+ROW(1,2) = (SELECT NULL, 1) ROW(1,2) IN (SELECT NULL, 1)
+0 0
+SELECT ROW(1,2) = (SELECT 1, 1), ROW(1,2) IN (SELECT 1, 1);
+ROW(1,2) = (SELECT 1, 1) ROW(1,2) IN (SELECT 1, 1)
+0 0
+SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2);
+ROW(1,2) = (SELECT 1, 2) ROW(1,2) IN (SELECT 1, 2)
+1 1
End of 5.0 tests
#
# BUG#36896: Server crash on SELECT FROM DUAL
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result 2008-12-28 15:00:21 +0000
+++ b/mysql-test/r/subselect3_jcl6.result 2009-01-28 18:53:58 +0000
@@ -808,6 +808,60 @@ WHERE INNR.varchar_key > 'n{'
);
varchar_nokey
DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (11);
+# 2nd and 3rd columns should be same
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a;
+a ROW(11, 12) = (SELECT a, 22) ROW(11, 12) IN (SELECT a, 22)
+1 0 0
+2 0 0
+11 0 0
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a;
+a ROW(11, 12) = (SELECT a, 12) ROW(11, 12) IN (SELECT a, 12)
+1 0 0
+2 0 0
+11 1 1
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1;
+a ROW(11, 12) = (SELECT a, 22) ROW(11, 12) IN (SELECT a, 22)
+1 0 0
+2 0 0
+11 0 0
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1;
+a ROW(11, 12) = (SELECT a, 12) ROW(11, 12) IN (SELECT a, 12)
+1 0 0
+2 0 0
+11 1 1
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1;
+x ROW(11, 12) = (SELECT MAX(x), 22) ROW(11, 12) IN (SELECT MAX(x), 22)
+1 0 0
+2 0 0
+11 0 0
+# 2nd and 3rd columns should be same for x == 11 only
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
+x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12)
+1 0 1
+2 0 1
+11 1 1
+DROP TABLE t1;
+# both columns should be same
+SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL);
+ROW(1,2) = (SELECT NULL, NULL) ROW(1,2) IN (SELECT NULL, NULL)
+NULL NULL
+SELECT ROW(1,2) = (SELECT 1, NULL), ROW(1,2) IN (SELECT 1, NULL);
+ROW(1,2) = (SELECT 1, NULL) ROW(1,2) IN (SELECT 1, NULL)
+NULL NULL
+SELECT ROW(1,2) = (SELECT NULL, 2), ROW(1,2) IN (SELECT NULL, 2);
+ROW(1,2) = (SELECT NULL, 2) ROW(1,2) IN (SELECT NULL, 2)
+NULL NULL
+SELECT ROW(1,2) = (SELECT NULL, 1), ROW(1,2) IN (SELECT NULL, 1);
+ROW(1,2) = (SELECT NULL, 1) ROW(1,2) IN (SELECT NULL, 1)
+0 0
+SELECT ROW(1,2) = (SELECT 1, 1), ROW(1,2) IN (SELECT 1, 1);
+ROW(1,2) = (SELECT 1, 1) ROW(1,2) IN (SELECT 1, 1)
+0 0
+SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2);
+ROW(1,2) = (SELECT 1, 2) ROW(1,2) IN (SELECT 1, 2)
+1 1
End of 5.0 tests
#
# BUG#36896: Server crash on SELECT FROM DUAL
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2008-12-29 16:40:21 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2009-01-29 09:11:36 +0000
@@ -4663,7 +4663,6 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
a incorrect
1 1
DROP TABLE t1,t2,t3;
-End of 5.1 tests.
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
@@ -4925,6 +4924,43 @@ ERROR 42000: You have an error in your S
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
DROP TABLE t1, t2;
+#
+# BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
+#
+create table t1(id integer primary key, g integer, v integer, s char(1));
+create table t2(id integer primary key, g integer, v integer, s char(1));
+insert into t1 values
+(10, 10, 10, 'l'),
+(20, 20, 20, 'l'),
+(40, 40, 40, 'l'),
+(41, 40, null, 'l'),
+(50, 50, 50, 'l'),
+(51, 50, null, 'l'),
+(60, 60, 60, 'l'),
+(61, 60, null, 'l'),
+(70, 70, 70, 'l'),
+(90, 90, null, 'l');
+insert into t2 values
+(10, 10, 10, 'r'),
+(30, 30, 30, 'r'),
+(50, 50, 50, 'r'),
+(60, 60, 60, 'r'),
+(61, 60, null, 'r'),
+(70, 70, 70, 'r'),
+(71, 70, null, 'r'),
+(80, 80, 80, 'r'),
+(81, 80, null, 'r'),
+(100,100,null, 'r');
+select *
+from t1
+where v in(select v
+from t2
+where t1.g=t2.g) is unknown;
+id g v s
+51 50 NULL l
+61 60 NULL l
+drop table t1, t2;
+End of 5.1 tests.
set optimizer_switch='';
show variables like 'optimizer_switch';
Variable_name Value
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2008-12-29 16:40:21 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2009-01-29 13:55:56 +0000
@@ -4663,7 +4663,6 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
a incorrect
1 1
DROP TABLE t1,t2,t3;
-End of 5.1 tests.
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
@@ -4925,6 +4924,43 @@ ERROR 42000: You have an error in your S
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
DROP TABLE t1, t2;
+#
+# BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
+#
+create table t1(id integer primary key, g integer, v integer, s char(1));
+create table t2(id integer primary key, g integer, v integer, s char(1));
+insert into t1 values
+(10, 10, 10, 'l'),
+(20, 20, 20, 'l'),
+(40, 40, 40, 'l'),
+(41, 40, null, 'l'),
+(50, 50, 50, 'l'),
+(51, 50, null, 'l'),
+(60, 60, 60, 'l'),
+(61, 60, null, 'l'),
+(70, 70, 70, 'l'),
+(90, 90, null, 'l');
+insert into t2 values
+(10, 10, 10, 'r'),
+(30, 30, 30, 'r'),
+(50, 50, 50, 'r'),
+(60, 60, 60, 'r'),
+(61, 60, null, 'r'),
+(70, 70, 70, 'r'),
+(71, 70, null, 'r'),
+(80, 80, 80, 'r'),
+(81, 80, null, 'r'),
+(100,100,null, 'r');
+select *
+from t1
+where v in(select v
+from t2
+where t1.g=t2.g) is unknown;
+id g v s
+51 50 NULL l
+61 60 NULL l
+drop table t1, t2;
+End of 5.1 tests.
set optimizer_switch='';
show variables like 'optimizer_switch';
Variable_name Value
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2008-12-29 16:40:21 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2009-01-29 09:11:36 +0000
@@ -4663,7 +4663,6 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
a incorrect
1 1
DROP TABLE t1,t2,t3;
-End of 5.1 tests.
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
@@ -4925,6 +4924,43 @@ ERROR 42000: You have an error in your S
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
DROP TABLE t1, t2;
+#
+# BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
+#
+create table t1(id integer primary key, g integer, v integer, s char(1));
+create table t2(id integer primary key, g integer, v integer, s char(1));
+insert into t1 values
+(10, 10, 10, 'l'),
+(20, 20, 20, 'l'),
+(40, 40, 40, 'l'),
+(41, 40, null, 'l'),
+(50, 50, 50, 'l'),
+(51, 50, null, 'l'),
+(60, 60, 60, 'l'),
+(61, 60, null, 'l'),
+(70, 70, 70, 'l'),
+(90, 90, null, 'l');
+insert into t2 values
+(10, 10, 10, 'r'),
+(30, 30, 30, 'r'),
+(50, 50, 50, 'r'),
+(60, 60, 60, 'r'),
+(61, 60, null, 'r'),
+(70, 70, 70, 'r'),
+(71, 70, null, 'r'),
+(80, 80, 80, 'r'),
+(81, 80, null, 'r'),
+(100,100,null, 'r');
+select *
+from t1
+where v in(select v
+from t2
+where t1.g=t2.g) is unknown;
+id g v s
+51 50 NULL l
+61 60 NULL l
+drop table t1, t2;
+End of 5.1 tests.
set optimizer_switch='';
show variables like 'optimizer_switch';
Variable_name Value
=== added file 'mysql-test/suite/binlog/r/binlog_tmp_table.result'
--- a/mysql-test/suite/binlog/r/binlog_tmp_table.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/r/binlog_tmp_table.result 2009-01-28 14:35:12 +0000
@@ -0,0 +1,30 @@
+create table foo (a int);
+flush logs;
+create temporary table tmp1_foo like foo;
+create temporary table tmp2_foo (a int);
+insert into tmp1_foo values (1), (2), (3), (4);
+replace into tmp2_foo values (1), (2), (3), (4);
+update tmp1_foo set a=2*a-1;
+update tmp2_foo set a=2*a;
+delete from tmp1_foo where a < 5;
+delete from tmp2_foo where a < 5;
+insert into foo select * from tmp1_foo;
+insert into foo select * from tmp2_foo;
+truncate table tmp1_foo;
+truncate table tmp2_foo;
+flush logs;
+select * from foo;
+a
+5
+7
+6
+8
+drop table foo;
+create table foo (a int);
+select * from foo;
+a
+5
+7
+6
+8
+drop table foo;
=== added file 'mysql-test/suite/binlog/t/binlog_tmp_table.test'
--- a/mysql-test/suite/binlog/t/binlog_tmp_table.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_tmp_table.test 2009-01-28 14:35:12 +0000
@@ -0,0 +1,82 @@
+# ==== Purpose ====
+#
+# Test if statements used temporary tables are binlogged correctly
+#
+# ==== Method ====
+#
+# Use two connections, use temporary tables on both of them, and by
+# switching connections between statements, the test can check if the
+# statements are logged with the correct thread id.
+#
+# The statements current tested include:
+# CREATE TEMPORARY TABLE
+# CREATE TEMPORARY TABLE LIKE
+# INSERT
+# REPLACE
+# UPDATE
+# INSERT SELECT
+# TRUNCATE
+#
+# Note: When adding new query statements, please add them between the
+# two 'flush logs'. And aslo please make sure the connection is
+# switched between each statement.
+#
+# ==== Related bugs ====
+#
+# BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
+#
+source include/have_log_bin.inc;
+source include/have_binlog_format_mixed_or_statement.inc;
+
+connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
+connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,);
+
+create table foo (a int);
+
+flush logs;
+
+connection master;
+create temporary table tmp1_foo like foo;
+connection master1;
+create temporary table tmp2_foo (a int);
+
+connection master;
+insert into tmp1_foo values (1), (2), (3), (4);
+connection master1;
+replace into tmp2_foo values (1), (2), (3), (4);
+
+connection master;
+update tmp1_foo set a=2*a-1;
+connection master1;
+update tmp2_foo set a=2*a;
+
+connection master;
+delete from tmp1_foo where a < 5;
+connection master1;
+delete from tmp2_foo where a < 5;
+
+connection master;
+insert into foo select * from tmp1_foo;
+connection master1;
+insert into foo select * from tmp2_foo;
+
+connection master;
+truncate table tmp1_foo;
+connection master1;
+truncate table tmp2_foo;
+
+flush logs;
+
+connection default;
+select * from foo;
+
+# prepare for the replay
+drop table foo;
+create table foo (a int);
+
+# replay from binary log
+exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/log/master-bin.000002 | $MYSQL;
+select * from foo;
+
+# clean up
+drop table foo;
=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test 2009-01-16 14:28:04 +0000
+++ b/mysql-test/t/subselect.test 2009-01-28 19:58:23 +0000
@@ -3503,12 +3503,8 @@ INSERT INTO t3 VALUES (1,1,1), (2,32,1);
explain
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
-
DROP TABLE t1,t2,t3;
---echo End of 5.1 tests.
-
-
#
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
#
@@ -3733,5 +3729,41 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 U
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
--error ER_PARSE_ERROR
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
-
DROP TABLE t1, t2;
+
+--echo #
+--echo # BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
+--echo #
+create table t1(id integer primary key, g integer, v integer, s char(1));
+create table t2(id integer primary key, g integer, v integer, s char(1));
+insert into t1 values
+ (10, 10, 10, 'l'),
+ (20, 20, 20, 'l'),
+ (40, 40, 40, 'l'),
+ (41, 40, null, 'l'),
+ (50, 50, 50, 'l'),
+ (51, 50, null, 'l'),
+ (60, 60, 60, 'l'),
+ (61, 60, null, 'l'),
+ (70, 70, 70, 'l'),
+ (90, 90, null, 'l');
+insert into t2 values
+ (10, 10, 10, 'r'),
+ (30, 30, 30, 'r'),
+ (50, 50, 50, 'r'),
+ (60, 60, 60, 'r'),
+ (61, 60, null, 'r'),
+ (70, 70, 70, 'r'),
+ (71, 70, null, 'r'),
+ (80, 80, 80, 'r'),
+ (81, 80, null, 'r'),
+ (100,100,null, 'r');
+
+select *
+from t1
+where v in(select v
+ from t2
+ where t1.g=t2.g) is unknown;
+drop table t1, t2;
+
+--echo End of 5.1 tests.
=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test 2008-12-28 15:00:21 +0000
+++ b/mysql-test/t/subselect3.test 2009-01-28 18:53:58 +0000
@@ -654,6 +654,35 @@ WHERE NULL NOT IN (
DROP TABLE t1;
+#
+# Bug #39069: <row constructor> IN <table-subquery> seriously messed up
+#
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (11);
+
+--echo # 2nd and 3rd columns should be same
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a;
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a;
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1;
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1;
+
+# The x alias is used below to workaround bug #40674.
+# Regression tests for sum function on outer column in subselect from dual:
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1;
+--echo # 2nd and 3rd columns should be same for x == 11 only
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
+
+DROP TABLE t1;
+
+--echo # both columns should be same
+SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL);
+SELECT ROW(1,2) = (SELECT 1, NULL), ROW(1,2) IN (SELECT 1, NULL);
+SELECT ROW(1,2) = (SELECT NULL, 2), ROW(1,2) IN (SELECT NULL, 2);
+SELECT ROW(1,2) = (SELECT NULL, 1), ROW(1,2) IN (SELECT NULL, 1);
+SELECT ROW(1,2) = (SELECT 1, 1), ROW(1,2) IN (SELECT 1, 1);
+SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2);
+
--echo End of 5.0 tests
--echo #
=== modified file 'mysql-test/t/user_limits.test'
--- a/mysql-test/t/user_limits.test 2005-04-04 19:43:58 +0000
+++ b/mysql-test/t/user_limits.test 2009-01-28 10:53:43 +0000
@@ -5,6 +5,23 @@
# Requires privileges to be enabled
-- source include/not_embedded.inc
+# <-- Start the cut here
+# As long as
+# Bug#42384 thread_handling = pool-of-threads, wrong handling of max_questions
+# user limit
+# is not fixed this test will fail when the server was started with
+# "pool-of-threads".
+# Please set $fixed_bug_42384 to 1 when checking if the fix for Bug#42384 works.
+# If everything is fine, please remove the lines between the markers.
+let $fixed_bug_42384 = 0;
+let $my_val= query_get_value(show variables like 'thread_handling', Value, 1);
+if (`SELECT $fixed_bug_42384 = 0 AND '$my_val' = 'pool-of-threads'`)
+{
+ --skip Bug#42384 thread_handling = pool-of-threads, wrong handling of max_questions user limit
+ exit;
+}
+# <-- End the cut here
+
# Prepare play-ground
--disable_warnings
drop table if exists t1;
@@ -27,12 +44,14 @@ flush user_resources;
connect (mqph, localhost, mysqltest_1,,);
connection mqph;
select * from t1;
+# Bug#42384 thread_handling = pool-of-threads, wrong handling of max_questions
+# user limit
select * from t1;
---error 1226
+--error ER_USER_LIMIT_REACHED
select * from t1;
connect (mqph2, localhost, mysqltest_1,,);
connection mqph2;
---error 1226
+--error ER_USER_LIMIT_REACHED
select * from t1;
# cleanup
connection default;
@@ -50,12 +69,12 @@ select * from t1;
select * from t1;
delete from t1;
delete from t1;
---error 1226
+--error ER_USER_LIMIT_REACHED
delete from t1;
select * from t1;
connect (muph2, localhost, mysqltest_1,,);
connection muph2;
---error 1226
+--error ER_USER_LIMIT_REACHED
delete from t1;
select * from t1;
# Cleanup
@@ -74,7 +93,7 @@ connect (mcph2, localhost, mysqltest_1,,
connection mcph2;
select * from t1;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
---error 1226
+--error ER_USER_LIMIT_REACHED
connect (mcph3, localhost, mysqltest_1,,);
# Old connection is still ok
select * from t1;
@@ -83,7 +102,7 @@ select * from t1;
disconnect mcph1;
disconnect mcph2;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
---error 1226
+--error ER_USER_LIMIT_REACHED
connect (mcph3, localhost, mysqltest_1,,);
# Cleanup
connection default;
@@ -101,7 +120,7 @@ connect (muc2, localhost, mysqltest_1,,)
connection muc2;
select * from t1;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
---error 1226
+--error ER_USER_LIMIT_REACHED
connect (muc3, localhost, mysqltest_1,,);
# Closing of one of connections should help
disconnect muc1;
@@ -115,7 +134,7 @@ connect (muc4, localhost, mysqltest_1,,)
connection muc4;
select * from t1;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
---error 1226
+--error ER_USER_LIMIT_REACHED
connect (muc5, localhost, mysqltest_1,,);
# Clean up
connection default;
@@ -154,7 +173,7 @@ connect (muca3, localhost, mysqltest_1,,
connection muca3;
select @@session.max_user_connections, @@global.max_user_connections;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
---error 1226
+--error ER_USER_LIMIT_REACHED
connect (muca4, localhost, mysqltest_1,,);
# Cleanup
connection default;
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2009-01-16 11:53:32 +0000
+++ b/sql/item_cmpfunc.cc 2009-01-28 19:58:23 +0000
@@ -1622,8 +1622,8 @@ void Item_in_optimizer::cleanup()
bool Item_in_optimizer::is_null()
{
- cache->store(args[0]);
- return (null_value= (cache->null_value || args[1]->is_null()));
+ val_int();
+ return null_value;
}
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc 2009-01-16 14:28:04 +0000
+++ b/sql/sql_delete.cc 2009-01-28 17:30:19 +0000
@@ -1045,6 +1045,9 @@ bool mysql_truncate(THD *thd, TABLE_LIST
share->table_name.str, 1,
OTM_OPEN))))
(void) rm_temporary_table(table_type, path, frm_only);
+ else
+ thd->thread_specific_used= TRUE;
+
free_table_share(share);
my_free((char*) table,MYF(0));
/*
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-01-16 14:28:04 +0000
+++ b/sql/sql_select.cc 2009-01-28 18:53:58 +0000
@@ -2339,8 +2339,13 @@ JOIN::exec()
We have to test for 'conds' here as the WHERE may not be constant
even if we don't have any tables for prepared statements or if
conds uses something like 'rand()'.
+ If the HAVING clause is either impossible or always true, then
+ JOIN::having is set to NULL by optimize_cond.
+ In this case JOIN::exec must check for JOIN::having_value, in the
+ same way it checks for JOIN::cond_value.
*/
if (cond_value != Item::COND_FALSE &&
+ having_value != Item::COND_FALSE &&
(!conds || conds->val_int()) &&
(!having || having->val_int()))
{
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2009-01-16 14:28:04 +0000
+++ b/sql/sql_table.cc 2009-01-28 17:30:19 +0000
@@ -5015,6 +5015,7 @@ bool mysql_create_like_table(THD* thd, T
dst_path, false); /* purecov: inspected */
goto err; /* purecov: inspected */
}
+ thd->thread_specific_used= TRUE;
}
else if (err)
{
| Thread |
|---|
| • bzr commit into mysql-6.0-bugteam branch (davi:2987) | Davi Arnaut | 29 Jan |
