summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/almost_full.result29
-rw-r--r--mysql-test/r/analyze.result8
-rw-r--r--mysql-test/r/archive.result2
-rw-r--r--mysql-test/r/bdb_gis.result39
-rw-r--r--mysql-test/r/bigint.result6
-rw-r--r--mysql-test/r/binlog_killed.result44
-rw-r--r--mysql-test/r/binlog_killed_simulate.result33
-rw-r--r--mysql-test/r/cast.result24
-rw-r--r--mysql-test/r/create.result2
-rw-r--r--mysql-test/r/csv.result42
-rw-r--r--mysql-test/r/ctype_euckr.result41
-rw-r--r--mysql-test/r/ctype_uca.result45
-rw-r--r--mysql-test/r/ctype_ucs.result54
-rw-r--r--mysql-test/r/ctype_utf8.result87
-rw-r--r--mysql-test/r/date_formats.result2
-rw-r--r--mysql-test/r/delayed.result16
-rw-r--r--mysql-test/r/delete.result8
-rw-r--r--mysql-test/r/derived.result3
-rw-r--r--mysql-test/r/fulltext.result6
-rw-r--r--mysql-test/r/func_gconcat.result9
-rw-r--r--mysql-test/r/func_group.result30
-rw-r--r--mysql-test/r/func_math.result38
-rw-r--r--mysql-test/r/func_misc.result22
-rw-r--r--mysql-test/r/func_regexp.result19
-rw-r--r--mysql-test/r/func_sapdb.result2
-rw-r--r--mysql-test/r/func_set.result30
-rw-r--r--mysql-test/r/func_str.result30
-rw-r--r--mysql-test/r/func_time.result9
-rw-r--r--mysql-test/r/gis-rtree.result35
-rw-r--r--mysql-test/r/gis.result45
-rw-r--r--mysql-test/r/grant2.result18
-rw-r--r--mysql-test/r/group_by.result65
-rw-r--r--mysql-test/r/information_schema.result36
-rw-r--r--mysql-test/r/information_schema_db.result6
-rw-r--r--mysql-test/r/innodb.result17
-rw-r--r--mysql-test/r/innodb_gis.result39
-rw-r--r--mysql-test/r/insert_select.result12
-rw-r--r--mysql-test/r/lowercase_view.result21
-rw-r--r--mysql-test/r/mix_innodb_myisam_binlog.result119
-rw-r--r--mysql-test/r/multi_update.result24
-rw-r--r--mysql-test/r/myisam.result33
-rw-r--r--mysql-test/r/myisampack.result29
-rw-r--r--mysql-test/r/mysql.result2
-rw-r--r--mysql-test/r/mysql_comments.result50
-rw-r--r--mysql-test/r/mysqlbinlog.result1
-rw-r--r--mysql-test/r/mysqldump.result22
-rw-r--r--mysql-test/r/ndb_condition_pushdown.result22
-rw-r--r--mysql-test/r/ndb_update.result8
-rw-r--r--mysql-test/r/null.result25
-rw-r--r--mysql-test/r/olap.result19
-rw-r--r--mysql-test/r/outfile_loaddata.result85
-rw-r--r--mysql-test/r/ps_2myisam.result3
-rw-r--r--mysql-test/r/ps_3innodb.result3
-rw-r--r--mysql-test/r/ps_4heap.result3
-rw-r--r--mysql-test/r/ps_5merge.result6
-rw-r--r--mysql-test/r/ps_6bdb.result3
-rw-r--r--mysql-test/r/ps_7ndb.result3
-rw-r--r--mysql-test/r/rpl_flush_log_loop.result7
-rw-r--r--mysql-test/r/rpl_packet.result37
-rw-r--r--mysql-test/r/rpl_slave_skip.result144
-rw-r--r--mysql-test/r/rpl_sp_effects.result41
-rw-r--r--mysql-test/r/select.result264
-rw-r--r--mysql-test/r/skip_grants.result5
-rw-r--r--mysql-test/r/sp_trans_log.result4
-rw-r--r--mysql-test/r/subselect.result60
-rw-r--r--mysql-test/r/symlink.result6
-rw-r--r--mysql-test/r/type_date.result79
-rw-r--r--mysql-test/r/type_datetime.result63
-rw-r--r--mysql-test/r/type_decimal.result83
-rw-r--r--mysql-test/r/variables.result19
-rw-r--r--mysql-test/r/view.result56
-rw-r--r--mysql-test/r/view_grant.result51
72 files changed, 2277 insertions, 76 deletions
diff --git a/mysql-test/r/almost_full.result b/mysql-test/r/almost_full.result
new file mode 100644
index 00000000000..eb28f12fa51
--- /dev/null
+++ b/mysql-test/r/almost_full.result
@@ -0,0 +1,29 @@
+drop table if exists t1;
+set global myisam_data_pointer_size=2;
+CREATE TABLE t1 (a int auto_increment primary key not null, b longtext) ENGINE=MyISAM;
+DELETE FROM t1 WHERE a=1 or a=5;
+INSERT INTO t1 SET b=repeat('a',600);
+ERROR HY000: The table 't1' is full
+CHECK TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 check warning Datafile is almost full, 65448 of 65534 used
+test.t1 check status OK
+UPDATE t1 SET b=repeat('a', 800) where a=10;
+ERROR HY000: The table 't1' is full
+CHECK TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 check warning Datafile is almost full, 65448 of 65534 used
+test.t1 check status OK
+INSERT INTO t1 SET b=repeat('a',400);
+CHECK TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 check warning Datafile is almost full, 65448 of 65534 used
+test.t1 check status OK
+DELETE FROM t1 WHERE a=2 or a=6;
+UPDATE t1 SET b=repeat('a', 600) where a=11;
+CHECK TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 check warning Datafile is almost full, 65448 of 65534 used
+test.t1 check status OK
+drop table t1;
+set global myisam_data_pointer_size=default;
diff --git a/mysql-test/r/analyze.result b/mysql-test/r/analyze.result
index 7b476c3cca2..c3dbb846402 100644
--- a/mysql-test/r/analyze.result
+++ b/mysql-test/r/analyze.result
@@ -56,3 +56,11 @@ show index from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
t1 1 a 1 a A 5 NULL NULL YES BTREE
drop table t1;
+End of 4.1 tests
+create table t1(a int);
+analyze table t1 extended;
+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 'extended' at line 1
+optimize table t1 extended;
+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 'extended' at line 1
+drop table t1;
+End of 5.0 tests
diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result
index a0b13b14b17..0fc43f58908 100644
--- a/mysql-test/r/archive.result
+++ b/mysql-test/r/archive.result
@@ -2618,7 +2618,7 @@ auto fld1 companynr fld3 fld4 fld5 fld6
INSERT INTO t2 VALUES (2,011401,37,'breaking','dreaded','Steinberg','W');
INSERT INTO t2 VALUES (3,011402,37,'Romans','scholastics','jarring','');
INSERT INTO t2 VALUES (4,011403,37,'intercepted','audiology','tinily','');
-OPTIMIZE TABLE t2 EXTENDED;
+OPTIMIZE TABLE t2;
Table Op Msg_type Msg_text
test.t2 optimize status OK
SELECT * FROM t2;
diff --git a/mysql-test/r/bdb_gis.result b/mysql-test/r/bdb_gis.result
index d48b5a26e1d..6651421b51c 100644
--- a/mysql-test/r/bdb_gis.result
+++ b/mysql-test/r/bdb_gis.result
@@ -542,3 +542,42 @@ Overlaps(@horiz1, @point2)
0
DROP TABLE t1;
End of 5.0 tests
+CREATE TABLE t1 (p POINT);
+CREATE TABLE t2 (p POINT, INDEX(p));
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const 1 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+EXPLAIN
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL p NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+DROP TABLE t1, t2;
+End of 5.0 tests
diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result
index 064304b27aa..b0d114f1655 100644
--- a/mysql-test/r/bigint.result
+++ b/mysql-test/r/bigint.result
@@ -170,6 +170,12 @@ t2.value64=t1.value64;
value64 value32 value64 value32
9223372036854775807 2 9223372036854775807 4
drop table t1, t2;
+create table t1 (sint64 bigint not null);
+insert into t1 values (-9223372036854775808);
+select * from t1;
+sint64
+-9223372036854775808
+drop table t1;
create table t1 select 1 as 'a';
show create table t1;
Table Create Table
diff --git a/mysql-test/r/binlog_killed.result b/mysql-test/r/binlog_killed.result
index ba4f38fb4c1..e04cc192876 100644
--- a/mysql-test/r/binlog_killed.result
+++ b/mysql-test/r/binlog_killed.result
@@ -9,4 +9,48 @@ insert into t2 values (null, null), (null, get_lock("a", 10));
select @result /* must be zero either way */;
@result
0
+select RELEASE_LOCK("a");
+RELEASE_LOCK("a")
+1
+delete from t1;
+delete from t2;
+insert into t1 values (1,1),(2,2);
+begin;
+update t1 set b=11 where a=2;
+begin;
+update t1 set b=b+10;
+kill query ID;
+rollback;
+rollback;
+select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
+a b
+1 1
+2 2
+begin;
+delete from t1 where a=2;
+begin;
+delete from t1 where a=2;
+kill query ID;
+rollback;
+rollback;
+select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
+a b
+1 1
+2 2
+drop table if exists t4;
+create table t4 (a int, b int) engine=innodb;
+insert into t4 values (3, 3);
+begin;
+insert into t1 values (3, 3);
+begin;
+insert into t1 select * from t4 for update;
+kill query ID;
+rollback;
+rollback;
+select * from t1 /* must be the same as before (1,1),(2,2) */;
+a b
+1 1
+2 2
+drop table t4;
drop table t1,t2,t3;
+end of the tests
diff --git a/mysql-test/r/binlog_killed_simulate.result b/mysql-test/r/binlog_killed_simulate.result
new file mode 100644
index 00000000000..a21ac5b1e32
--- /dev/null
+++ b/mysql-test/r/binlog_killed_simulate.result
@@ -0,0 +1,33 @@
+drop table if exists t1,t2;
+create table t1 (a int) engine=MyISAM;
+insert into t1 set a=1;
+reset master;
+update t1 set a=2 /* will be "killed" after work has been done */;
+select
+(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
+is not null;
+(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
+is not null
+1
+select 1 /* must return 1 as query completed before got killed*/;
+1
+1
+create table t2 (a int, b int) ENGINE=MyISAM;
+reset master;
+load data infile '../std_data_ln/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */;
+ERROR 70100: Query execution was interrupted
+show binlog events from 98;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 98 Begin_load_query 1 # ;file_id=1;block_len=12
+master-bin.000001 133 Execute_load_query 1 # use `test`; load data infile '../std_data_ln/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */ ;file_id=1
+select
+(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
+is not null;
+(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
+is not null
+1
+select 0 /* must return 0 to mean the killed query is in */;
+0
+0
+drop table t1,t2;
+end of the tests
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 524ff48d69e..88601eceb0a 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -414,4 +414,28 @@ NULL
NULL
20070719
drop table t1;
+CREATE TABLE t1 (f1 DATE);
+INSERT INTO t1 VALUES ('2007-07-19'), (NULL);
+SELECT HOUR(f1),
+MINUTE(f1),
+SECOND(f1) FROM t1;
+HOUR(f1) MINUTE(f1) SECOND(f1)
+0 0 0
+NULL NULL NULL
+SELECT HOUR(CAST('2007-07-19' AS DATE)),
+MINUTE(CAST('2007-07-19' AS DATE)),
+SECOND(CAST('2007-07-19' AS DATE));
+HOUR(CAST('2007-07-19' AS DATE)) MINUTE(CAST('2007-07-19' AS DATE)) SECOND(CAST('2007-07-19' AS DATE))
+0 0 0
+SELECT HOUR(CAST(NULL AS DATE)),
+MINUTE(CAST(NULL AS DATE)),
+SECOND(CAST(NULL AS DATE));
+HOUR(CAST(NULL AS DATE)) MINUTE(CAST(NULL AS DATE)) SECOND(CAST(NULL AS DATE))
+NULL NULL NULL
+SELECT HOUR(NULL),
+MINUTE(NULL),
+SECOND(NULL);
+HOUR(NULL) MINUTE(NULL) SECOND(NULL)
+NULL NULL NULL
+DROP TABLE t1;
End of 5.0 tests
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index ab5d23d6cea..3d7486b6ba2 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -425,7 +425,7 @@ explain t2;
Field Type Null Key Default Extra
a int(11) YES NULL
b bigint(11) NO 0
-c bigint(11) NO 0
+c bigint(11) unsigned NO 0
d date YES NULL
e varchar(1) NO
f datetime YES NULL
diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result
index 3900597d2a6..dca4e349c8a 100644
--- a/mysql-test/r/csv.result
+++ b/mysql-test/r/csv.result
@@ -5029,4 +5029,46 @@ F7 ÷ DIVISION SIGN
FE þ LATIN SMALL LETTER THORN
FF ÿ LATIN SMALL LETTER Y WITH DIAERESIS
drop table t1;
+create table t1(a datetime) engine=csv;
+insert into t1 values();
+select * from t1;
+a
+0000-00-00 00:00:00
+drop table t1;
+create table t1(a set('foo','bar')) engine=csv;
+insert into t1 values();
+select * from t1;
+a
+
+drop table t1;
+create table t1(a varchar(32)) engine=csv;
+insert into t1 values();
+select * from t1;
+a
+
+drop table t1;
+create table t1(a int) engine=csv;
+insert into t1 values();
+select * from t1;
+a
+0
+drop table t1;
+create table t1(a blob) engine=csv;
+insert into t1 values();
+select * from t1;
+a
+
+drop table t1;
+create table t1(a bit(1)) engine=csv;
+insert into t1 values();
+select BIN(a) from t1;
+BIN(a)
+0
+drop table t1;
+create table t1(a enum('foo','bar') default 'foo') engine=csv;
+insert into t1 values();
+select * from t1;
+a
+foo
+drop table t1;
End of 5.0 tests
diff --git a/mysql-test/r/ctype_euckr.result b/mysql-test/r/ctype_euckr.result
index b9619370d4c..ee786202c01 100644
--- a/mysql-test/r/ctype_euckr.result
+++ b/mysql-test/r/ctype_euckr.result
@@ -178,3 +178,44 @@ hex(a)
A2E6
FEF7
DROP TABLE t1;
+create table t1 (s1 varchar(5) character set euckr);
+insert into t1 values (0xA141);
+insert into t1 values (0xA15A);
+insert into t1 values (0xA161);
+insert into t1 values (0xA17A);
+insert into t1 values (0xA181);
+insert into t1 values (0xA1FE);
+insert into t1 values (0xA140);
+Warnings:
+Warning 1366 Incorrect string value: '\xA1@' for column 's1' at row 1
+insert into t1 values (0xA15B);
+Warnings:
+Warning 1366 Incorrect string value: '\xA1[' for column 's1' at row 1
+insert into t1 values (0xA160);
+Warnings:
+Warning 1366 Incorrect string value: '\xA1`' for column 's1' at row 1
+insert into t1 values (0xA17B);
+Warnings:
+Warning 1366 Incorrect string value: '\xA1{' for column 's1' at row 1
+insert into t1 values (0xA180);
+Warnings:
+Warning 1366 Incorrect string value: '\xA1\x80' for column 's1' at row 1
+insert into t1 values (0xA1FF);
+Warnings:
+Warning 1366 Incorrect string value: '\xA1\xFF' for column 's1' at row 1
+select hex(s1), hex(convert(s1 using utf8)) from t1 order by binary s1;
+hex(s1) hex(convert(s1 using utf8))
+
+
+
+
+
+
+A141 ECA2A5
+A15A ECA381
+A161 ECA382
+A17A ECA3A5
+A181 ECA3A6
+A1FE EFBFA2
+drop table t1;
+End of 5.0 tests
diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result
index e676d5a5ca0..ae9146fc9db 100644
--- a/mysql-test/r/ctype_uca.result
+++ b/mysql-test/r/ctype_uca.result
@@ -2767,4 +2767,49 @@ a
c
ch
drop table t1;
+set collation_connection=ucs2_unicode_ci;
+drop table if exists t1;
+create table t1 as
+select repeat(' ', 64) as s1, repeat(' ',64) as s2
+union
+select null, null;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `s1` varchar(64) character set ucs2 collate ucs2_unicode_ci default NULL,
+ `s2` varchar(64) character set ucs2 collate ucs2_unicode_ci default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+delete from t1;
+insert into t1 values('aaa','aaa');
+insert into t1 values('aaa|qqq','qqq');
+insert into t1 values('gheis','^[^a-dXYZ]+$');
+insert into t1 values('aab','^aa?b');
+insert into t1 values('Baaan','^Ba*n');
+insert into t1 values('aaa','qqq|aaa');
+insert into t1 values('qqq','qqq|aaa');
+insert into t1 values('bbb','qqq|aaa');
+insert into t1 values('bbb','qqq');
+insert into t1 values('aaa','aba');
+insert into t1 values(null,'abc');
+insert into t1 values('def',null);
+insert into t1 values(null,null);
+insert into t1 values('ghi','ghi[');
+select HIGH_PRIORITY s1 regexp s2 from t1;
+s1 regexp s2
+1
+1
+1
+1
+1
+1
+1
+0
+0
+0
+NULL
+NULL
+NULL
+NULL
+drop table t1;
+set names utf8;
End for 5.0 tests
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index cd734ff9d23..a6d6c604b9b 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -819,6 +819,12 @@ set character_set_client= ucs2;
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
set character_set_client= concat('ucs', substr('2', 1));
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2'
+CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci);
+INSERT INTO t1 VALUES('abcd');
+SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE);
+a
+abcd
+DROP TABLE t1;
End of 4.1 tests
CREATE TABLE t1 (a varchar(64) character set ucs2, b decimal(10,3));
INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0);
@@ -930,4 +936,52 @@ ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_gen
select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0);
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '='
drop table t1;
+set collation_connection=ucs2_general_ci;
+drop table if exists t1;
+create table t1 as
+select repeat(' ', 64) as s1, repeat(' ',64) as s2
+union
+select null, null;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `s1` varchar(64) character set ucs2 default NULL,
+ `s2` varchar(64) character set ucs2 default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+delete from t1;
+insert into t1 values('aaa','aaa');
+insert into t1 values('aaa|qqq','qqq');
+insert into t1 values('gheis','^[^a-dXYZ]+$');
+insert into t1 values('aab','^aa?b');
+insert into t1 values('Baaan','^Ba*n');
+insert into t1 values('aaa','qqq|aaa');
+insert into t1 values('qqq','qqq|aaa');
+insert into t1 values('bbb','qqq|aaa');
+insert into t1 values('bbb','qqq');
+insert into t1 values('aaa','aba');
+insert into t1 values(null,'abc');
+insert into t1 values('def',null);
+insert into t1 values(null,null);
+insert into t1 values('ghi','ghi[');
+select HIGH_PRIORITY s1 regexp s2 from t1;
+s1 regexp s2
+1
+1
+1
+1
+1
+1
+1
+0
+0
+0
+NULL
+NULL
+NULL
+NULL
+drop table t1;
+set names latin1;
+select hex(char(0x41 using ucs2));
+hex(char(0x41 using ucs2))
+0041
End of 5.0 tests
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 710cac388a5..5ca1d578d2a 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -267,6 +267,51 @@ b
select * from t1 where a = 'b' and a != 'b';
a
drop table t1;
+set collation_connection=utf8_general_ci;
+drop table if exists t1;
+create table t1 as
+select repeat(' ', 64) as s1, repeat(' ',64) as s2
+union
+select null, null;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `s1` varchar(64) character set utf8 default NULL,
+ `s2` varchar(64) character set utf8 default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+delete from t1;
+insert into t1 values('aaa','aaa');
+insert into t1 values('aaa|qqq','qqq');
+insert into t1 values('gheis','^[^a-dXYZ]+$');
+insert into t1 values('aab','^aa?b');
+insert into t1 values('Baaan','^Ba*n');
+insert into t1 values('aaa','qqq|aaa');
+insert into t1 values('qqq','qqq|aaa');
+insert into t1 values('bbb','qqq|aaa');
+insert into t1 values('bbb','qqq');
+insert into t1 values('aaa','aba');
+insert into t1 values(null,'abc');
+insert into t1 values('def',null);
+insert into t1 values(null,null);
+insert into t1 values('ghi','ghi[');
+select HIGH_PRIORITY s1 regexp s2 from t1;
+s1 regexp s2
+1
+1
+1
+1
+1
+1
+1
+0
+0
+0
+NULL
+NULL
+NULL
+NULL
+drop table t1;
+set names utf8;
set names utf8;
select 'ваÑÑ' rlike '[[:<:]]ваÑÑ[[:>:]]';
'ваÑÑ' rlike '[[:<:]]ваÑÑ[[:>:]]'
@@ -1538,12 +1583,12 @@ char(53647 using utf8)
Ñ
select char(0xff,0x8f using utf8);
char(0xff,0x8f using utf8)
-ÿ
+
Warnings:
Warning 1300 Invalid utf8 character string: 'FF8F'
select convert(char(0xff,0x8f) using utf8);
convert(char(0xff,0x8f) using utf8)
-ÿ
+
Warnings:
Warning 1300 Invalid utf8 character string: 'FF8F'
set sql_mode=traditional;
@@ -1730,3 +1775,41 @@ i
1
н1234567890
DROP TABLE t1, t2;
+set sql_mode=traditional;
+select hex(char(0xFF using utf8));
+hex(char(0xFF using utf8))
+NULL
+Warnings:
+Error 1300 Invalid utf8 character string: 'FF'
+select hex(convert(0xFF using utf8));
+hex(convert(0xFF using utf8))
+NULL
+Warnings:
+Error 1300 Invalid utf8 character string: 'FF'
+select hex(_utf8 0x616263FF);
+ERROR HY000: Invalid utf8 character string: 'FF'
+select hex(_utf8 X'616263FF');
+ERROR HY000: Invalid utf8 character string: 'FF'
+select hex(_utf8 B'001111111111');
+ERROR HY000: Invalid utf8 character string: 'FF'
+select (_utf8 X'616263FF');
+ERROR HY000: Invalid utf8 character string: 'FF'
+set sql_mode=default;
+select hex(char(0xFF using utf8));
+hex(char(0xFF using utf8))
+
+Warnings:
+Warning 1300 Invalid utf8 character string: 'FF'
+select hex(convert(0xFF using utf8));
+hex(convert(0xFF using utf8))
+
+Warnings:
+Warning 1300 Invalid utf8 character string: 'FF'
+select hex(_utf8 0x616263FF);
+ERROR HY000: Invalid utf8 character string: 'FF'
+select hex(_utf8 X'616263FF');
+ERROR HY000: Invalid utf8 character string: 'FF'
+select hex(_utf8 B'001111111111');
+ERROR HY000: Invalid utf8 character string: 'FF'
+select (_utf8 X'616263FF');
+ERROR HY000: Invalid utf8 character string: 'FF'
diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index d62c865bb3c..6833a7f1594 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -481,7 +481,7 @@ str_to_date(a,b)
create table t2 select str_to_date(a,b) from t1;
describe t2;
Field Type Null Key Default Extra
-str_to_date(a,b) binary(29) YES NULL
+str_to_date(a,b) datetime YES NULL
select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2,
str_to_date("2003-01-02", "%Y-%m-%d") as f3,
diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result
index b37679847be..ff333d88fef 100644
--- a/mysql-test/r/delayed.result
+++ b/mysql-test/r/delayed.result
@@ -73,13 +73,13 @@ a
13
DROP TABLE t1;
SET @bug20627_old_auto_increment_offset=
-@@auto_increment_offset= 2;
+@@auto_increment_offset;
SET @bug20627_old_auto_increment_increment=
-@@auto_increment_increment= 3;
+@@auto_increment_increment;
SET @bug20627_old_session_auto_increment_offset=
-@@session.auto_increment_offset= 4;
+@@session.auto_increment_offset;
SET @bug20627_old_session_auto_increment_increment=
-@@session.auto_increment_increment= 5;
+@@session.auto_increment_increment;
SET @@auto_increment_offset= 2;
SET @@auto_increment_increment= 3;
SET @@session.auto_increment_offset= 4;
@@ -116,13 +116,13 @@ SET @@session.auto_increment_offset=
SET @@session.auto_increment_increment=
@bug20627_old_session_auto_increment_increment;
SET @bug20830_old_auto_increment_offset=
-@@auto_increment_offset= 2;
+@@auto_increment_offset;
SET @bug20830_old_auto_increment_increment=
-@@auto_increment_increment= 3;
+@@auto_increment_increment;
SET @bug20830_old_session_auto_increment_offset=
-@@session.auto_increment_offset= 4;
+@@session.auto_increment_offset;
SET @bug20830_old_session_auto_increment_increment=
-@@session.auto_increment_increment= 5;
+@@session.auto_increment_increment;
SET @@auto_increment_offset= 2;
SET @@auto_increment_increment= 3;
SET @@session.auto_increment_offset= 4;
diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result
index 5084498c01c..eb93c69d960 100644
--- a/mysql-test/r/delete.result
+++ b/mysql-test/r/delete.result
@@ -271,3 +271,11 @@ a
DROP TABLE t1, t2;
DROP DATABASE db1;
DROP DATABASE db2;
+CREATE FUNCTION f1() RETURNS INT RETURN 1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0);
+DELETE FROM t1 ORDER BY (f1(10)) LIMIT 1;
+ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 0, got 1
+DROP TABLE t1;
+DROP FUNCTION f1;
+End of 5.0 tests
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 3a098308b49..81502c7b430 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -326,7 +326,8 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DERIVED t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index
drop table t2;
CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`));
-insert into t1 values (128, 'rozn', 2, now(), 10),(128, 'rozn', 1, now(), 10);
+insert into t1 values (128, 'rozn', 2, curdate(), 10),
+(128, 'rozn', 1, curdate(), 10);
SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices;
min max avg
10.00 10.00 10
diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result
index f6efe2411c0..29fb430bb97 100644
--- a/mysql-test/r/fulltext.result
+++ b/mysql-test/r/fulltext.result
@@ -463,3 +463,9 @@ ALTER TABLE t1 DISABLE KEYS;
SELECT * FROM t1 WHERE MATCH(a) AGAINST('test');
ERROR HY000: Can't find FULLTEXT index matching the column list
DROP TABLE t1;
+CREATE TABLE t1(a TEXT);
+INSERT INTO t1 VALUES(' aaaaa aaaa');
+SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE);
+a
+ aaaaa aaaa
+DROP TABLE t1;
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 73f756bc1d2..df61954b22a 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -861,4 +861,13 @@ select group_concat(distinct a, c order by a desc, c desc) from t1;
group_concat(distinct a, c order by a desc, c desc)
31,11,10,01,00
drop table t1;
+create table t1 (f1 char(20));
+insert into t1 values (''),('');
+select group_concat(distinct f1) from t1;
+group_concat(distinct f1)
+
+select group_concat(f1) from t1;
+group_concat(f1)
+,
+drop table t1;
End of 5.0 tests
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index e5720cc1ee0..1e130877088 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1377,4 +1377,34 @@ SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
MIN(a) MIN(b)
1 2
DROP TABLE t1, t2, t3, t4, t5;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 values (),(),();
+SELECT (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ) as x FROM t1
+GROUP BY x;
+x
+0
+SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) );
+1
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a int, b date NOT NULL, KEY k1 (a,b));
+SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01';
+MIN(b)
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+SET SQL_MODE=ONLY_FULL_GROUP_BY;
+SELECT a FROM t1 HAVING COUNT(*)>2;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT COUNT(*), a FROM t1;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SET SQL_MODE=DEFAULT;
+SELECT a FROM t1 HAVING COUNT(*)>2;
+a
+1
+SELECT COUNT(*), a FROM t1;
+COUNT(*) a
+4 1
+DROP TABLE t1;
End of 5.0 tests
diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result
index ace94217fdc..150b6003dbe 100644
--- a/mysql-test/r/func_math.result
+++ b/mysql-test/r/func_math.result
@@ -322,4 +322,42 @@ mod(5, cast(-2 as unsigned)) mod(5, 18446744073709551614) mod(5, -2)
select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5);
pow(cast(-2 as unsigned), 5) pow(18446744073709551614, 5) pow(-2, 5)
2.1359870359209e+96 2.1359870359209e+96 -32
+CREATE TABLE t1 (a timestamp, b varchar(20), c bit(1));
+INSERT INTO t1 VALUES('1998-09-23', 'str1', 1), ('2003-03-25', 'str2', 0);
+SELECT a DIV 900 y FROM t1 GROUP BY y;
+y
+22201025555
+22255916666
+SELECT DISTINCT a DIV 900 y FROM t1;
+y
+22201025555
+22255916666
+SELECT b DIV 900 y FROM t1 GROUP BY y;
+y
+0
+SELECT c DIV 900 y FROM t1 GROUP BY y;
+y
+0
+DROP TABLE t1;
+CREATE TABLE t1(a LONGBLOB);
+INSERT INTO t1 VALUES('1'),('2'),('3');
+SELECT DISTINCT (a DIV 254576881) FROM t1;
+(a DIV 254576881)
+0
+SELECT (a DIV 254576881) FROM t1 UNION ALL
+SELECT (a DIV 254576881) FROM t1;
+(a DIV 254576881)
+0
+0
+0
+0
+0
+0
+DROP TABLE t1;
+CREATE TABLE t1(a SET('a','b','c'));
+INSERT INTO t1 VALUES ('a');
+SELECT a DIV 2 FROM t1 UNION SELECT a DIV 2 FROM t1;
+a DIV 2
+0
+DROP TABLE t1;
End of 5.0 tests
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index db2490bd261..a302ad4769e 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -185,6 +185,28 @@ ERROR 21000: Operand should contain 1 column(s)
drop table table_26093;
drop function func_26093_a;
drop function func_26093_b;
+SELECT NAME_CONST('test', NOW());
+ERROR HY000: Incorrect arguments to NAME_CONST
+SELECT NAME_CONST('test', UPPER('test'));
+ERROR HY000: Incorrect arguments to NAME_CONST
+SELECT NAME_CONST('test', NULL);
+test
+NULL
+SELECT NAME_CONST('test', 1);
+test
+1
+SELECT NAME_CONST('test', -1);
+test
+-1
+SELECT NAME_CONST('test', 1.0);
+test
+1.0
+SELECT NAME_CONST('test', -1.0);
+test
+-1.0
+SELECT NAME_CONST('test', 'test');
+test
+test
create table t1 (a int not null);
insert into t1 values (-1), (-2);
select min(a) from t1 group by inet_ntoa(a);
diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result
index 584c8a9b820..c2dbfc3c331 100644
--- a/mysql-test/r/func_regexp.result
+++ b/mysql-test/r/func_regexp.result
@@ -1,5 +1,17 @@
drop table if exists t1;
-create table t1 (s1 char(64),s2 char(64));
+set names latin1;
+drop table if exists t1;
+create table t1 as
+select repeat(' ', 64) as s1, repeat(' ',64) as s2
+union
+select null, null;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `s1` varchar(64) default NULL,
+ `s2` varchar(64) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+delete from t1;
insert into t1 values('aaa','aaa');
insert into t1 values('aaa|qqq','qqq');
insert into t1 values('gheis','^[^a-dXYZ]+$');
@@ -98,3 +110,8 @@ R2
R3
deallocate prepare stmt1;
drop table t1;
+End of 4.1 tests
+SELECT 1 REGEXP NULL;
+1 REGEXP NULL
+NULL
+End of 5.0 tests
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index ba66ac3bc94..4db0416bdce 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -199,7 +199,7 @@ f2 datetime YES NULL
f3 time YES NULL
f4 time YES NULL
f5 time YES NULL
-f6 time NO 00:00:00
+f6 time YES NULL
f7 datetime YES NULL
f8 date YES NULL
f9 time YES NULL
diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result
index aa71cee0752..ef5526ca13e 100644
--- a/mysql-test/r/func_set.result
+++ b/mysql-test/r/func_set.result
@@ -73,3 +73,33 @@ find_in_set(binary 'a', 'A,B,C')
select find_in_set('1','3,1,');
find_in_set('1','3,1,')
2
+End of 4.1 tests
+SELECT INTERVAL(0.0, NULL);
+INTERVAL(0.0, NULL)
+1
+SELECT INTERVAL(0.0, CAST(NULL AS DECIMAL));
+INTERVAL(0.0, CAST(NULL AS DECIMAL))
+1
+SELECT INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL));
+INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL))
+1
+SELECT INTERVAL(0.0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
+INTERVAL(0.0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
+8
+SELECT INTERVAL(0.0, CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL),
+CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL),
+CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL));
+INTERVAL(0.0, CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL),
+CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL),
+CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL))
+8
+SELECT INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL),
+CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL),
+CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL),
+CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL));
+INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL),
+CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL),
+CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL),
+CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL))
+8
+End of 5.0 tests
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index ce9633006af..d0809eca65b 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -722,11 +722,11 @@ Warning 1265 Data truncated for column 'format(130,10)' at row 1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `bin(130)` varchar(64) NOT NULL default '',
- `oct(130)` varchar(64) NOT NULL default '',
- `conv(130,16,10)` varchar(64) NOT NULL default '',
+ `bin(130)` varchar(64) default NULL,
+ `oct(130)` varchar(64) default NULL,
+ `conv(130,16,10)` varchar(64) default NULL,
`hex(130)` varchar(6) NOT NULL default '',
- `char(130)` varbinary(1) NOT NULL default '',
+ `char(130)` varbinary(4) NOT NULL default '',
`format(130,10)` varchar(4) NOT NULL default '',
`left(_latin2'a',1)` varchar(1) character set latin2 NOT NULL default '',
`right(_latin2'a',1)` varchar(1) character set latin2 NOT NULL default '',
@@ -1096,6 +1096,18 @@ id select_type table type possible_keys key key_len ref rows Extra
Warnings:
Note 1003 select decode('','zxcv') AS `enc` from `test`.`t1`
drop table t1;
+create table t1 (a bigint not null)engine=myisam;
+insert into t1 set a = 1024*1024*1024*4;
+delete from t1 order by (inet_ntoa(a)) desc limit 10;
+drop table t1;
+create table t1 (a char(36) not null)engine=myisam;
+insert ignore into t1 set a = ' ';
+insert ignore into t1 set a = ' ';
+select * from t1 order by (oct(a));
+a
+
+
+drop table t1;
End of 4.1 tests
create table t1 (d decimal default null);
insert into t1 values (null);
@@ -2153,4 +2165,14 @@ SUBSTR(a,1,len)
ba
DROP TABLE t1;
+CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1;
+SELECT HEX(c1) from t1;
+HEX(c1)
+414243
+DROP TABLE t1;
+CREATE VIEW v1 AS SELECT CHAR(0x414243) as c1;
+SELECT HEX(c1) from v1;
+HEX(c1)
+414243
+DROP VIEW v1;
End of 5.0 tests
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index ee8b8c1e908..74859be4d04 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1027,6 +1027,15 @@ fmtddate field2
Sep-4 12:00AM abcd
DROP TABLE testBug8868;
SET NAMES DEFAULT;
+CREATE TABLE t1 (
+a TIMESTAMP
+);
+INSERT INTO t1 VALUES (now()), (now());
+SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a);
+1
+1
+1
+DROP TABLE t1;
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H);
diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result
index 8476ea9e838..99bfede3ee0 100644
--- a/mysql-test/r/gis-rtree.result
+++ b/mysql-test/r/gis-rtree.result
@@ -167,7 +167,7 @@ count(*)
150
EXPLAIN SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range g g 32 NULL 8 Using where
+1 SIMPLE t1 range g g 34 NULL 8 Using where
SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
fid AsText(g)
1 LINESTRING(150 150,150 150)
@@ -301,7 +301,7 @@ count(*)
EXPLAIN SELECT fid, AsText(g) FROM t2 WHERE Within(g,
GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))'));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range g g 32 NULL 4 Using where
+1 SIMPLE t2 range g g 34 NULL 4 Using where
SELECT fid, AsText(g) FROM t2 WHERE Within(g,
GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))'));
fid AsText(g)
@@ -1425,6 +1425,37 @@ CHECK TABLE t1 EXTENDED;
Table Op Msg_type Msg_text
test.t1 check status OK
DROP TABLE t1;
+create table t1 (a geometry not null, spatial index(a));
+insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 131072)));
+insert into t1 values (PointFromWKB(POINT(9.1248812352444e+192, 2.9740338169556e+284)));
+insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, -0)));
+insert into t1 values (PointFromWKB(POINT(1.49166814624e-154, 2.0880974297595e-53)));
+insert into t1 values (PointFromWKB(POINT(4.0917382598702e+149, 1.2024538023802e+111)));
+insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 2.9993936277913e-241)));
+insert into t1 values (PointFromWKB(POINT(2.5243548967072e-29, 1.2024538023802e+111)));
+insert into t1 values (PointFromWKB(POINT(0, 6.9835074892995e-251)));
+insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 3.1050361846014e+231)));
+insert into t1 values (PointFromWKB(POINT(2.8728483499323e-188, 2.4600631144627e+260)));
+insert into t1 values (PointFromWKB(POINT(3.0517578125e-05, 2.0349165139404e+236)));
+insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 1.1818212630766e-125)));
+insert into t1 values (PointFromWKB(POINT(2.481040258324e-265, 5.7766220027675e-275)));
+insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 2.5243548967072e-29)));
+insert into t1 values (PointFromWKB(POINT(5.7766220027675e-275, 9.9464647281957e+86)));
+insert into t1 values (PointFromWKB(POINT(2.2181357552967e+130, 3.7857669957337e-270)));
+insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.6893488147419e+19)));
+insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.7537584144024e+255)));
+insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 1.8033161362863e-130)));
+insert into t1 values (PointFromWKB(POINT(0, 5.8774717541114e-39)));
+insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 2.2761049594727e-159)));
+insert into t1 values (PointFromWKB(POINT(6.243497100632e+144, 3.7857669957337e-270)));
+insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 2.6355494858076e-82)));
+insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 3.8518598887745e-34)));
+insert into t1 values (PointFromWKB(POINT(4.6566128730774e-10, 2.0880974297595e-53)));
+insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 1.8827498946116e-183)));
+insert into t1 values (PointFromWKB(POINT(1.8033161362863e-130, 9.1248812352444e+192)));
+insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, 2.2761049594727e-159)));
+insert into t1 values (PointFromWKB(POINT(1.94906280228e+289, 1.2338789709327e-178)));
+drop table t1;
CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
INSERT INTO t1(foo) VALUES (NULL);
ERROR 23000: Column 'foo' cannot be null
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index f6189ec1236..40c70721347 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -736,6 +736,12 @@ SELECT * FROM t1;
a
NULL
DROP TABLE t1;
+CREATE TABLE `t1` ( `col9` set('a'), `col89` date);
+INSERT INTO `t1` VALUES ('','0000-00-00');
+select geomfromtext(col9,col89) as a from t1;
+a
+NULL
+DROP TABLE t1;
End of 4.1 tests
create table t1 (s1 geometry not null,s2 char(100));
create trigger t1_bu before update on t1 for each row set new.s1 = null;
@@ -888,4 +894,43 @@ drop table t1, t2;
SELECT 1;
1
1
+CREATE TABLE t1 (p POINT);
+CREATE TABLE t2 (p POINT, INDEX(p));
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 system p NULL NULL NULL 1
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+EXPLAIN
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const 1 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+DROP TABLE t1, t2;
+End of 5.0 tests
End of 5.0 tests
diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result
index 6de9a83aeed..e3c92ecc7c8 100644
--- a/mysql-test/r/grant2.result
+++ b/mysql-test/r/grant2.result
@@ -421,4 +421,22 @@ revoke all privileges, grant option from mysqltest_1@localhost;
revoke all privileges, grant option from mysqltest_2@localhost;
drop user mysqltest_1@localhost;
drop user mysqltest_2@localhost;
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(2,2);
+CREATE TABLE t2 (b INT, c INT);
+INSERT INTO t2 VALUES (1,100),(2,200);
+GRANT SELECT ON t1 TO mysqltest1@localhost;
+GRANT SELECT (b) ON t2 TO mysqltest1@localhost;
+USE db1;
+SELECT c FROM t2;
+ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2'
+SELECT * FROM t2;
+ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2'
+SELECT * FROM t1 JOIN t2 USING (b);
+ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2'
+DROP TABLE db1.t1, db1.t2;
+DROP USER mysqltest1@localhost;
+DROP DATABASE db1;
End of 5.0 tests
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 053c2901509..1693fa646eb 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1113,3 +1113,68 @@ c b
3 1
3 2
DROP TABLE t1;
+CREATE TABLE t1(
+id INT AUTO_INCREMENT PRIMARY KEY,
+c1 INT NOT NULL,
+c2 INT NOT NULL,
+UNIQUE KEY (c2,c1));
+INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
+SELECT * FROM t1 ORDER BY c1;
+id c1 c2
+5 1 3
+4 2 3
+3 3 5
+2 4 1
+1 5 1
+SELECT * FROM t1 GROUP BY id ORDER BY c1;
+id c1 c2
+5 1 3
+4 2 3
+3 3 5
+2 4 1
+1 5 1
+SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
+id c1 c2
+5 1 3
+4 2 3
+3 3 5
+2 4 1
+1 5 1
+SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
+id c1 c2
+2 4 1
+1 5 1
+5 1 3
+4 2 3
+3 3 5
+SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
+id c1 c2
+3 3 5
+5 1 3
+4 2 3
+2 4 1
+1 5 1
+SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
+id c1 c2
+3 3 5
+4 2 3
+5 1 3
+1 5 1
+2 4 1
+SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
+id c1 c2
+1 5 1
+4 2 3
+3 3 5
+SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
+id c1 c2
+3 3 5
+4 2 3
+1 5 1
+SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
+id c1 c2
+3 3 5
+4 2 3
+1 5 1
+DROP TABLE t1;
+End of 5.0 tests
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index cf82cdd31bd..80f85aee429 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1385,4 +1385,40 @@ f6 bigint(20) NO 10
f7 datetime NO NULL
f8 datetime YES 2006-01-01 00:00:00
drop table t1;
+select * from information_schema.columns where table_schema = NULL;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT
+select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT
+select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
+select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
+select * from information_schema.schemata where schema_name = NULL;
+CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
+select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT
+select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT
+select * from information_schema.tables where table_schema = NULL;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
+select * from information_schema.tables where table_catalog = NULL;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
+select * from information_schema.tables where table_name = NULL;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
+select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
+select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
+select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL;
+TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER
+select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL;
+TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER
+select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE
+select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE
End of 5.0 tests.
+show fields from information_schema.table_names;
+ERROR 42S02: Unknown table 'table_names' in information_schema
+show keys from information_schema.table_names;
+ERROR 42S02: Unknown table 'table_names' in information_schema
diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result
index dd1f0295277..ef63ef719a4 100644
--- a/mysql-test/r/information_schema_db.result
+++ b/mysql-test/r/information_schema_db.result
@@ -130,7 +130,7 @@ Warnings:
Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
show fields from testdb_1.v7;
Field Type Null Key Default Extra
-f1 null YES NULL
+f1 char(4) YES NULL
Warnings:
Note 1449 There is no 'no_such_user'@'no_such_host' registered
create table t3 (f1 char(4), f2 char(4));
@@ -150,7 +150,7 @@ View Create View
v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v6` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1`
show fields from testdb_1.v7;
Field Type Null Key Default Extra
-f1 null YES NULL
+f1 char(4) YES NULL
Warnings:
Note 1449 There is no 'no_such_user'@'no_such_host' registered
show create view testdb_1.v7;
@@ -178,7 +178,7 @@ show create view v4;
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
show fields from v4;
Field Type Null Key Default Extra
-f1 null YES NULL
+f1 char(4) YES NULL
f2 char(4) YES NULL
show fields from v2;
Field Type Null Key Default Extra
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 6082a30bce3..38b26425ec8 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1119,6 +1119,19 @@ show master status /* there must be no UPDATE query event */;
File Position Binlog_Do_DB Binlog_Ignore_DB
master-bin.000001 98
drop table t1, t2;
+drop table if exists t1, t2;
+CREATE TABLE t1 (a int, PRIMARY KEY (a));
+CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
+create trigger trg_del_t2 after delete on t2 for each row
+insert into t1 values (1);
+insert into t1 values (1);
+insert into t2 values (1),(2);
+delete t2 from t2;
+ERROR 23000: Duplicate entry '1' for key 1
+select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
+count(*)
+2
+drop table t1, t2;
create table t1 (a int, b int) engine=innodb;
insert into t1 values(20,null);
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
@@ -1792,10 +1805,10 @@ Variable_name Value
Innodb_page_size 16384
show status like "Innodb_rows_deleted";
Variable_name Value
-Innodb_rows_deleted 72
+Innodb_rows_deleted 73
show status like "Innodb_rows_inserted";
Variable_name Value
-Innodb_rows_inserted 29732
+Innodb_rows_inserted 29734
show status like "Innodb_rows_updated";
Variable_name Value
Innodb_rows_updated 29532
diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result
index 2c62537aa94..bfe8c984b7b 100644
--- a/mysql-test/r/innodb_gis.result
+++ b/mysql-test/r/innodb_gis.result
@@ -542,3 +542,42 @@ Overlaps(@horiz1, @point2)
0
DROP TABLE t1;
End of 5.0 tests
+CREATE TABLE t1 (p POINT);
+CREATE TABLE t2 (p POINT, INDEX(p));
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const 1 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+EXPLAIN
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const 1 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+DROP TABLE t1, t2;
+End of 5.0 tests
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index d16562d97e2..7a37f49125a 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -830,3 +830,15 @@ id prev_id join_id
3 2 0
4 3 0
DROP TABLE t1,t2;
+#
+# Bug#30384: Having SQL_BUFFER_RESULT option in the
+# CREATE .. KEY(..) .. SELECT led to creating corrupted index.
+#
+create table t1(f1 int);
+insert into t1 values(1),(2),(3);
+create table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1;
+check table t2 extended;
+Table Op Msg_type Msg_text
+test.t2 check status OK
+drop table t1,t2;
+##################################################################
diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result
index f09725dafcb..a89b79263c5 100644
--- a/mysql-test/r/lowercase_view.result
+++ b/mysql-test/r/lowercase_view.result
@@ -119,7 +119,7 @@ create table t1Aa (col1 int);
create view v1Aa as select col1 from t1Aa as AaA;
show create view v1AA;
View Create View
-v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA`
+v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa`
drop view v1AA;
select Aaa.col1 from t1Aa as AaA;
col1
@@ -128,6 +128,23 @@ drop view v1AA;
create view v1Aa as select AaA.col1 from t1Aa as AaA;
show create view v1AA;
View Create View
-v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA`
+v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa`
drop view v1AA;
drop table t1Aa;
+CREATE TABLE t1 (a int, b int);
+select X.a from t1 AS X group by X.b having (X.a = 1);
+a
+select X.a from t1 AS X group by X.b having (x.a = 1);
+a
+select X.a from t1 AS X group by X.b having (x.b = 1);
+a
+CREATE OR REPLACE VIEW v1 AS
+select X.a from t1 AS X group by X.b having (X.a = 1);
+SHOW CREATE VIEW v1;
+View Create View
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `x`.`a` AS `a` from `t1` `x` group by `x`.`b` having (`x`.`a` = 1)
+SELECT * FROM v1;
+a
+DROP VIEW v1;
+DROP TABLE t1;
+End of 5.0 tests.
diff --git a/mysql-test/r/mix_innodb_myisam_binlog.result b/mysql-test/r/mix_innodb_myisam_binlog.result
index 5777bd890b2..89ee82e9655 100644
--- a/mysql-test/r/mix_innodb_myisam_binlog.result
+++ b/mysql-test/r/mix_innodb_myisam_binlog.result
@@ -351,7 +351,7 @@ drop function if exists bug27417;
drop table if exists t1,t2;
CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
-create function bug27417(n int)
+create function bug27417(n int)
RETURNS int(11)
begin
insert into t1 values (null);
@@ -365,7 +365,7 @@ insert into t2 values (bug27417(2));
ERROR 23000: Duplicate entry '2' for key 1
show master status;
File Position Binlog_Do_DB Binlog_Ignore_DB
-master-bin.000001 98
+master-bin.000001 196
/* only (!) with fixes for #23333 will show there is the query */;
select count(*) from t1 /* must be 3 */;
count(*)
@@ -390,6 +390,119 @@ affected rows: 0
select count(*) from t1 /* must be 7 */;
count(*)
7
-drop function bug27417;
drop table t1,t2;
+CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
+CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
+CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
+CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
+insert into t2 values (1);
+reset master;
+insert into t2 values (bug27417(1));
+ERROR 23000: Duplicate entry '1' for key 1
+show master status /* the offset must denote there is the query */;
+File Position Binlog_Do_DB Binlog_Ignore_DB
+master-bin.000001 267
+select count(*) from t1 /* must be 1 */;
+count(*)
+1
+delete from t1;
+delete from t2;
+insert into t2 values (2);
+reset master;
+insert into t2 select bug27417(1) union select bug27417(2);
+ERROR 23000: Duplicate entry '2' for key 1
+show master status /* the offset must denote there is the query */;
+File Position Binlog_Do_DB Binlog_Ignore_DB
+master-bin.000001 290
+select count(*) from t1 /* must be 2 */;
+count(*)
+2
+delete from t1;
+insert into t3 values (1,1),(2,3),(3,4);
+reset master;
+update t3 set b=b+bug27417(1);
+ERROR 23000: Duplicate entry '4' for key 2
+show master status /* the offset must denote there is the query */;
+File Position Binlog_Do_DB Binlog_Ignore_DB
+master-bin.000001 190
+select count(*) from t1 /* must be 2 */;
+count(*)
+2
+delete from t3;
+delete from t4;
+insert into t3 values (1,1);
+insert into t4 values (1,1),(2,2);
+reset master;
+UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
+ERROR 23000: Duplicate entry '2' for key 1
+show master status /* the offset must denote there is the query */;
+File Position Binlog_Do_DB Binlog_Ignore_DB
+master-bin.000001 301
+select count(*) from t1 /* must be 4 */;
+count(*)
+4
+delete from t1;
+delete from t3;
+delete from t4;
+insert into t3 values (1,1),(2,2);
+insert into t4 values (1,1),(2,2);
+reset master;
+UPDATE t3,t4 SET t3.a=t4.a + bug27417(1);
+ERROR 23000: Duplicate entry '2' for key 1
+select count(*) from t1 /* must be 1 */;
+count(*)
+1
+drop table t4;
+delete from t1;
+delete from t2;
+delete from t3;
+insert into t2 values (1);
+insert into t3 values (1,1);
+create trigger trg_del before delete on t2 for each row
+insert into t3 values (bug27417(1), 2);
+reset master;
+delete from t2;
+ERROR 23000: Duplicate entry '1' for key 1
+show master status /* the offset must denote there is the query */;
+File Position Binlog_Do_DB Binlog_Ignore_DB
+master-bin.000001 246
+select count(*) from t1 /* must be 1 */;
+count(*)
+1
+drop trigger trg_del;
+delete from t1;
+delete from t2;
+delete from t5;
+create trigger trg_del_t2 after delete on t2 for each row
+insert into t1 values (1);
+insert into t2 values (2),(3);
+insert into t5 values (1),(2);
+reset master;
+delete t2.* from t2,t5 where t2.a=t5.a + 1;
+ERROR 23000: Duplicate entry '1' for key 1
+show master status /* the offset must denote there is the query */;
+File Position Binlog_Do_DB Binlog_Ignore_DB
+master-bin.000001 274
+select count(*) from t1 /* must be 1 */;
+count(*)
+1
+delete from t1;
+create table t4 (a int default 0, b int primary key) engine=innodb;
+insert into t4 values (0, 17);
+reset master;
+load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
+ERROR 23000: Duplicate entry '17' for key 1
+select * from t4;
+a b
+0 17
+select count(*) from t1 /* must be 2 */;
+count(*)
+2
+show master status /* the offset must denote there is the query */;
+File Position Binlog_Do_DB Binlog_Ignore_DB
+master-bin.000001 376
+drop trigger trg_del_t2;
+drop table t1,t2,t3,t4,t5;
+drop function bug27417;
end of tests
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 0f624e3ee8d..d95036090a5 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -545,7 +545,7 @@ a b
4 4
show master status /* there must be the UPDATE query event */;
File Position Binlog_Do_DB Binlog_Ignore_DB
-master-bin.000001 189
+master-bin.000001 260
delete from t1;
delete from t2;
insert into t1 values (1,2),(3,4),(4,4);
@@ -555,6 +555,26 @@ UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a;
ERROR 23000: Duplicate entry '4' for key 1
show master status /* there must be the UPDATE query event */;
File Position Binlog_Do_DB Binlog_Ignore_DB
-master-bin.000001 204
+master-bin.000001 275
drop table t1, t2;
+drop table if exists t1, t2, t3;
+CREATE TABLE t1 (a int, PRIMARY KEY (a));
+CREATE TABLE t2 (a int, PRIMARY KEY (a));
+CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;
+create trigger trg_del_t3 before delete on t3 for each row insert into t1 values (1);
+insert into t2 values (1),(2);
+insert into t3 values (1),(2);
+reset master;
+delete t3.* from t2,t3 where t2.a=t3.a;
+ERROR 23000: Duplicate entry '1' for key 1
+select count(*) from t1 /* must be 1 */;
+count(*)
+1
+select count(*) from t3 /* must be 1 */;
+count(*)
+1
+show binlog events from 98;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 98 Query 1 # use `test`; delete t3.* from t2,t3 where t2.a=t3.a
+drop table t1, t2, t3;
end of tests
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 7fc29cd13ca..56933f45fbf 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -1806,4 +1806,37 @@ SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
a
1
DROP TABLE t1;
+CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE INDEX (c1), INDEX (c2)) ENGINE=MYISAM;
+SHOW TABLE STATUS LIKE 't1';
+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
+t1 MyISAM 10 Fixed 0 # # # 1024 # # # # # # #
+INSERT INTO t1 VALUES (1,1);
+SHOW TABLE STATUS LIKE 't1';
+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
+t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #
+ALTER TABLE t1 DISABLE KEYS;
+SHOW TABLE STATUS LIKE 't1';
+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
+t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #
+ALTER TABLE t1 ENABLE KEYS;
+SHOW TABLE STATUS LIKE 't1';
+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
+t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #
+ALTER TABLE t1 DISABLE KEYS;
+SHOW TABLE STATUS LIKE 't1';
+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
+t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #
+ALTER TABLE t1 ENABLE KEYS;
+SHOW TABLE STATUS LIKE 't1';
+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
+t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #
+# Enable keys with parallel repair
+SET @@myisam_repair_threads=2;
+ALTER TABLE t1 DISABLE KEYS;
+ALTER TABLE t1 ENABLE KEYS;
+SET @@myisam_repair_threads=1;
+CHECK TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+DROP TABLE t1;
End of 5.0 tests
diff --git a/mysql-test/r/myisampack.result b/mysql-test/r/myisampack.result
new file mode 100644
index 00000000000..5f39d318234
--- /dev/null
+++ b/mysql-test/r/myisampack.result
@@ -0,0 +1,29 @@
+CREATE TABLE t1(c1 DOUBLE, c2 DOUBLE, c3 DOUBLE, c4 DOUBLE, c5 DOUBLE,
+c6 DOUBLE, c7 DOUBLE, c8 DOUBLE, c9 DOUBLE, a INT PRIMARY KEY);
+INSERT INTO t1 VALUES
+(-3.31168791059336e-06,-3.19054655887874e-06,-1.06528081684847e-05,-1.227278240089e-06,-1.66718069164799e-06,-2.59038972510885e-06,-2.83145227805303e-06,-4.09678491270648e-07,-2.22610091291797e-06,6),
+(0.0030743000272545,2.53222044316438e-05,2.78674650061845e-05,1.95914465544536e-05,1.7347572525984e-05,1.87513810069614e-05,1.69882826885005e-05,2.44449336987598e-05,1.89914629921774e-05,9),
+(2.85229319423495e-05,3.05970988282259e-05,3.77161100113133e-05,2.3055238978766e-05,2.08241267364615e-05,2.28009504270553e-05,2.12070165658947e-05,2.84350091565409e-05,2.3366822910704e-05,3),
+(0,0,0,0,0,0,0,0,0,12),
+(3.24544577570754e-05,3.44619021870993e-05,4.37561613201124e-05,2.57556808726748e-05,2.3195354640561e-05,2.58532400758869e-05,2.34934241667179e-05,3.1621640063232e-05,2.58229982746189e-05,19),
+(2.53222044316438e-05,0.00445071933455582,2.97447268116016e-05,2.12379514059868e-05,1.86777776502663e-05,2.0170058676712e-05,1.8946030385445e-05,2.66040037173511e-05,2.09161899668946e-05,20),
+(3.03462382611645e-05,3.26517930083994e-05,3.5242025468662e-05,2.53219745106391e-05,2.24384532945004e-05,2.4052346047657e-05,2.23865572957053e-05,3.1634313969082e-05,2.48285463481801e-05,21),
+(1.95914465544536e-05,2.12379514059868e-05,2.27808649037128e-05,0.000341724375366877,1.4512761275113e-05,1.56475828693953e-05,1.44372366441415e-05,2.07952121981765e-05,1.61488256935919e-05,28),
+(1.7347572525984e-05,1.86777776502663e-05,2.04116907052727e-05,1.4512761275113e-05,0.000432162526082388,1.38116514014465e-05,1.2712914948904e-05,1.82503165178506e-05,1.43043075345922e-05,30),
+(1.68339762136661e-05,1.77836497166611e-05,2.36328309295222e-05,1.30183423732016e-05,1.18674654241553e-05,1.32467273128652e-05,1.24581739117775e-05,1.55624190959406e-05,1.33010638508213e-05,31),
+(1.89643062824415e-05,2.06997140070717e-05,2.29045490159364e-05,1.57918175731019e-05,1.39864987449492e-05,1.50580274578455e-05,1.45908734129609e-05,1.95329296993327e-05,1.5814709481221e-05,32),
+(1.69882826885005e-05,1.8946030385445e-05,2.00820439721439e-05,1.44372366441415e-05,1.2712914948904e-05,1.35209686474184e-05,0.00261563314789896,1.78285095864627e-05,1.46699314500019e-05,34),
+(2.0278186540684e-05,2.18923409729654e-05,2.39981539939738e-05,1.71774589459438e-05,1.54654355357383e-05,1.62731485707636e-05,1.49253140625051e-05,2.18229800160297e-05,1.71923561673718e-05,35),
+(2.44449336987598e-05,2.66040037173511e-05,2.84860148925308e-05,2.07952121981765e-05,1.82503165178506e-05,1.97667730441441e-05,1.78285095864627e-05,0.00166478601822712,2.0299952103232e-05,36),
+(1.89914629921774e-05,2.09161899668946e-05,2.26026841007872e-05,1.61488256935919e-05,1.43043075345922e-05,1.52609063290127e-05,1.46699314500019e-05,2.0299952103232e-05,0.00306670170971682,39),
+(0,0,0,0,0,0,0,0,0,41),
+(0,0,0,0,0,0,0,0,0,17),
+(0,0,0,0,0,0,0,0,0,18),
+(2.51880677333017e-05,2.63051795435778e-05,2.79874748974906e-05,2.02888886670845e-05,1.8178636318197e-05,1.91308527003585e-05,1.83260023644133e-05,2.4422300558171e-05,1.96411467520551e-05,44),
+(2.22402118719591e-05,2.37546284320705e-05,2.58463051055541e-05,1.83391609130854e-05,1.6300720519646e-05,1.74559091886791e-05,1.63733785575587e-05,2.26616253279828e-05,1.79541237435621e-05,45),
+(3.01092775359837e-05,3.23865212934412e-05,4.09444584045994e-05,0,2.15470966302776e-05,2.39082636344032e-05,2.28296706429177e-05,2.9007671511595e-05,2.44201138973326e-05,46);
+FLUSH TABLES;
+CHECK TABLE t1 EXTENDED;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+DROP TABLE t1;
diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result
index c6e589a5fb7..eded1a3fc3b 100644
--- a/mysql-test/r/mysql.result
+++ b/mysql-test/r/mysql.result
@@ -178,4 +178,6 @@ ERROR at line 1: DELIMITER cannot contain a backslash character
1
1
1
+This is a file starting with UTF8 BOM 0xEFBBBF
+This is a file starting with UTF8 BOM 0xEFBBBF
End of 5.0 tests
diff --git a/mysql-test/r/mysql_comments.result b/mysql-test/r/mysql_comments.result
new file mode 100644
index 00000000000..366ceeb5bbf
--- /dev/null
+++ b/mysql-test/r/mysql_comments.result
@@ -0,0 +1,50 @@
+drop table if exists t1;
+drop function if exists foofct;
+drop procedure if exists empty;
+drop procedure if exists foosp;
+drop procedure if exists nicesp;
+drop trigger if exists t1_empty;
+drop trigger if exists t1_bi;
+"Pass 1 : --disable-comments"
+1
+1
+2
+2
+foofct("call 1")
+call 1
+Function sql_mode Create Function
+foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nreturn\n\n\n\nx
+foofct("call 2")
+call 2
+Function sql_mode Create Function
+foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nbegin\n \n \n \n\n \n\n \n return x;\nend
+Procedure sql_mode Create Procedure
+empty CREATE DEFINER=`root`@`localhost` PROCEDURE `empty`()\nbegin\nend
+id data
+foo 42
+Procedure sql_mode Create Procedure
+foosp CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into test.t1\n\n\n\n\n \n\n \n values ("foo", 42)
+Procedure sql_mode Create Procedure
+nicesp CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n \n declare b int;\n declare c float;\n\n \n \n\n \nend
+"Pass 2 : --enable-comments"
+1
+1
+2
+2
+foofct("call 1")
+call 1
+Function sql_mode Create Function
+foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nreturn\n-- comment 1a\n# comment 1b\n/* comment 1c */\nx # after body, on same line
+foofct("call 2")
+call 2
+Function sql_mode Create Function
+foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nbegin\n -- comment 1a\n # comment 1b\n /*\n comment 1c\n */\n\n -- empty line below\n\n -- empty line above\n return x;\nend
+Procedure sql_mode Create Procedure
+empty CREATE DEFINER=`root`@`localhost` PROCEDURE `empty`()\nbegin\nend
+id data
+foo 42
+Procedure sql_mode Create Procedure
+foosp CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into test.t1\n## These comments are part of the procedure body, and should be kept.\n# Comment 2a\n-- Comment 2b\n/* Comment 2c */\n -- empty line below\n\n -- empty line above\n values ("foo", 42) # comment 3, still part of the body
+Procedure sql_mode Create Procedure
+nicesp CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n -- declare some variables here\n declare b int;\n declare c float;\n\n -- do more stuff here\n -- commented nicely and so on\n\n -- famous last words ...\nend
+End of 5.0 tests
diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result
index d16a4c39a11..9f001c293de 100644
--- a/mysql-test/r/mysqlbinlog.result
+++ b/mysql-test/r/mysqlbinlog.result
@@ -325,4 +325,5 @@ flush logs;
drop table t1;
1
drop table t1;
+shell> mysqlbinlog std_data/corrupt-relay-bin.000624 > var/tmp/bug31793.sql
End of 5.0 tests
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 42c437857b7..ca0aa399a56 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -3544,5 +3544,27 @@ c1
2
DROP TABLE t1,t2;
#
+# Bug#29815: new option for suppressing last line of mysqldump:
+# "Dump completed on"
+#
+# --skip-dump-date:
+--
+
+
+
+-- Dump completed
+# --dump-date:
+--
+
+
+
+-- Dump completed on DATE
+# --dump-date (default):
+--
+
+
+
+-- Dump completed on DATE
+#
# End of 5.0 tests
#
diff --git a/mysql-test/r/ndb_condition_pushdown.result b/mysql-test/r/ndb_condition_pushdown.result
index 6012c9b6969..d49c0cd983e 100644
--- a/mysql-test/r/ndb_condition_pushdown.result
+++ b/mysql-test/r/ndb_condition_pushdown.result
@@ -1888,5 +1888,27 @@ set engine_condition_pushdown = 1;
SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
fname lname
Young Foo
+drop table t1;
+create table t1 (a int, b int, c int, d int, primary key using hash(a))
+engine=ndbcluster;
+insert into t1 values (10,1,100,0+0x1111);
+insert into t1 values (20,2,200,0+0x2222);
+insert into t1 values (30,3,300,0+0x3333);
+insert into t1 values (40,4,400,0+0x4444);
+insert into t1 values (50,5,500,0+0x5555);
+set engine_condition_pushdown = on;
+select a,b,d from t1
+where b in (0,1,2,5)
+order by b;
+a b d
+10 1 4369
+20 2 8738
+50 5 21845
+a b d
+10 1 4369
+20 2 8738
+50 5 21845
+Warnings:
+Warning 4294 Scan filter is too large, discarded
set engine_condition_pushdown = @old_ecpd;
DROP TABLE t1,t2,t3,t4,t5;
diff --git a/mysql-test/r/ndb_update.result b/mysql-test/r/ndb_update.result
index d75f82172ae..7848a47bcef 100644
--- a/mysql-test/r/ndb_update.result
+++ b/mysql-test/r/ndb_update.result
@@ -39,4 +39,12 @@ pk1 b c
10 0 0
12 2 2
14 1 1
+create unique index ib on t1(b);
+update t1 set c = 4 where pk1 = 12;
+update ignore t1 set b = 55 where pk1 = 14;
+select * from t1 order by pk1;
+pk1 b c
+10 0 0
+12 2 4
+14 55 1
DROP TABLE IF EXISTS t1;
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index c33adee76b2..090f41baec3 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -1,4 +1,4 @@
-drop table if exists t1;
+drop table if exists t1, t2;
select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null;
NULL NULL isnull(null) isnull(1/0) isnull(1/0 = null) ifnull(null,1) ifnull(null,"TRUE") ifnull("TRUE","ERROR") 1/0 is null 1 is not null
NULL NULL 1 1 1 1 TRUE TRUE 1 1
@@ -320,3 +320,26 @@ bug19145c CREATE TABLE `bug19145c` (
drop table bug19145a;
drop table bug19145b;
drop table bug19145c;
+# End of 4.1 tests
+#
+# Bug #31471: decimal_bin_size: Assertion `scale >= 0 &&
+# precision > 0 && scale <= precision'
+#
+CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL);
+INSERT INTO t1 (a, b) VALUES (0, 0);
+CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1;
+DESCRIBE t2;
+Field Type Null Key Default Extra
+IFNULL(a, b) decimal(1,0) unsigned YES NULL
+DROP TABLE t2;
+CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1;
+DESCRIBE t2;
+Field Type Null Key Default Extra
+IFNULL(a, NULL) decimal(1,0) YES NULL
+DROP TABLE t2;
+CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1;
+DESCRIBE t2;
+Field Type Null Key Default Extra
+IFNULL(NULL, b) decimal(1,0) YES NULL
+DROP TABLE t1, t2;
+# End of 5.0 tests
diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result
index 4a54b17316d..ad04e7304c9 100644
--- a/mysql-test/r/olap.result
+++ b/mysql-test/r/olap.result
@@ -715,3 +715,22 @@ a SUM(a)
4 4
NULL 14
DROP TABLE t1;
+#
+# Bug#31095: Unexpected NULL constant caused server crash.
+#
+create table t1(a int);
+insert into t1 values (1),(2),(3);
+select count(a) from t1 group by null with rollup;
+count(a)
+3
+3
+drop table t1;
+##############################################################
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(0);
+SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;
+1
+1
+1
+DROP TABLE t1;
+End of 5.0 tests
diff --git a/mysql-test/r/outfile_loaddata.result b/mysql-test/r/outfile_loaddata.result
new file mode 100644
index 00000000000..1bcaf308b7c
--- /dev/null
+++ b/mysql-test/r/outfile_loaddata.result
@@ -0,0 +1,85 @@
+DROP TABLE IF EXISTS t1, t2;
+#
+# Bug#31663 FIELDS TERMINATED BY special character
+#
+CREATE TABLE t1 (i1 int, i2 int, c1 VARCHAR(256), c2 VARCHAR(256));
+INSERT INTO t1 VALUES (101, 202, '-r-', '=raker=');
+# FIELDS TERMINATED BY 'raker', warning:
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'raker' FROM t1;
+Warnings:
+Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY
+SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
+LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
+101raker202raker-r-raker=raker=
+
+CREATE TABLE t2 SELECT * FROM t1;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'raker';
+Warnings:
+Warning 1262 Row 1 was truncated; it contained more data than there were input columns
+SELECT * FROM t2;
+i1 i2 c1 c2
+101 202 -r- =raker=
+101 202 -r- =
+DROP TABLE t2;
+# Only numeric fields, FIELDS TERMINATED BY 'r', no warnings:
+SELECT i1, i2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'r' FROM t1;
+SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
+LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
+101r202
+
+CREATE TABLE t2 SELECT i1, i2 FROM t1;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'r';
+SELECT i1, i2 FROM t2;
+i1 i2
+101 202
+101 202
+DROP TABLE t2;
+# FIELDS TERMINATED BY '0', warning:
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY '0' FROM t1;
+Warnings:
+Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY
+SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
+LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
+10102020-r-0=raker=
+
+CREATE TABLE t2 SELECT * FROM t1;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY '0';
+Warnings:
+Warning 1262 Row 1 was truncated; it contained more data than there were input columns
+SELECT * FROM t2;
+i1 i2 c1 c2
+101 202 -r- =raker=
+1 1 2 2
+DROP TABLE t2;
+# FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', warning:
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1;
+Warnings:
+Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY
+SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
+LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
+10102020"-r-"0"=raker="
+
+CREATE TABLE t2 SELECT * FROM t1;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0';
+Warnings:
+Warning 1262 Row 1 was truncated; it contained more data than there were input columns
+SELECT * FROM t2;
+i1 i2 c1 c2
+101 202 -r- =raker=
+1 1 2 2
+DROP TABLE t2;
+# Only string fields, FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', no warnings:
+SELECT c1, c2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1;
+SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
+LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
+"-r-"0"=raker="
+
+CREATE TABLE t2 SELECT c1, c2 FROM t1;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0';
+SELECT c1, c2 FROM t2;
+c1 c2
+-r- =raker=
+-r- =raker=
+DROP TABLE t2;
+DROP TABLE t1;
+# End of 5.0 tests.
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 7ccb41e6294..57932a6c455 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -2973,11 +2973,13 @@ Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
@@ -3011,7 +3013,6 @@ Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
Warnings:
-Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 21d2b23f27b..fd24c29d558 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -2956,11 +2956,13 @@ Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
@@ -2994,7 +2996,6 @@ Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
Warnings:
-Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index 6b31e95c6c8..b4596ab85bc 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -2957,11 +2957,13 @@ Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
@@ -2995,7 +2997,6 @@ Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
Warnings:
-Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index c7b20b774bd..18982db937a 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -2893,11 +2893,13 @@ Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
@@ -2931,7 +2933,6 @@ Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
Warnings:
-Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
@@ -5914,11 +5915,13 @@ Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
@@ -5952,7 +5955,6 @@ Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
Warnings:
-Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result
index 5e97d5cf179..0e4086bc202 100644
--- a/mysql-test/r/ps_6bdb.result
+++ b/mysql-test/r/ps_6bdb.result
@@ -2956,11 +2956,13 @@ Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
@@ -2994,7 +2996,6 @@ Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
Warnings:
-Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index 7ca18edc9cc..7a20fb3146d 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -2956,11 +2956,13 @@ Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
+Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Note 1265 Data truncated for column 'c13' at row 1
@@ -2994,7 +2996,6 @@ Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
Warnings:
-Note 1265 Data truncated for column 'c13' at row 1
Warning 1265 Data truncated for column 'c15' at row 1
Warning 1264 Out of range value adjusted for column 'c16' at row 1
Warning 1264 Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/rpl_flush_log_loop.result b/mysql-test/r/rpl_flush_log_loop.result
index f9bd42ec26c..3b1db804da9 100644
--- a/mysql-test/r/rpl_flush_log_loop.result
+++ b/mysql-test/r/rpl_flush_log_loop.result
@@ -4,6 +4,13 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
+show variables like 'relay_log%';
+Variable_name Value
+relay_log MYSQLTEST_VARDIR/master-data/relay-log
+relay_log_index
+relay_log_info_file relay-log.info
+relay_log_purge ON
+relay_log_space_limit 0
stop slave;
change master to master_host='127.0.0.1',master_user='root',
master_password='',master_port=MASTER_PORT;
diff --git a/mysql-test/r/rpl_packet.result b/mysql-test/r/rpl_packet.result
index 894bc81b08d..9425724b434 100644
--- a/mysql-test/r/rpl_packet.result
+++ b/mysql-test/r/rpl_packet.result
@@ -21,6 +21,37 @@ STOP SLAVE;
START SLAVE;
CREATE TABLe `t1` (`f1` LONGTEXT) ENGINE=MyISAM;
INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2048');
-SHOW STATUS LIKE 'Slave_running';
-Variable_name Value
-Slave_running OFF
+show slave status;
+Slave_IO_State #
+Master_Host 127.0.0.1
+Master_User root
+Master_Port MASTER_MYPORT
+Connect_Retry 1
+Master_Log_File master-bin.000001
+Read_Master_Log_Pos 2138
+Relay_Log_File #
+Relay_Log_Pos #
+Relay_Master_Log_File master-bin.000001
+Slave_IO_Running No
+Slave_SQL_Running #
+Replicate_Do_DB
+Replicate_Ignore_DB
+Replicate_Do_Table
+Replicate_Ignore_Table
+Replicate_Wild_Do_Table
+Replicate_Wild_Ignore_Table
+Last_Errno 0
+Last_Error
+Skip_Counter 0
+Exec_Master_Log_Pos 2138
+Relay_Log_Space #
+Until_Condition None
+Until_Log_File
+Until_Log_Pos 0
+Master_SSL_Allowed No
+Master_SSL_CA_File
+Master_SSL_CA_Path
+Master_SSL_Cert
+Master_SSL_Cipher
+Master_SSL_Key
+Seconds_Behind_Master #
diff --git a/mysql-test/r/rpl_slave_skip.result b/mysql-test/r/rpl_slave_skip.result
new file mode 100644
index 00000000000..a59ac3eb884
--- /dev/null
+++ b/mysql-test/r/rpl_slave_skip.result
@@ -0,0 +1,144 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+**** On Master ****
+CREATE TABLE t1 (a INT, b SET('master','slave')) ENGINE=INNODB;
+CREATE TABLE t2 (a INT, b SET('master','slave')) ENGINE=MYISAM;
+==== Skipping normal transactions ====
+**** On Slave ****
+STOP SLAVE;
+**** On Master ****
+BEGIN;
+INSERT INTO t1 VALUES (1, 'master');
+INSERT INTO t1 VALUES (2, 'master');
+INSERT INTO t1 VALUES (3, 'master');
+COMMIT;
+BEGIN;
+INSERT INTO t1 VALUES (4, 'master,slave');
+INSERT INTO t1 VALUES (5, 'master,slave');
+INSERT INTO t1 VALUES (6, 'master,slave');
+COMMIT;
+SELECT * FROM t1 ORDER BY a;
+a b
+1 master
+2 master
+3 master
+4 master,slave
+5 master,slave
+6 master,slave
+**** On Slave ****
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
+START SLAVE;
+SELECT * FROM t1 ORDER BY a;
+a b
+4 master,slave
+5 master,slave
+6 master,slave
+**** On Master ****
+DELETE FROM t1;
+==== Skipping two normal transactions ====
+**** On Slave ****
+STOP SLAVE;
+**** On Master ****
+BEGIN;
+INSERT INTO t1 VALUES (1, 'master');
+INSERT INTO t1 VALUES (2, 'master');
+INSERT INTO t1 VALUES (3, 'master');
+COMMIT;
+BEGIN;
+INSERT INTO t1 VALUES (4, 'master');
+INSERT INTO t1 VALUES (5, 'master');
+INSERT INTO t1 VALUES (6, 'master');
+COMMIT;
+BEGIN;
+INSERT INTO t1 VALUES (7, 'master,slave');
+INSERT INTO t1 VALUES (8, 'master,slave');
+INSERT INTO t1 VALUES (9, 'master,slave');
+COMMIT;
+SELECT * FROM t1 ORDER BY a;
+a b
+1 master
+2 master
+3 master
+4 master
+5 master
+6 master
+7 master,slave
+8 master,slave
+9 master,slave
+**** On Slave ****
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=8;
+START SLAVE;
+SELECT * FROM t1 ORDER BY a;
+a b
+7 master,slave
+8 master,slave
+9 master,slave
+**** On Master ****
+DELETE FROM t1;
+==== Skipping without autocommit ====
+**** On Slave ****
+STOP SLAVE;
+**** On Master ****
+SET AUTOCOMMIT=0;
+INSERT INTO t1 VALUES (1, 'master');
+INSERT INTO t1 VALUES (2, 'master');
+INSERT INTO t1 VALUES (3, 'master');
+COMMIT;
+INSERT INTO t1 VALUES (4, 'master,slave');
+INSERT INTO t1 VALUES (5, 'master,slave');
+INSERT INTO t1 VALUES (6, 'master,slave');
+COMMIT;
+SELECT * FROM t1 ORDER BY a;
+a b
+1 master
+2 master
+3 master
+4 master,slave
+5 master,slave
+6 master,slave
+**** On Slave ****
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
+START SLAVE;
+SELECT * FROM t1 ORDER BY a;
+a b
+4 master,slave
+5 master,slave
+6 master,slave
+==== Rollback of transaction with non-transactional change ====
+**** On Master ****
+DELETE FROM t1;
+SET AUTOCOMMIT=1;
+**** On Slave ****
+STOP SLAVE;
+**** On Master ****
+BEGIN;
+INSERT INTO t1 VALUES (1, '');
+INSERT INTO t2 VALUES (2, 'master');
+INSERT INTO t1 VALUES (3, '');
+ROLLBACK;
+BEGIN;
+INSERT INTO t1 VALUES (4, '');
+INSERT INTO t2 VALUES (5, 'master,slave');
+INSERT INTO t1 VALUES (6, '');
+ROLLBACK;
+SELECT * FROM t1 ORDER BY a;
+a b
+SELECT * FROM t2 ORDER BY a;
+a b
+2 master
+5 master,slave
+**** On Slave ****
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
+START SLAVE;
+SELECT * FROM t1 ORDER BY a;
+a b
+SELECT * FROM t2 ORDER BY a;
+a b
+5 master,slave
+==== Cleanup ====
+**** On Master ****
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/rpl_sp_effects.result b/mysql-test/r/rpl_sp_effects.result
index bf8128d9385..70b2338d187 100644
--- a/mysql-test/r/rpl_sp_effects.result
+++ b/mysql-test/r/rpl_sp_effects.result
@@ -213,3 +213,44 @@ drop table t1;
drop function f1;
drop function f2;
drop procedure p1;
+create table t2 (b BIT(7));
+create procedure sp_bug26199(bitvalue BIT(7))
+begin
+insert into t2 set b = bitvalue;
+end //
+create function sf_bug26199(b BIT(7)) returns int
+begin
+insert into t2 values(b);
+return 0;
+end//
+call sp_bug26199(b'1110');
+call sp_bug26199('\0');
+select sf_bug26199(b'1111111');
+sf_bug26199(b'1111111')
+0
+select sf_bug26199(b'101111111');
+sf_bug26199(b'101111111')
+0
+Warnings:
+Warning 1264 Out of range value adjusted for column 'b' at row 1
+select sf_bug26199('\'');
+sf_bug26199('\'')
+0
+select hex(b) from t2;
+hex(b)
+E
+0
+7F
+7F
+27
+select hex(b) from t2;
+hex(b)
+E
+0
+7F
+7F
+27
+drop table t2;
+drop procedure sp_bug26199;
+drop function sf_bug26199;
+end of the tests
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index ed120a1bbb8..a1ba58a536c 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2827,6 +2827,14 @@ FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF
8FFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF
drop table t1;
+CREATE TABLE t1 (c0 int);
+CREATE TABLE t2 (c0 int);
+INSERT INTO t1 VALUES(@@connect_timeout);
+INSERT INTO t2 VALUES(@@connect_timeout);
+SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout);
+c0 c0
+X X
+DROP TABLE t1, t2;
End of 4.1 tests
CREATE TABLE t1 (
K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '',
@@ -3233,40 +3241,40 @@ drop table t1, t2 ,t3;
create table t1(f1 int, f2 date);
insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),
(4,'2005-10-01'),(5,'2005-12-30');
-select * from t1 where f2 >= 0;
+select * from t1 where f2 >= 0 order by f2;
f1 f2
1 2005-01-01
2 2005-09-01
3 2005-09-30
4 2005-10-01
5 2005-12-30
-select * from t1 where f2 >= '0000-00-00';
+select * from t1 where f2 >= '0000-00-00' order by f2;
f1 f2
1 2005-01-01
2 2005-09-01
3 2005-09-30
4 2005-10-01
5 2005-12-30
-select * from t1 where f2 >= '2005-09-31';
+select * from t1 where f2 >= '2005-09-31' order by f2;
f1 f2
4 2005-10-01
5 2005-12-30
-select * from t1 where f2 >= '2005-09-3a';
+select * from t1 where f2 >= '2005-09-3a' order by f2;
f1 f2
+3 2005-09-30
4 2005-10-01
5 2005-12-30
Warnings:
Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1
-select * from t1 where f2 <= '2005-09-31';
+select * from t1 where f2 <= '2005-09-31' order by f2;
f1 f2
1 2005-01-01
2 2005-09-01
3 2005-09-30
-select * from t1 where f2 <= '2005-09-3a';
+select * from t1 where f2 <= '2005-09-3a' order by f2;
f1 f2
1 2005-01-01
2 2005-09-01
-3 2005-09-30
Warnings:
Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1
drop table t1;
@@ -4077,23 +4085,241 @@ x
1
Warnings:
Warning 1466 Leading spaces are removed from name ' x'
+CREATE VIEW v1 AS SELECT 1 AS ``;
+ERROR 42000: Incorrect column name ''
CREATE VIEW v1 AS SELECT 1 AS ` `;
+ERROR 42000: Incorrect column name ' '
+CREATE VIEW v1 AS SELECT 1 AS ` `;
+ERROR 42000: Incorrect column name ' '
+CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `);
+ERROR 42000: Incorrect column name ' '
+CREATE VIEW v1 AS SELECT 1 AS ` x`;
Warnings:
-Warning 1474 Name ' ' has become ''
-SELECT `` FROM v1;
-
+Warning 1466 Leading spaces are removed from name ' x'
+SELECT `x` FROM v1;
+x
+1
+ALTER VIEW v1 AS SELECT 1 AS ` `;
+ERROR 42000: Incorrect column name ' '
+DROP VIEW v1;
+select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
+ and '2007/10/20 00:00:00 GMT';
+str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
+ and '2007/10/20 00:00:00 GMT'
1
-CREATE VIEW v2 AS SELECT 1 AS ` `;
Warnings:
-Warning 1474 Name ' ' has become ''
-SELECT `` FROM v2;
-
+Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT'
+Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT'
+select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
+str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'
1
-CREATE VIEW v3 AS SELECT 1 AS ` x`;
Warnings:
-Warning 1466 Leading spaces are removed from name ' x'
-SELECT `x` FROM v3;
-x
+Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6'
+select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
+str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'
+1
+Warnings:
+Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6'
+select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
+str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'
+1
+Warnings:
+Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6'
+select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';
+str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'
+1
+Warnings:
+Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6'
+select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';
+str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'
+1
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6'
+select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';
+str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'
+1
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6'
+select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';
+str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'
+1
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6'
+select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
+str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
1
-DROP VIEW v1, v2, v3;
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
+select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
+str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'
+0
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'
+select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';
+str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'
+1
+select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';
+str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'
+0
+select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';
+str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'
+1
+select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';
+str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'
+1
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34'
+select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';
+str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'
+1
+select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
+str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
+1
+select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
+ and '2007/10/20 00:00:00';
+str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
+ and '2007/10/20 00:00:00'
+1
+set SQL_MODE=TRADITIONAL;
+select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
+str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
+0
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34'
+select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
+str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
+0
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34'
+select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
+str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
+0
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34:00'
+select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
+ and '2007/10/20';
+str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
+ and '2007/10/20'
+0
+Warnings:
+Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/09/01' at row 1
+Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/10/20' at row 1
+set SQL_MODE=DEFAULT;
+select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
+str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
+1
+Warnings:
+Warning 1292 Truncated incorrect datetime value: ''
+select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';
+str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'
+0
+select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
+str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
+0
+select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';
+str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'
+NULL
+select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';
+str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''
+0
+Warnings:
+Warning 1292 Truncated incorrect datetime value: ''
+select str_to_date('1','%Y-%m-%d') = '1';
+str_to_date('1','%Y-%m-%d') = '1'
+0
+Warnings:
+Warning 1292 Truncated incorrect date value: '1'
+select str_to_date('1','%Y-%m-%d') = '1';
+str_to_date('1','%Y-%m-%d') = '1'
+0
+Warnings:
+Warning 1292 Truncated incorrect date value: '1'
+select str_to_date('','%Y-%m-%d') = '';
+str_to_date('','%Y-%m-%d') = ''
+0
+Warnings:
+Warning 1292 Truncated incorrect date value: ''
+select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
+str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL
+0
+select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
+str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'
+0
+select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
+str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL
+0
+CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,
+c22 INT DEFAULT NULL,
+KEY(c21, c22));
+CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0,
+c32 INT DEFAULT NULL,
+c33 INT NOT NULL,
+c34 INT UNSIGNED DEFAULT 0,
+KEY (c33, c34, c32));
+INSERT INTO t1 values (),(),(),(),();
+INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;
+INSERT INTO t3 VALUES (1, 1, 1, 0),
+(2, 2, 0, 0),
+(3, 3, 1, 0),
+(4, 4, 0, 0),
+(5, 5, 1, 0);
+SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
+t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
+t3.c33 = 1 AND t2.c22 in (1, 3)
+ORDER BY c32;
+c32
+1
+1
+3
+3
+5
+5
+SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND
+t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND
+t3.c33 = 1 AND t2.c22 in (1, 3)
+ORDER BY c32 DESC;
+c32
+5
+5
+3
+3
+1
+1
+DROP TABLE t1, t2, t3;
+
+#
+# Bug#30736: Row Size Too Large Error Creating a Table and
+# Inserting Data.
+#
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+
+CREATE TABLE t1(
+c1 DECIMAL(10, 2),
+c2 FLOAT);
+
+INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5);
+
+CREATE TABLE t2(
+c3 DECIMAL(10, 2))
+SELECT
+c1 * c2 AS c3
+FROM t1;
+
+SELECT * FROM t1;
+c1 c2
+0.00 1
+2.00 3
+4.00 5
+
+SELECT * FROM t2;
+c3
+0.00
+6.00
+20.00
+
+DROP TABLE t1;
+DROP TABLE t2;
+
End of 5.0 tests
diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result
index 3052bae8e97..1ef35b051d6 100644
--- a/mysql-test/r/skip_grants.result
+++ b/mysql-test/r/skip_grants.result
@@ -70,3 +70,8 @@ count(*)
select count(*) from information_schema.USER_PRIVILEGES;
count(*)
0
+CREATE FUNCTION a RETURNS STRING SONAME '';
+ERROR HY000: Can't initialize function 'a'; UDFs are unavailable with the --skip-grant-tables option
+DROP FUNCTION a;
+ERROR 42000: FUNCTION test.a does not exist
+End of 5.0 tests
diff --git a/mysql-test/r/sp_trans_log.result b/mysql-test/r/sp_trans_log.result
index 96e6f76b23c..9b644798079 100644
--- a/mysql-test/r/sp_trans_log.result
+++ b/mysql-test/r/sp_trans_log.result
@@ -12,8 +12,10 @@ end|
reset master|
insert into t2 values (bug23333(),1)|
ERROR 23000: Duplicate entry '1' for key 1
-show binlog events from 98 /* with fixes for #23333 will show there is the query */|
+show binlog events from 98 /* with fixes for #23333 will show there are 2 queries */|
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query 1 # #
+master-bin.000001 # Query 1 # #
select count(*),@a from t1 /* must be 1,1 */|
count(*) @a
1 1
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index be99bdb1afc..1c450269809 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4139,4 +4139,64 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
3
DROP TABLE t1,t2;
+CREATE TABLE t1 (a1 INT, a2 INT);
+CREATE TABLE t2 (b1 INT, b2 INT);
+INSERT INTO t1 VALUES (100, 200);
+INSERT INTO t1 VALUES (101, 201);
+INSERT INTO t2 VALUES (101, 201);
+INSERT INTO t2 VALUES (103, 203);
+SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
+((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL
+0
+0
+DROP TABLE t1, t2;
+CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
+INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+s1 s2
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+s1 s2
+CREATE INDEX I1 ON t1 (s1);
+CREATE INDEX I2 ON t1 (s2);
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+s1 s2
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+s1 s2
+TRUNCATE t1;
+INSERT INTO t1 VALUES (0x41,0x41);
+SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
+s1 s2
+DROP TABLE t1;
+CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
+CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
+CREATE TABLE t3 (a3 BINARY(2) default '0');
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t2 VALUES (1),(2),(3);
+INSERT INTO t3 VALUES (1),(2),(3);
+SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
+LEFT(t2.a2, 1)
+1
+2
+3
+SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
+a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)
+1 0
+2 0
+3 0
+4 0
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
+CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
+CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
+INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
+INSERT INTO t2 VALUES (2), (3), (4), (5);
+INSERT INTO t3 VALUES (10), (20), (30);
+SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
+LEFT(t1.a1,1)
+1
+2
+3
+SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
+a2
+DROP TABLE t1, t2, t3;
End of 5.0 tests.
diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result
index 18299bf4298..833adbeb851 100644
--- a/mysql-test/r/symlink.result
+++ b/mysql-test/r/symlink.result
@@ -99,6 +99,12 @@ t1 CREATE TABLE `t1` (
`b` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
+CREATE TABLE t1(a INT)
+DATA DIRECTORY='TEST_DIR/master-data/mysql'
+INDEX DIRECTORY='TEST_DIR/master-data/mysql';
+RENAME TABLE t1 TO user;
+ERROR HY000: Can't create/write to file 'TEST_DIR/master-data/mysql/user.MYI' (Errcode: 17)
+DROP TABLE t1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 9f51fc0371c..bd2a43569dd 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -136,3 +136,82 @@ d dt ts
0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00
2001-11-11 2001-11-11 00:00:00 2001-11-11 00:00:00
drop table t1;
+CREATE TABLE t1 (
+a INT
+);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (NULL);
+SELECT str_to_date( '', a ) FROM t1;
+str_to_date( '', a )
+0000-00-00 00:00:00
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b));
+INSERT INTO t1 VALUES (DATE(NOW()), 1);
+SELECT COUNT(*) FROM t1 WHERE a = NOW();
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+INSERT INTO t1 VALUES (DATE(NOW()), 2);
+SELECT COUNT(*) FROM t1 WHERE a = NOW();
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1;
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+ALTER TABLE t1 DROP PRIMARY KEY;
+SELECT COUNT(*) FROM t1 WHERE a = NOW();
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+CREATE TABLE t2 (a DATE);
+CREATE INDEX i ON t1 (a);
+INSERT INTO t1 VALUES ('0000-00-00'),('0000-00-00');
+INSERT INTO t2 VALUES ('0000-00-00'),('0000-00-00');
+SELECT * FROM t1 WHERE a = '0000-00-00';
+a
+0000-00-00
+0000-00-00
+SELECT * FROM t2 WHERE a = '0000-00-00';
+a
+0000-00-00
+0000-00-00
+SET SQL_MODE=TRADITIONAL;
+EXPLAIN SELECT * FROM t1 WHERE a = '0000-00-00';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i i 4 const 1 Using where; Using index
+Warnings:
+Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1
+Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1
+SELECT * FROM t1 WHERE a = '0000-00-00';
+a
+0000-00-00
+0000-00-00
+Warnings:
+Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1
+Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1
+Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1
+SELECT * FROM t2 WHERE a = '0000-00-00';
+a
+0000-00-00
+0000-00-00
+Warnings:
+Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1
+Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1
+INSERT INTO t1 VALUES ('0000-00-00');
+ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
+SET SQL_MODE=DEFAULT;
+DROP TABLE t1,t2;
+End of 5.0 tests
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 590e9b7c9e0..73edfb75ff6 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -59,6 +59,8 @@ t
drop table t1;
CREATE TABLE t1 (a timestamp, b date, c time, d datetime);
insert into t1 (b,c,d) values(now(),curtime(),now());
+Warnings:
+Note 1265 Data truncated for column 'b' at row 1
select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1;
date_format(a,"%Y-%m-%d")=b right(a+0,6)=c+0 a=d+0
1 1 1
@@ -427,6 +429,67 @@ f1
Warnings:
Warning 1292 Incorrect datetime value: '2007010100000' for column 'f1' at row 1
drop table t1;
+#
+# Bug#27216: functions with parameters of different date types may
+# return wrong type of the result.
+#
+create table t1 (f1 date, f2 datetime, f3 varchar(20));
+create table t2 as select coalesce(f1,f1) as f4 from t1;
+desc t2;
+Field Type Null Key Default Extra
+f4 date YES NULL
+create table t3 as select coalesce(f1,f2) as f4 from t1;
+desc t3;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t4 as select coalesce(f2,f2) as f4 from t1;
+desc t4;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t5 as select coalesce(f1,f3) as f4 from t1;
+desc t5;
+Field Type Null Key Default Extra
+f4 varbinary(20) YES NULL
+create table t6 as select coalesce(f2,f3) as f4 from t1;
+desc t6;
+Field Type Null Key Default Extra
+f4 varbinary(20) YES NULL
+create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1;
+desc t7;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4
+from t1;
+desc t8;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t9 as select case when 1 then cast('01-01-01' as date)
+when 0 then cast('01-01-01' as date) end as f4 from t1;
+desc t9;
+Field Type Null Key Default Extra
+f4 date YES NULL
+create table t10 as select case when 1 then cast('01-01-01' as datetime)
+when 0 then cast('01-01-01' as datetime) end as f4 from t1;
+desc t10;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t11 as select if(1, cast('01-01-01' as datetime),
+cast('01-01-01' as date)) as f4 from t1;
+desc t11;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t12 as select least(cast('01-01-01' as datetime),
+cast('01-01-01' as date)) as f4 from t1;
+desc t12;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+create table t13 as select ifnull(cast('01-01-01' as datetime),
+cast('01-01-01' as date)) as f4 from t1;
+desc t13;
+Field Type Null Key Default Extra
+f4 datetime YES NULL
+drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13;
+###################################################################
create table t1 (f1 time);
insert into t1 set f1 = '45:44:44';
insert into t1 set f1 = '15:44:44';
diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
index 65a72e0aa9b..a438755ce6b 100644
--- a/mysql-test/r/type_decimal.result
+++ b/mysql-test/r/type_decimal.result
@@ -800,10 +800,93 @@ SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1;
ROUND(qty,3) dps ROUND(qty,dps)
1.133 3 1.133
DROP TABLE t1;
+SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%';
+%
+0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
+SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()';
+MOD()
+0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
create table t1 (f1 decimal(6,6),f2 decimal(6,6) zerofill);
insert into t1 values (-0.123456,0.123456);
select group_concat(f1),group_concat(f2) from t1;
group_concat(f1) group_concat(f2)
-0.123456 0.123456
drop table t1;
+create table t1 (
+ua_id decimal(22,0) not null,
+ua_invited_by_id decimal(22,0) default NULL,
+primary key(ua_id)
+);
+insert into t1 values (123, NULL), (456, NULL);
+this must not produce error 1048:
+select * from t1 where ua_invited_by_id not in (select ua_id from t1);
+ua_id ua_invited_by_id
+drop table t1;
+DROP TABLE IF EXISTS t3;
+DROP TABLE IF EXISTS t4;
+CREATE TABLE t1( a NUMERIC, b INT );
+INSERT INTO t1 VALUES (123456, 40), (123456, 40);
+SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c;
+c
+123456
+123456
+SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c;
+c
+123456
+123456
+SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c;
+c
+123456.000000000000000000000000000000
+123456.000000000000000000000000000000
+CREATE TABLE t2( a NUMERIC, b INT );
+INSERT INTO t2 VALUES (123456, 100);
+SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c;
+c
+123456
+SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c;
+c
+123456
+CREATE TABLE t3( a DECIMAL, b INT );
+INSERT INTO t3 VALUES (123456, 40), (123456, 40);
+SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c;
+c
+123456
+123456
+SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c;
+c
+123456
+123456
+SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c;
+c
+123456.000000000000000000000000000000
+123456.000000000000000000000000000000
+CREATE TABLE t4( a DECIMAL, b INT );
+INSERT INTO t4 VALUES (123456, 40), (123456, 40);
+SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c;
+c
+123456
+123456
+SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c;
+c
+123456
+123456
+SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c;
+c
+123456.000000000000000000000000000000
+123456.000000000000000000000000000000
+delete from t1;
+INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5);
+Warnings:
+Note 1265 Data truncated for column 'a' at row 2
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` decimal(10,0) default NULL,
+ `b` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select round(a,b) as c from t1 order by c;
+c
+1000
+1234567890
+DROP TABLE t1, t2, t3, t4;
End of 5.0 tests
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index 9e52fbeac1a..47cd96b90b7 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -675,6 +675,9 @@ set @@query_prealloc_size = @test;
select @@query_prealloc_size = @test;
@@query_prealloc_size = @test
1
+set global sql_mode=repeat('a',80);
+ERROR 42000: Variable 'sql_mode' can't be set to the value of 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
+End of 4.1 tests
create table t1 (a int);
select a into @x from t1;
Warnings:
@@ -793,6 +796,22 @@ ERROR HY000: Variable 'hostname' is a read only variable
show variables like 'hostname';
Variable_name Value
hostname #
+SHOW VARIABLES LIKE 'log';
+Variable_name Value
+log ON
+SELECT @@log;
+@@log
+1
+SET GLOBAL log=0;
+ERROR HY000: Variable 'log' is a read only variable
+SHOW VARIABLES LIKE 'log_slow_queries';
+Variable_name Value
+log_slow_queries ON
+SELECT @@log_slow_queries;
+@@log_slow_queries
+1
+SET GLOBAL log_slow_queries=0;
+ERROR HY000: Variable 'log_slow_queries' is a read only variable
End of 5.0 tests
set global binlog_cache_size =@my_binlog_cache_size;
set global connect_timeout =@my_connect_timeout;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index c5c6b675146..0e3d650c571 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -625,7 +625,7 @@ drop table t1;
create table t1 (a int, b int);
create view v1 as select a, sum(b) from t1 group by a;
select b from v1 use index (some_index) where b=1;
-ERROR HY000: Key 'some_index' doesn't exist in table 'v1'
+ERROR HY000: Incorrect usage of USE INDEX and VIEW
drop view v1;
drop table t1;
create table t1 (col1 char(5),col2 char(5));
@@ -2706,18 +2706,19 @@ CREATE TABLE t1(
fName varchar(25) NOT NULL,
lName varchar(25) NOT NULL,
DOB date NOT NULL,
+test_date date NOT NULL,
uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY);
-INSERT INTO t1(fName, lName, DOB) VALUES
-('Hank', 'Hill', '1964-09-29'),
-('Tom', 'Adams', '1908-02-14'),
-('Homer', 'Simpson', '1968-03-05');
+INSERT INTO t1(fName, lName, DOB, test_date) VALUES
+('Hank', 'Hill', '1964-09-29', '2007-01-01'),
+('Tom', 'Adams', '1908-02-14', '2007-01-01'),
+('Homer', 'Simpson', '1968-03-05', '2007-01-01');
CREATE VIEW v1 AS
-SELECT (year(now())-year(DOB)) AS Age
+SELECT (year(test_date)-year(DOB)) AS Age
FROM t1 HAVING Age < 75;
SHOW CREATE VIEW v1;
View Create View
-v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(now()) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75)
-SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75;
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(`t1`.`test_date`) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75)
+SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75;
Age
43
39
@@ -3562,4 +3563,43 @@ table_name is_updatable
v1 NO
drop view v1;
drop table t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM v1 USE KEY(non_existant);
+ERROR HY000: Incorrect usage of USE INDEX and VIEW
+SELECT * FROM v1 FORCE KEY(non_existant);
+ERROR HY000: Incorrect usage of FORCE INDEX and VIEW
+SELECT * FROM v1 IGNORE KEY(non_existant);
+ERROR HY000: Incorrect usage of IGNORE INDEX and VIEW
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0,
+PRIMARY KEY(a), KEY (b));
+INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a;
+SHOW CREATE VIEW v1;
+View Create View
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` FORCE INDEX (PRIMARY,`b`) order by `t1`.`a`
+EXPLAIN SELECT * FROM v1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 15
+CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a;
+SHOW CREATE VIEW v2;
+View Create View
+v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` USE INDEX () order by `t1`.`a`
+EXPLAIN SELECT * FROM v2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort
+CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a;
+SHOW CREATE VIEW v3;
+View Create View
+v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` IGNORE INDEX (`b`) order by `t1`.`a`
+EXPLAIN SELECT * FROM v3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort
+DROP VIEW v1;
+DROP VIEW v2;
+DROP VIEW v3;
+DROP TABLE t1;
End of 5.0 tests.
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index 0f9ce47dec6..eef61c65fb8 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -776,15 +776,60 @@ GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost;
GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost;
GRANT SELECT ON db26813.t1 TO u26813@localhost;
ALTER VIEW v1 AS SELECT f2 FROM t1;
-ERROR 42000: CREATE VIEW command denied to user 'u26813'@'localhost' for table 'v1'
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
ALTER VIEW v2 AS SELECT f2 FROM t1;
-ERROR 42000: DROP command denied to user 'u26813'@'localhost' for table 'v2'
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
ALTER VIEW v3 AS SELECT f2 FROM t1;
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
SHOW CREATE VIEW v3;
View Create View
-v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f2` AS `f2` from `t1`
+v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f1` AS `f1` from `t1`
DROP USER u26813@localhost;
DROP DATABASE db26813;
+#
+# Bug#29908: A user can gain additional access through the ALTER VIEW.
+#
+CREATE DATABASE mysqltest_29908;
+USE mysqltest_29908;
+CREATE TABLE t1(f1 INT, f2 INT);
+CREATE USER u29908_1@localhost;
+CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1;
+CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS
+SELECT f1 FROM t1;
+GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost;
+GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost;
+GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost;
+CREATE USER u29908_2@localhost;
+GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost;
+GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost;
+GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost;
+ALTER VIEW v1 AS SELECT f2 FROM t1;
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
+ALTER VIEW v2 AS SELECT f2 FROM t1;
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
+SHOW CREATE VIEW v2;
+View Create View
+v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1`
+ALTER VIEW v1 AS SELECT f2 FROM t1;
+SHOW CREATE VIEW v1;
+View Create View
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f2` AS `f2` from `t1`
+ALTER VIEW v2 AS SELECT f2 FROM t1;
+SHOW CREATE VIEW v2;
+View Create View
+v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f2` AS `f2` from `t1`
+ALTER VIEW v1 AS SELECT f1 FROM t1;
+SHOW CREATE VIEW v1;
+View Create View
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1`
+ALTER VIEW v2 AS SELECT f1 FROM t1;
+SHOW CREATE VIEW v2;
+View Create View
+v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1`
+DROP USER u29908_1@localhost;
+DROP USER u29908_2@localhost;
+DROP DATABASE mysqltest_29908;
+#######################################################################
DROP DATABASE IF EXISTS mysqltest1;
DROP DATABASE IF EXISTS mysqltest2;
CREATE DATABASE mysqltest1;