merge with 5.1

16 files changed, 356 insertions, 31 deletions

diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test
index b31fa83588b..4e337d54660 100644
--- a/mysql-test/t/csv.test
+++ b/mysql-test/t/csv.test
@@ -1553,26 +1553,25 @@ drop table t1;
 # whole alter table code is being tested all around the test suite already.
 #
 
-create table t1 (v varchar(32) not null);
+create table t1 (v varchar(32) not null) engine=csv;
 insert into t1 values ('def'),('abc'),('hij'),('3r4f');
 select * from t1;
 # Fast alter, no copy performed
-alter table t1 change v v2 varchar(32);
+alter table t1 change v v2 varchar(32) not null;
 select * from t1;
 # Fast alter, no copy performed
-alter table t1 change v2 v varchar(64);
+alter table t1 change v2 v varchar(64) not null;
 select * from t1;
 update t1 set v = 'lmn' where v = 'hij';
 select * from t1;
 # Regular alter table
-alter table t1 add i int auto_increment not null primary key first;
+alter table t1 add i int not null first;
 select * from t1;
-update t1 set i=5 where i=3;
+update t1 set i=3 where v = 'abc';
 select * from t1;
-alter table t1 change i i bigint;
+alter table t1 change i i bigint not null;
 select * from t1;
-alter table t1 add unique key (i, v);
-select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
+select * from t1 where i between 2 and 4 and v in ('def','3r4f','abc');
 drop table t1;
 
 #
diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test
index 5a0e86568f3..ea5c87babbb 100644
--- a/mysql-test/t/delete.test
+++ b/mysql-test/t/delete.test
@@ -387,4 +387,17 @@ DELETE FROM t1 WHERE a = 10 OR b = 20 ORDER BY c LIMIT 1;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug #53034: Multiple-table DELETE statements not accepting
+--echo #             "Access compatibility" syntax
+--echo #
+
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 LIKE t1;
+CREATE TABLE t3 LIKE t1;
+
+DELETE FROM t1.*, test.t2.*, a.* USING t1, t2, t3 AS a;
+
+DROP TABLE t1, t2, t3;
+
 --echo End of 5.1 tests
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 6dbc8a05789..72a78f612a2 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -1082,6 +1082,20 @@ select a.f1 as a, b.f4 as b, a.f1 > b.f4 as gt,
 from t1 a, t1 b;
 select *, f1 = f2 from t1;
 drop table t1;
+
+--echo #
+--echo # Bug #54465: assert: field_types == 0 || field_types[field_pos] ==
+--echo #             MYSQL_TYPE_LONGLONG
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2);
+
+SELECT MAX((SELECT 1 FROM t1 ORDER BY @var LIMIT 1)) m FROM t1 t2, t1 
+       ORDER BY t1.a;
+
+DROP TABLE t1;
+
 --echo #
 --echo End of 5.1 tests
 
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 2f9a48ffd6a..57eee43d75e 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -838,4 +838,15 @@ select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);
 select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
 
 
+#
+# Bug #55565: debug assertion when ordering by expressions with user
+# variable assignments
+#
+
+CREATE TABLE t1(a DOUBLE NOT NULL);
+INSERT INTO t1 VALUES (0),(9.216e-096);
+--echo # should not crash
+SELECT 1 FROM t1 ORDER BY @x:=makedate(a,a);
+DROP TABLE t1;
+
 --echo End of 5.1 tests
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index bc0695aaa93..fd0a18ab4dd 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -401,6 +401,7 @@ create table t1 (a int, b blob);
 insert into t1 values (1, ''), (2, NULL), (3, '1');
 select * from t1;
 
+--error ER_ILLEGAL_VALUE_FOR_TYPE
 select
   geometryfromtext(b) IS NULL, geometryfromwkb(b) IS NULL, astext(b) IS NULL, 
   aswkb(b) IS NULL, geometrytype(b) IS NULL, centroid(b) IS NULL,
@@ -419,6 +420,7 @@ select
   intersects(b, b) IS NULL, crosses(b, b) IS NULL
 from t1;
 
+--error ER_ILLEGAL_VALUE_FOR_TYPE
 select 
   point(b, b) IS NULL, linestring(b) IS NULL, polygon(b) IS NULL, multipoint(b) IS NULL, 
   multilinestring(b) IS NULL, multipolygon(b) IS NULL, 
@@ -702,24 +704,44 @@ drop table t1;
 # Bug#44684: valgrind reports invalid reads in 
 # Item_func_spatial_collection::val_str
 #
+--error ER_ILLEGAL_VALUE_FOR_TYPE
 SELECT MultiPoint(12345,'');
-SELECT MultiPoint(123451,'');
-SELECT MultiPoint(1234512,'');
-SELECT MultiPoint(12345123,'');
-
-SELECT MultiLineString(12345,'');
-SELECT MultiLineString(123451,'');
-SELECT MultiLineString(1234512,'');
-SELECT MultiLineString(12345123,'');
-
-SELECT LineString(12345,'');
-SELECT LineString(123451,'');
-SELECT LineString(1234512,'');
-SELECT LineString(12345123,'');
-
-SELECT Polygon(12345,'');
-SELECT Polygon(123451,'');
-SELECT Polygon(1234512,'');
-SELECT Polygon(12345123,'');
+#SELECT MultiPoint(123451,'');
+#SELECT MultiPoint(1234512,'');
+#SELECT MultiPoint(12345123,'');
+
+--error ER_ILLEGAL_VALUE_FOR_TYPE
+#SELECT MultiLineString(12345,'');
+#SELECT MultiLineString(123451,'');
+#SELECT MultiLineString(1234512,'');
+#SELECT MultiLineString(12345123,'');
+
+--error ER_ILLEGAL_VALUE_FOR_TYPE
+#SELECT LineString(12345,'');
+#SELECT LineString(123451,'');
+#SELECT LineString(1234512,'');
+#SELECT LineString(12345123,'');
+
+--error ER_ILLEGAL_VALUE_FOR_TYPE
+#SELECT Polygon(12345,'');
+#SELECT Polygon(123451,'');
+#SELECT Polygon(1234512,'');
+#SELECT Polygon(12345123,'');
+
+#
+# Bug55531 crash with conversions of geometry types / strings
+#
+--error ER_ILLEGAL_VALUE_FOR_TYPE
+SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b));
+
+
+--echo #
+--echo # BUG#51875: crash when loading data into geometry function polyfromwkb
+--echo #
+SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
+SET @a=POLYFROMWKB(@a);
+SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
+SET @a=POLYFROMWKB(@a);
+
 
 --echo End of 5.1 tests
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index 5ec0aefe021..045b5807391 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -504,3 +504,28 @@ DROP TABLE t1, t2;
 
 --echo End of 5.0 tests.
 
+
+--echo #
+--echo # Bug#54106 assert in Protocol::end_statement,
+--echo #           INSERT IGNORE ... SELECT ... UNION SELECT ...
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a INT);
+
+--error ER_FIELD_SPECIFIED_TWICE
+INSERT INTO t1 (a, a) VALUES (1, 1);
+# Verify that ER_FIELD_SPECIFIED_TWICE is not ignorable
+--error ER_FIELD_SPECIFIED_TWICE
+INSERT IGNORE t1 (a, a) VALUES (1, 1);
+
+--error ER_FIELD_SPECIFIED_TWICE
+INSERT IGNORE t1 (a, a) SELECT 1,1;
+# Used to cause an assert
+--error ER_FIELD_SPECIFIED_TWICE
+INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 43b373c9703..05d630edfb2 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -851,4 +851,74 @@ ENGINE=MERGE  UNION=(t1,t2);
 SELECT t1.a FROM mm1,t1;
 DROP TABLE t1, t2, mm1;
 
+#--echo #
+#--echo # Bug #55568: user variable assignments crash server when used within
+#--echo #             query
+#--echo #
+#
+#
+# This test case is invalidated because of fix of bug 55531
+# The reason is that {1} is not a valid geometric collection.
+#
+#CREATE TABLE t1 (a INT);
+
+#INSERT INTO t1 VALUES (0), (1);
+
+#let $i=2;
+#while ($i)
+#{
+#  SELECT MULTIPOINT(
+#    1,
+#    (
+#      SELECT MULTIPOINT(
+#        MULTIPOINT(
+#          1,
+#          (SELECT COUNT(*) FROM (SELECT 1 FROM t1 GROUP BY a,a) d)
+#        )
+#      ) FROM t1
+#    )
+#  ) != COUNT(*) q FROM t1 GROUP BY a;
+#  dec $i;
+#}
+#
+#DROP TABLE t1;
+
+--echo #
+--echo # Bug #54468: crash after item's print() function when ordering/grouping 
+--echo #             by subquery
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (), ();
+
+SELECT 1 FROM t1
+GROUP BY
+GREATEST(t1.a,
+         (SELECT 1 FROM
+          (SELECT t1.b FROM t1,t1 t2
+           ORDER BY t1.a, t1.a LIMIT 1) AS d)
+        );
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug #53544: Server hangs during JOIN query in stored procedure called
+--echo #             twice in a row
+--echo #
+
+CREATE TABLE t1(c INT);
+
+INSERT INTO t1 VALUES (1), (2);
+
+PREPARE stmt FROM "SELECT t2.c AS f1 FROM t1 LEFT JOIN
+                                        t1 t2 ON t1.c=t2.c RIGHT JOIN
+                                        t1 t3 ON t1.c=t3.c 
+                   GROUP BY f1;";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1;
+
 --echo End of 5.1 tests
diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test
index 7c9fbf031bb..783708cc3b2 100644
--- a/mysql-test/t/mysqlbinlog.test
+++ b/mysql-test/t/mysqlbinlog.test
@@ -1,6 +1,6 @@
 # We are using .opt file since we need small binlog size
 # TODO: Need to look at making a row based version once the new row based client is completed. [jbm]
--- source include/have_binlog_format_mixed_or_statement.inc
+-- source include/have_binlog_format_statement.inc
 
 -- source include/have_log_bin.inc
 
diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test
index edc4da4e37f..707f6db99c4 100644
--- a/mysql-test/t/mysqltest.test
+++ b/mysql-test/t/mysqltest.test
@@ -334,6 +334,15 @@ eval select $mysql_errno as "after_!errno_masked_error" ;
 --exec echo "disable_abort_on_error; error 1000; select 3 from t1; error 1000; select 3 from t1;" | $MYSQL_TEST  2>&1
 
 # ----------------------------------------------------------------------------
+# Check some non-query statements that would fail
+# ----------------------------------------------------------------------------
+--exec illegal_command
+--cat_file does_not_exist
+--perl
+  exit(1);
+EOF
+
+# ----------------------------------------------------------------------------
 # Switch the abort on error on and check the effect on $mysql_errno
 # ----------------------------------------------------------------------------
 --error ER_PARSE_ERROR
@@ -871,7 +880,7 @@ while ($outer)
 }
 
 # Test source in an if in a while which is false on 1st iteration
-# Also test --error in same context
+# Also test --error and --disable_abort_on_error in same context
 let $outer= 2; # Number of outer loops
 let $ifval= 0; # false 1st time
 while ($outer)
@@ -882,6 +891,10 @@ while ($outer)
     --source $MYSQLTEST_VARDIR/tmp/sourced.inc
     --error ER_NO_SUCH_TABLE
     SELECT * from nowhere;
+    --disable_abort_on_error
+# Statement giving a different error, to make sure we don't mask it
+    SELECT * FROM nowhere else;
+    --enable_abort_on_error
   }
   dec $outer;
   inc $ifval;
@@ -1101,6 +1114,36 @@ if (!$counter)
 }
 
 # ----------------------------------------------------------------------------
+# Test if with some non-numerics
+# ----------------------------------------------------------------------------
+
+let $counter=alpha;
+if ($counter)
+{
+  echo Counter is true, (counter=alpha);
+}
+let $counter=   ;
+if ($counter)
+{
+  echo oops, space is true;
+}
+let $counter=-0;
+if ($counter)
+{
+  echo oops, -0 is true;
+}
+if (beta)
+{
+  echo Beta is true;
+}
+let $counter=gamma;
+while ($counter)
+{
+  echo while with string, only once;
+  let $counter=000;
+}
+
+# ----------------------------------------------------------------------------
 # Test while, { and }
 # ----------------------------------------------------------------------------
 
@@ -1445,7 +1488,6 @@ EOF
 remove_file $MYSQLTEST_VARDIR/tmp/mysqltest.sql;
 
 # connect when "disable_abort_on_error" caused "connection not found"
---replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
 --disable_abort_on_error
 connect (con1,localhost,root,,);
 connection default;
@@ -1734,7 +1776,16 @@ select 1;
 --reap
 EOF
 --error 1
---exec $MYSQL_TEST < $MYSQLTEST_VARDIR/tmp/mysqltest.in 2>&1
+# Must filter unpredictable extra warning from output
+--exec $MYSQL_TEST < $MYSQLTEST_VARDIR/tmp/mysqltest.in > $MYSQL_TMP_DIR/mysqltest.out 2>&1
+--perl
+  my $dir= $ENV{'MYSQL_TMP_DIR'};
+  open (FILE, "$dir/mysqltest.out");
+  while (<FILE>) {
+    print unless /Note: net_clear/; # This shows up on rare occations
+  }
+EOF
+remove_file $MYSQL_TMP_DIR/mysqltest.out;
 remove_file $MYSQLTEST_VARDIR/tmp/mysqltest.in;
 
 drop table t1;
diff --git a/mysql-test/t/partition_not_blackhole-master.opt b/mysql-test/t/partition_not_blackhole-master.opt
new file mode 100644
index 00000000000..1e47be930bc
--- /dev/null
+++ b/mysql-test/t/partition_not_blackhole-master.opt
@@ -0,0 +1 @@
+--loose-skip-blackhole
diff --git a/mysql-test/t/partition_not_blackhole.test b/mysql-test/t/partition_not_blackhole.test
new file mode 100644
index 00000000000..7352aeaa230
--- /dev/null
+++ b/mysql-test/t/partition_not_blackhole.test
@@ -0,0 +1,26 @@
+--source include/have_partition.inc
+--source include/not_blackhole.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+let $MYSQLD_DATADIR= `SELECT @@datadir`;
+
+--echo #
+--echo # Bug#46086: crash when dropping a partitioned table and
+--echo #            the original engine is disabled
+--echo # Copy a .frm and .par file which was created with:
+--echo # create table `t1` (`id` int primary key) engine=blackhole
+--echo # partition by key () partitions 1;
+--copy_file std_data/parts/t1_blackhole.frm $MYSQLD_DATADIR/test/t1.frm
+--copy_file std_data/parts/t1_blackhole.par $MYSQLD_DATADIR/test/t1.par
+SHOW TABLES;
+--replace_result $MYSQLD_DATADIR ./
+--error ER_NOT_FORM_FILE
+SHOW CREATE TABLE t1;
+--error ER_BAD_TABLE_ERROR
+DROP TABLE t1;
+--list_files $MYSQLD_DATADIR/test t1*
+--remove_file $MYSQLD_DATADIR/test/t1.frm
+--remove_file $MYSQLD_DATADIR/test/t1.par
diff --git a/mysql-test/t/partition_range.test b/mysql-test/t/partition_range.test
index 4d011ddc468..f386fac4998 100644
--- a/mysql-test/t/partition_range.test
+++ b/mysql-test/t/partition_range.test
@@ -846,3 +846,44 @@ insert into t2 values(52, 20070322, 456, 'filler') ;
 select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid;
 drop table t1, t2;
 
+
+--echo #
+--echo # Bug#50939: Loose Index Scan unduly relies on engine to remember range 
+--echo # endpoints
+--echo #
+CREATE TABLE t1 (
+ a INT,
+ b INT,
+ KEY ( a, b )
+) PARTITION BY HASH (a) PARTITIONS 1;
+
+CREATE TABLE t2 (
+ a INT,
+ b INT,
+ KEY ( a, b )
+);
+
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+
+INSERT INTO t1 SELECT a +  5, b +  5 FROM t1;
+INSERT INTO t1 SELECT a + 10, b + 10 FROM t1;
+INSERT INTO t1 SELECT a + 20, b + 20 FROM t1;
+INSERT INTO t1 SELECT a + 40, b + 40 FROM t1;
+
+INSERT INTO t2 SELECT * FROM t1;
+
+--echo # plans should be identical
+EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a;
+EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a;
+
+FLUSH status;
+SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a;
+--echo # Should be no more than 4 reads.
+SHOW status LIKE 'handler_read_key';
+
+FLUSH status;
+SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a;
+--echo # Should be no more than 4 reads.
+SHOW status LIKE 'handler_read_key';
+
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 5d5ad180f1a..0ad3d3e8504 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1313,4 +1313,16 @@ SELECT * FROM t1 FORCE INDEX (PRIMARY)
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug #54802: 'NOT BETWEEN' evaluation is incorrect
+--echo #
+
+CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key));
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+
+EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
+SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
+
+DROP TABLE t1;
+
 --echo End of 5.1 tests
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index c287091ab54..304714b83ae 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -87,4 +87,17 @@ SELECT t1.col_int_nokey,(SELECT MIN( t2_a.col_int_key ) +1 FROM t2 t2_a, t2 t2_b
 
 DROP TABLE t1,t2;
 
---echo End of 5.1 tests.
+--echo #
+--echo # Bug#54568: create view cause Assertion failed: 0, 
+--echo # file .\item_subselect.cc, line 836
+--echo #
+EXPLAIN SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
+DESCRIBE SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
+--echo # None of the below should crash
+CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
+CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
+DROP VIEW v1, v2;
+
+--echo #
+--echo # End of 5.1 tests.
+--echo #
diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test
index 15ddceb8d68..c4445da107c 100644
--- a/mysql-test/t/timezone2.test
+++ b/mysql-test/t/timezone2.test
@@ -273,5 +273,14 @@ UNLOCK TABLES;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug #55424: convert_tz crashes when fed invalid data
+--echo #
+
+CREATE TABLE t1 (a SET('x') NOT NULL);
+INSERT INTO t1 VALUES ('');
+SELECT CONVERT_TZ(1, a, 1) FROM t1;
+SELECT CONVERT_TZ(1, 1, a) FROM t1;
+DROP TABLE t1;
 
 --echo End of 5.1 tests
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index b6b58e7bd46..4f27866de23 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -328,4 +328,22 @@ INSERT INTO t1 VALUES (1);
 INSERT INTO t1 VALUES (1);
 DROP TABLE t1;
 
+#
+# Bug #55615: debug assertion after using variable in assignment and
+# referred to
+# Bug #55564: crash with user variables, assignments, joins...
+#
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0),(0);
+--echo # BUG#55615 : should not crash
+SELECT (@a:=(SELECT @a:=1 FROM t1 LIMIT 1)) AND COUNT(1) FROM t1 GROUP BY @a;
+--echo # BUG#55564 : should not crash
+SELECT IF(
+  @v:=LEAST((SELECT 1 FROM t1 t2 LEFT JOIN t1 ON (@v) GROUP BY t1.a), a),
+  count(*), 1) 
+FROM t1 GROUP BY a LIMIT 1;
+
+DROP TABLE t1;
+
 --echo End of 5.1 tests