diff options
29 files changed, 270 insertions, 91 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 685e8ddd6bc..9d31ceab4d1 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -438,6 +438,33 @@ CALL p(); id drop procedure p; drop temporary table t1; +# +# MDEV-5143: update of a joined table with a nested subquery with +# a syntax error crashes mysqld with signal 11 +# +create table t1 (id int(11) not null auto_increment, val varchar(100) null,primary key (id)); +create table t2 (id int(11) not null auto_increment, val varchar(100) null,primary key (id)); +insert into t1 (val) values('a'); +insert into t2 (val) values('1'); +update +( +select +val +from +( +select +v.val +from +t2 wrong_table_alias +) t4 +) t3 +inner join t1 on +t1.id=t3.val +set +t1.val=t3.val +; +ERROR 42S22: Unknown column 'v.val' in 'field list' +drop table t1, t2; # End of 5.3 tests # # Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table, diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index d06d0e51a56..6c0d77b36b0 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2415,6 +2415,11 @@ a 200 set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +# +# MDEV-5104 crash in Item_field::used_tables with broken order by +# +(select 1 order by x(y)) order by 1; +ERROR 42S22: Unknown column 'y' in 'order clause' # End of 5.3 tests # # Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00 diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index a706ae2c671..45b406a5ad0 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -594,4 +594,49 @@ a b c 4 2 1 5 1 1 DROP TABLE t1; +# +# MDEV-5102 : MySQL Bug 69851 +# +CREATE TABLE t1 ( +`col1` bigint(20) unsigned NOT NULL , +`col2` bigint(20) unsigned NOT NULL , +`col3` datetime NOT NULL , +PRIMARY KEY (`col3`), +KEY (`col1`), +KEY (`col2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +PARTITION BY RANGE (TO_DAYS(col3)) +( +PARTITION p_20130310 VALUES LESS THAN (735303) ENGINE = InnoDB, +PARTITION p_20130311 VALUES LESS THAN (735304) ENGINE = InnoDB, +PARTITION p_20130312 VALUES LESS THAN (735305) ENGINE = InnoDB +); +INSERT INTO `t1` VALUES (2,96,'2013-03-08 16:28:05'); +INSERT INTO `t1` VALUES (1,2,'2013-03-08 16:47:39'); +INSERT INTO `t1` VALUES (1,2,'2013-03-08 16:50:27'); +INSERT INTO `t1` VALUES (1,2,'2013-03-11 16:33:04'); +INSERT INTO `t1` VALUES (1,2,'2013-03-11 16:33:24'); +INSERT INTO `t1` VALUES (2,2,'2013-03-12 10:11:48'); +SELECT * FROM t1 WHERE col1 = 1 AND col2 = 2 +AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' +GROUP BY 1, 2, 3; +col1 col2 col3 +1 2 2013-03-08 16:47:39 +1 2 2013-03-08 16:50:27 +1 2 2013-03-11 16:33:04 +1 2 2013-03-11 16:33:24 +EXPLAIN SELECT * FROM t1 WHERE col1 = 1 AND col2 = 2 +AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' +GROUP BY 1, 2, 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge PRIMARY,col1,col2 col1,col2 8,8 NULL # Using intersect(col1,col2); Using where; Using index; Using filesort +SELECT * FROM t1 USE INDEX () WHERE col1 = 1 AND col2 = 2 +AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' +GROUP BY 1, 2, 3; +col1 col2 col3 +1 2 2013-03-08 16:47:39 +1 2 2013-03-08 16:50:27 +1 2 2013-03-11 16:33:04 +1 2 2013-03-11 16:33:24 +DROP TABLE t1; set global default_storage_engine=default; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cb84e8c7c2e..b15b9c262f7 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -193,7 +193,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`)) order by `a`) +Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`))) select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 diff --git a/mysql-test/r/subselect_exists_to_in.result b/mysql-test/r/subselect_exists_to_in.result index 65d1ed720a9..01d67686003 100644 --- a/mysql-test/r/subselect_exists_to_in.result +++ b/mysql-test/r/subselect_exists_to_in.result @@ -197,7 +197,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`)) order by `a`) +Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`))) select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 92093c34024..9ba4c1c0279 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -200,7 +200,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`)) order by `a`) +Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`))) select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 2407060d6ad..648a2516bee 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -196,7 +196,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`)) order by `a`) +Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`))) select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 0b3bc405c65..70c9be1eaf4 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -199,7 +199,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`)) order by `a`) +Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`))) select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index bd1f1155b57..293a9f0083c 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -196,7 +196,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`)) order by `a`) +Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`))) select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result index 5504ec7efc2..06d1c9c5da6 100644 --- a/mysql-test/suite/funcs_1/r/innodb_func_view.result +++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result @@ -28,7 +28,7 @@ my_binary_30 = '', my_varbinary_1000 = '', my_datetime = '0001-01-01 00:00:00', my_date = '0001-01-01', -my_timestamp = '1970-01-01 03:00:01', +my_timestamp = '1970-01-01 14:00:01', my_time = '-838:59:59', my_year = '1901', my_bigint = -9223372036854775808, @@ -1276,7 +1276,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 136 OR select_id IS NULL order by id; IFNULL(my_timestamp,'IS_NULL') my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -1288,7 +1288,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 136 OR select_id IS NULL) order by id; IFNULL(my_timestamp,'IS_NULL') my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -1595,7 +1595,7 @@ WHERE select_id = 124 OR select_id IS NULL order by id; IF(my_timestamp IS NULL, 'IS NULL', 'IS NOT NULL') my_timestamp id IS NOT NULL 0000-00-00 00:00:00 1 -IS NOT NULL 1970-01-01 03:00:01 2 +IS NOT NULL 1970-01-01 14:00:01 2 IS NOT NULL 2038-01-01 02:59:59 3 IS NOT NULL 2004-02-29 23:59:59 4 IS NOT NULL 2005-06-28 10:00:00 5 @@ -1609,7 +1609,7 @@ WHERE select_id = 124 OR select_id IS NULL) order by id; IF(my_timestamp IS NULL, 'IS NULL', 'IS NOT NULL') my_timestamp id IS NOT NULL 0000-00-00 00:00:00 1 -IS NOT NULL 1970-01-01 03:00:01 2 +IS NOT NULL 1970-01-01 14:00:01 2 IS NOT NULL 2038-01-01 02:59:59 3 IS NOT NULL 2004-02-29 23:59:59 4 IS NOT NULL 2005-06-28 10:00:00 5 @@ -1936,7 +1936,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 112 OR select_id IS NULL order by id; IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id IS NOT TRUE 0000-00-00 00:00:00 1 -IS TRUE 1970-01-01 03:00:01 2 +IS TRUE 1970-01-01 14:00:01 2 IS TRUE 2038-01-01 02:59:59 3 IS TRUE 2004-02-29 23:59:59 4 IS TRUE 2005-06-28 10:00:00 5 @@ -1948,7 +1948,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 112 OR select_id IS NULL) order by id; IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id IS NOT TRUE 0000-00-00 00:00:00 1 -IS TRUE 1970-01-01 03:00:01 2 +IS TRUE 1970-01-01 14:00:01 2 IS TRUE 2038-01-01 02:59:59 3 IS TRUE 2004-02-29 23:59:59 4 IS TRUE 2005-06-28 10:00:00 5 @@ -2488,7 +2488,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 92 OR select_id IS NULL order by id; CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -2500,7 +2500,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 92 OR select_id IS NULL) order by id; CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -2866,7 +2866,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 80 OR select_id IS NULL order by id; CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -2878,7 +2878,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 80 OR select_id IS NULL) order by id; CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -3224,7 +3224,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 68 OR select_id IS NULL order by id; CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id 0.00 0000-00-00 00:00:00 1 -19700101030001.00 1970-01-01 03:00:01 2 +19700101140001.00 1970-01-01 14:00:01 2 20380101025959.00 2038-01-01 02:59:59 3 20040229235959.00 2004-02-29 23:59:59 4 20050628100000.00 2005-06-28 10:00:00 5 @@ -3236,7 +3236,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 68 OR select_id IS NULL) order by id; CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id 0.00 0000-00-00 00:00:00 1 -19700101030001.00 1970-01-01 03:00:01 2 +19700101140001.00 1970-01-01 14:00:01 2 20380101025959.00 2038-01-01 02:59:59 3 20040229235959.00 2004-02-29 23:59:59 4 20050628100000.00 2005-06-28 10:00:00 5 @@ -3616,7 +3616,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 56 OR select_id IS NULL order by id; CAST(my_timestamp AS TIME) my_timestamp id 00:00:00 0000-00-00 00:00:00 1 -03:00:01 1970-01-01 03:00:01 2 +14:00:01 1970-01-01 14:00:01 2 02:59:59 2038-01-01 02:59:59 3 23:59:59 2004-02-29 23:59:59 4 10:00:00 2005-06-28 10:00:00 5 @@ -3628,7 +3628,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 56 OR select_id IS NULL) order by id; CAST(my_timestamp AS TIME) my_timestamp id 00:00:00 0000-00-00 00:00:00 1 -03:00:01 1970-01-01 03:00:01 2 +14:00:01 1970-01-01 14:00:01 2 02:59:59 2038-01-01 02:59:59 3 23:59:59 2004-02-29 23:59:59 4 10:00:00 2005-06-28 10:00:00 5 @@ -3966,7 +3966,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 45 OR select_id IS NULL order by id; CAST(my_timestamp AS DATETIME) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -3978,7 +3978,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 45 OR select_id IS NULL) order by id; CAST(my_timestamp AS DATETIME) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -4328,7 +4328,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 34 OR select_id IS NULL order by id; CAST(my_timestamp AS DATE) my_timestamp id 0000-00-00 0000-00-00 00:00:00 1 -1970-01-01 1970-01-01 03:00:01 2 +1970-01-01 1970-01-01 14:00:01 2 2038-01-01 2038-01-01 02:59:59 3 2004-02-29 2004-02-29 23:59:59 4 2005-06-28 2005-06-28 10:00:00 5 @@ -4340,7 +4340,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 34 OR select_id IS NULL) order by id; CAST(my_timestamp AS DATE) my_timestamp id 0000-00-00 0000-00-00 00:00:00 1 -1970-01-01 1970-01-01 03:00:01 2 +1970-01-01 1970-01-01 14:00:01 2 2038-01-01 2038-01-01 02:59:59 3 2004-02-29 2004-02-29 23:59:59 4 2005-06-28 2005-06-28 10:00:00 5 @@ -4686,7 +4686,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 23 OR select_id IS NULL order by id; CAST(my_timestamp AS CHAR) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -4698,7 +4698,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 23 OR select_id IS NULL) order by id; CAST(my_timestamp AS CHAR) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -4998,7 +4998,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 11 OR select_id IS NULL order by id; CAST(my_timestamp AS BINARY) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -5010,7 +5010,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 11 OR select_id IS NULL) order by id; CAST(my_timestamp AS BINARY) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result index 7f7baf65d1f..bb092355c41 100644 --- a/mysql-test/suite/funcs_1/r/memory_func_view.result +++ b/mysql-test/suite/funcs_1/r/memory_func_view.result @@ -29,7 +29,7 @@ my_binary_30 = '', my_varbinary_1000 = '', my_datetime = '0001-01-01 00:00:00', my_date = '0001-01-01', -my_timestamp = '1970-01-01 03:00:01', +my_timestamp = '1970-01-01 14:00:01', my_time = '-838:59:59', my_year = '1901', my_bigint = -9223372036854775808, @@ -1277,7 +1277,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 136 OR select_id IS NULL order by id; IFNULL(my_timestamp,'IS_NULL') my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -1289,7 +1289,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 136 OR select_id IS NULL) order by id; IFNULL(my_timestamp,'IS_NULL') my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -1596,7 +1596,7 @@ WHERE select_id = 124 OR select_id IS NULL order by id; IF(my_timestamp IS NULL, 'IS NULL', 'IS NOT NULL') my_timestamp id IS NOT NULL 0000-00-00 00:00:00 1 -IS NOT NULL 1970-01-01 03:00:01 2 +IS NOT NULL 1970-01-01 14:00:01 2 IS NOT NULL 2038-01-01 02:59:59 3 IS NOT NULL 2004-02-29 23:59:59 4 IS NOT NULL 2005-06-28 10:00:00 5 @@ -1610,7 +1610,7 @@ WHERE select_id = 124 OR select_id IS NULL) order by id; IF(my_timestamp IS NULL, 'IS NULL', 'IS NOT NULL') my_timestamp id IS NOT NULL 0000-00-00 00:00:00 1 -IS NOT NULL 1970-01-01 03:00:01 2 +IS NOT NULL 1970-01-01 14:00:01 2 IS NOT NULL 2038-01-01 02:59:59 3 IS NOT NULL 2004-02-29 23:59:59 4 IS NOT NULL 2005-06-28 10:00:00 5 @@ -1937,7 +1937,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 112 OR select_id IS NULL order by id; IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id IS NOT TRUE 0000-00-00 00:00:00 1 -IS TRUE 1970-01-01 03:00:01 2 +IS TRUE 1970-01-01 14:00:01 2 IS TRUE 2038-01-01 02:59:59 3 IS TRUE 2004-02-29 23:59:59 4 IS TRUE 2005-06-28 10:00:00 5 @@ -1949,7 +1949,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 112 OR select_id IS NULL) order by id; IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id IS NOT TRUE 0000-00-00 00:00:00 1 -IS TRUE 1970-01-01 03:00:01 2 +IS TRUE 1970-01-01 14:00:01 2 IS TRUE 2038-01-01 02:59:59 3 IS TRUE 2004-02-29 23:59:59 4 IS TRUE 2005-06-28 10:00:00 5 @@ -2489,7 +2489,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 92 OR select_id IS NULL order by id; CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -2501,7 +2501,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 92 OR select_id IS NULL) order by id; CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -2867,7 +2867,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 80 OR select_id IS NULL order by id; CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -2879,7 +2879,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 80 OR select_id IS NULL) order by id; CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -3225,7 +3225,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 68 OR select_id IS NULL order by id; CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id 0.00 0000-00-00 00:00:00 1 -19700101030001.00 1970-01-01 03:00:01 2 +19700101140001.00 1970-01-01 14:00:01 2 20380101025959.00 2038-01-01 02:59:59 3 20040229235959.00 2004-02-29 23:59:59 4 20050628100000.00 2005-06-28 10:00:00 5 @@ -3237,7 +3237,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 68 OR select_id IS NULL) order by id; CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id 0.00 0000-00-00 00:00:00 1 -19700101030001.00 1970-01-01 03:00:01 2 +19700101140001.00 1970-01-01 14:00:01 2 20380101025959.00 2038-01-01 02:59:59 3 20040229235959.00 2004-02-29 23:59:59 4 20050628100000.00 2005-06-28 10:00:00 5 @@ -3617,7 +3617,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 56 OR select_id IS NULL order by id; CAST(my_timestamp AS TIME) my_timestamp id 00:00:00 0000-00-00 00:00:00 1 -03:00:01 1970-01-01 03:00:01 2 +14:00:01 1970-01-01 14:00:01 2 02:59:59 2038-01-01 02:59:59 3 23:59:59 2004-02-29 23:59:59 4 10:00:00 2005-06-28 10:00:00 5 @@ -3629,7 +3629,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 56 OR select_id IS NULL) order by id; CAST(my_timestamp AS TIME) my_timestamp id 00:00:00 0000-00-00 00:00:00 1 -03:00:01 1970-01-01 03:00:01 2 +14:00:01 1970-01-01 14:00:01 2 02:59:59 2038-01-01 02:59:59 3 23:59:59 2004-02-29 23:59:59 4 10:00:00 2005-06-28 10:00:00 5 @@ -3967,7 +3967,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 45 OR select_id IS NULL order by id; CAST(my_timestamp AS DATETIME) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -3979,7 +3979,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 45 OR select_id IS NULL) order by id; CAST(my_timestamp AS DATETIME) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -4329,7 +4329,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 34 OR select_id IS NULL order by id; CAST(my_timestamp AS DATE) my_timestamp id 0000-00-00 0000-00-00 00:00:00 1 -1970-01-01 1970-01-01 03:00:01 2 +1970-01-01 1970-01-01 14:00:01 2 2038-01-01 2038-01-01 02:59:59 3 2004-02-29 2004-02-29 23:59:59 4 2005-06-28 2005-06-28 10:00:00 5 @@ -4341,7 +4341,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 34 OR select_id IS NULL) order by id; CAST(my_timestamp AS DATE) my_timestamp id 0000-00-00 0000-00-00 00:00:00 1 -1970-01-01 1970-01-01 03:00:01 2 +1970-01-01 1970-01-01 14:00:01 2 2038-01-01 2038-01-01 02:59:59 3 2004-02-29 2004-02-29 23:59:59 4 2005-06-28 2005-06-28 10:00:00 5 @@ -4687,7 +4687,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 23 OR select_id IS NULL order by id; CAST(my_timestamp AS CHAR) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -4699,7 +4699,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 23 OR select_id IS NULL) order by id; CAST(my_timestamp AS CHAR) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -4999,7 +4999,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 11 OR select_id IS NULL order by id; CAST(my_timestamp AS BINARY) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -5011,7 +5011,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 11 OR select_id IS NULL) order by id; CAST(my_timestamp AS BINARY) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result index 7f7baf65d1f..bb092355c41 100644 --- a/mysql-test/suite/funcs_1/r/myisam_func_view.result +++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result @@ -29,7 +29,7 @@ my_binary_30 = '', my_varbinary_1000 = '', my_datetime = '0001-01-01 00:00:00', my_date = '0001-01-01', -my_timestamp = '1970-01-01 03:00:01', +my_timestamp = '1970-01-01 14:00:01', my_time = '-838:59:59', my_year = '1901', my_bigint = -9223372036854775808, @@ -1277,7 +1277,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 136 OR select_id IS NULL order by id; IFNULL(my_timestamp,'IS_NULL') my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -1289,7 +1289,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 136 OR select_id IS NULL) order by id; IFNULL(my_timestamp,'IS_NULL') my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -1596,7 +1596,7 @@ WHERE select_id = 124 OR select_id IS NULL order by id; IF(my_timestamp IS NULL, 'IS NULL', 'IS NOT NULL') my_timestamp id IS NOT NULL 0000-00-00 00:00:00 1 -IS NOT NULL 1970-01-01 03:00:01 2 +IS NOT NULL 1970-01-01 14:00:01 2 IS NOT NULL 2038-01-01 02:59:59 3 IS NOT NULL 2004-02-29 23:59:59 4 IS NOT NULL 2005-06-28 10:00:00 5 @@ -1610,7 +1610,7 @@ WHERE select_id = 124 OR select_id IS NULL) order by id; IF(my_timestamp IS NULL, 'IS NULL', 'IS NOT NULL') my_timestamp id IS NOT NULL 0000-00-00 00:00:00 1 -IS NOT NULL 1970-01-01 03:00:01 2 +IS NOT NULL 1970-01-01 14:00:01 2 IS NOT NULL 2038-01-01 02:59:59 3 IS NOT NULL 2004-02-29 23:59:59 4 IS NOT NULL 2005-06-28 10:00:00 5 @@ -1937,7 +1937,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 112 OR select_id IS NULL order by id; IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id IS NOT TRUE 0000-00-00 00:00:00 1 -IS TRUE 1970-01-01 03:00:01 2 +IS TRUE 1970-01-01 14:00:01 2 IS TRUE 2038-01-01 02:59:59 3 IS TRUE 2004-02-29 23:59:59 4 IS TRUE 2005-06-28 10:00:00 5 @@ -1949,7 +1949,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 112 OR select_id IS NULL) order by id; IF(my_timestamp, 'IS TRUE', 'IS NOT TRUE') my_timestamp id IS NOT TRUE 0000-00-00 00:00:00 1 -IS TRUE 1970-01-01 03:00:01 2 +IS TRUE 1970-01-01 14:00:01 2 IS TRUE 2038-01-01 02:59:59 3 IS TRUE 2004-02-29 23:59:59 4 IS TRUE 2005-06-28 10:00:00 5 @@ -2489,7 +2489,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 92 OR select_id IS NULL order by id; CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -2501,7 +2501,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 92 OR select_id IS NULL) order by id; CAST(my_timestamp AS UNSIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -2867,7 +2867,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 80 OR select_id IS NULL order by id; CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -2879,7 +2879,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 80 OR select_id IS NULL) order by id; CAST(my_timestamp AS SIGNED INTEGER) my_timestamp id 0 0000-00-00 00:00:00 1 -19700101030001 1970-01-01 03:00:01 2 +19700101140001 1970-01-01 14:00:01 2 20380101025959 2038-01-01 02:59:59 3 20040229235959 2004-02-29 23:59:59 4 20050628100000 2005-06-28 10:00:00 5 @@ -3225,7 +3225,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 68 OR select_id IS NULL order by id; CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id 0.00 0000-00-00 00:00:00 1 -19700101030001.00 1970-01-01 03:00:01 2 +19700101140001.00 1970-01-01 14:00:01 2 20380101025959.00 2038-01-01 02:59:59 3 20040229235959.00 2004-02-29 23:59:59 4 20050628100000.00 2005-06-28 10:00:00 5 @@ -3237,7 +3237,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 68 OR select_id IS NULL) order by id; CAST(my_timestamp AS DECIMAL(37,2)) my_timestamp id 0.00 0000-00-00 00:00:00 1 -19700101030001.00 1970-01-01 03:00:01 2 +19700101140001.00 1970-01-01 14:00:01 2 20380101025959.00 2038-01-01 02:59:59 3 20040229235959.00 2004-02-29 23:59:59 4 20050628100000.00 2005-06-28 10:00:00 5 @@ -3617,7 +3617,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 56 OR select_id IS NULL order by id; CAST(my_timestamp AS TIME) my_timestamp id 00:00:00 0000-00-00 00:00:00 1 -03:00:01 1970-01-01 03:00:01 2 +14:00:01 1970-01-01 14:00:01 2 02:59:59 2038-01-01 02:59:59 3 23:59:59 2004-02-29 23:59:59 4 10:00:00 2005-06-28 10:00:00 5 @@ -3629,7 +3629,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 56 OR select_id IS NULL) order by id; CAST(my_timestamp AS TIME) my_timestamp id 00:00:00 0000-00-00 00:00:00 1 -03:00:01 1970-01-01 03:00:01 2 +14:00:01 1970-01-01 14:00:01 2 02:59:59 2038-01-01 02:59:59 3 23:59:59 2004-02-29 23:59:59 4 10:00:00 2005-06-28 10:00:00 5 @@ -3967,7 +3967,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 45 OR select_id IS NULL order by id; CAST(my_timestamp AS DATETIME) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -3979,7 +3979,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 45 OR select_id IS NULL) order by id; CAST(my_timestamp AS DATETIME) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -4329,7 +4329,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 34 OR select_id IS NULL order by id; CAST(my_timestamp AS DATE) my_timestamp id 0000-00-00 0000-00-00 00:00:00 1 -1970-01-01 1970-01-01 03:00:01 2 +1970-01-01 1970-01-01 14:00:01 2 2038-01-01 2038-01-01 02:59:59 3 2004-02-29 2004-02-29 23:59:59 4 2005-06-28 2005-06-28 10:00:00 5 @@ -4341,7 +4341,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 34 OR select_id IS NULL) order by id; CAST(my_timestamp AS DATE) my_timestamp id 0000-00-00 0000-00-00 00:00:00 1 -1970-01-01 1970-01-01 03:00:01 2 +1970-01-01 1970-01-01 14:00:01 2 2038-01-01 2038-01-01 02:59:59 3 2004-02-29 2004-02-29 23:59:59 4 2005-06-28 2005-06-28 10:00:00 5 @@ -4687,7 +4687,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 23 OR select_id IS NULL order by id; CAST(my_timestamp AS CHAR) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -4699,7 +4699,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 23 OR select_id IS NULL) order by id; CAST(my_timestamp AS CHAR) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -4999,7 +4999,7 @@ my_timestamp, id FROM t1_values WHERE select_id = 11 OR select_id IS NULL order by id; CAST(my_timestamp AS BINARY) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 @@ -5011,7 +5011,7 @@ WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 11 OR select_id IS NULL) order by id; CAST(my_timestamp AS BINARY) my_timestamp id 0000-00-00 00:00:00 0000-00-00 00:00:00 1 -1970-01-01 03:00:01 1970-01-01 03:00:01 2 +1970-01-01 14:00:01 1970-01-01 14:00:01 2 2038-01-01 02:59:59 2038-01-01 02:59:59 3 2004-02-29 23:59:59 2004-02-29 23:59:59 4 2005-06-28 10:00:00 2005-06-28 10:00:00 5 diff --git a/mysql-test/suite/funcs_1/views/func_view.inc b/mysql-test/suite/funcs_1/views/func_view.inc index 660efde8927..1dba96f6901 100644 --- a/mysql-test/suite/funcs_1/views/func_view.inc +++ b/mysql-test/suite/funcs_1/views/func_view.inc @@ -247,7 +247,7 @@ INSERT INTO t1_values SET my_varbinary_1000 = '', my_datetime = '0001-01-01 00:00:00', my_date = '0001-01-01', - my_timestamp = '1970-01-01 03:00:01', + my_timestamp = '1970-01-01 14:00:01', my_time = '-838:59:59', my_year = '1901', my_bigint = -9223372036854775808, diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 008af317aec..a67a0e6c9d0 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -350,6 +350,40 @@ drop procedure p; drop temporary table t1; + +--echo # +--echo # MDEV-5143: update of a joined table with a nested subquery with +--echo # a syntax error crashes mysqld with signal 11 +--echo # + +create table t1 (id int(11) not null auto_increment, val varchar(100) null,primary key (id)); +create table t2 (id int(11) not null auto_increment, val varchar(100) null,primary key (id)); + +insert into t1 (val) values('a'); +insert into t2 (val) values('1'); + +--error ER_BAD_FIELD_ERROR +update + ( + select + val + from + ( + select + v.val + from + t2 wrong_table_alias + ) t4 + ) t3 + inner join t1 on + t1.id=t3.val +set + t1.val=t3.val +; + +drop table t1, t2; + + --echo # End of 5.3 tests --echo # diff --git a/mysql-test/t/file_contents.test b/mysql-test/t/file_contents.test index d54a60e62a3..f52f0a93c82 100644 --- a/mysql-test/t/file_contents.test +++ b/mysql-test/t/file_contents.test @@ -33,6 +33,8 @@ if ($dir_bin eq '/usr/') { $dir_docs = glob "$dir_docs/mariadb-server-*"; $dir_docs = glob "$dir_docs/MySQL-server*" unless -d $dir_docs; } + # Slackware + $dir_docs = glob "$dir_bin/doc/mariadb-[0-9]*" unless -d $dir_docs; } else { # tar.gz package, Windows, or developer work (in BZR) $dir_docs = $dir_bin; diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 8a2e847fb94..03aeca605b8 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1593,6 +1593,13 @@ set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +--echo # +--echo # MDEV-5104 crash in Item_field::used_tables with broken order by +--echo # + +--error ER_BAD_FIELD_ERROR +(select 1 order by x(y)) order by 1; + --echo # End of 5.3 tests --echo # diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 3aa78e74828..7badc143c8a 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -678,4 +678,43 @@ UPDATE t1 SET b = 0, c=1 WHERE a <=>0; SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-5102 : MySQL Bug 69851 +--echo # +CREATE TABLE t1 ( + `col1` bigint(20) unsigned NOT NULL , + `col2` bigint(20) unsigned NOT NULL , + `col3` datetime NOT NULL , + PRIMARY KEY (`col3`), + KEY (`col1`), + KEY (`col2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY RANGE (TO_DAYS(col3)) +( + PARTITION p_20130310 VALUES LESS THAN (735303) ENGINE = InnoDB, + PARTITION p_20130311 VALUES LESS THAN (735304) ENGINE = InnoDB, + PARTITION p_20130312 VALUES LESS THAN (735305) ENGINE = InnoDB +); +INSERT INTO `t1` VALUES (2,96,'2013-03-08 16:28:05'); +INSERT INTO `t1` VALUES (1,2,'2013-03-08 16:47:39'); +INSERT INTO `t1` VALUES (1,2,'2013-03-08 16:50:27'); +INSERT INTO `t1` VALUES (1,2,'2013-03-11 16:33:04'); +INSERT INTO `t1` VALUES (1,2,'2013-03-11 16:33:24'); +INSERT INTO `t1` VALUES (2,2,'2013-03-12 10:11:48'); + +SELECT * FROM t1 WHERE col1 = 1 AND col2 = 2 + AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' +GROUP BY 1, 2, 3; +--replace_column 9 # +EXPLAIN SELECT * FROM t1 WHERE col1 = 1 AND col2 = 2 + AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' +GROUP BY 1, 2, 3; + +SELECT * FROM t1 USE INDEX () WHERE col1 = 1 AND col2 = 2 + AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00' +GROUP BY 1, 2, 3; + +DROP TABLE t1; + + set global default_storage_engine=default; diff --git a/sql/field.cc b/sql/field.cc index 64d625cc9cd..1e025784617 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -7222,7 +7222,7 @@ int Field_blob::store(const char *from,uint length,CHARSET_INFO *cs) if (!String::needs_conversion(length, cs, field_charset, &dummy_offset)) { Field_blob::store_length(length); - bmove(ptr+packlength, &from, sizeof(char*)); + bmove(ptr + packlength, &from, sizeof(char*)); return 0; } if (tmpstr.copy(from, length, cs)) diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 7392264c460..cdc695c978e 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -238,6 +238,7 @@ ha_partition::ha_partition(handlerton *hton, TABLE_SHARE *share, m_is_sub_partitioned= m_part_info->is_sub_partitioned(); m_is_clone_of= clone_arg; m_clone_mem_root= clone_mem_root_arg; + m_pkey_is_clustered= clone_arg->primary_key_is_clustered(); DBUG_VOID_RETURN; } diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 27c7b2ce88d..6809ee0dfb5 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3478,6 +3478,7 @@ int subselect_single_select_engine::prepare() select_lex->order_list.elements + select_lex->group_list.elements, select_lex->order_list.first, + false, select_lex->group_list.first, select_lex->having, NULL, select_lex, diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index bb55451e9dc..b584ed6de93 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -617,7 +617,8 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) { sl->context.outer_context= 0; // Prepare underlying views/DT first. - sl->handle_derived(lex, DT_PREPARE); + if ((res= sl->handle_derived(lex, DT_PREPARE))) + goto exit; if (derived->outer_join) { diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index f4371624d6c..07b90fd406c 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2552,7 +2552,13 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) /* Fix ORDER list */ for (order= sl->order_list.first; order; order= order->next) order->item= &order->item_ptr; - sl->handle_derived(lex, DT_REINIT); + { +#ifndef DBUG_OFF + bool res= +#endif + sl->handle_derived(lex, DT_REINIT); + DBUG_ASSERT(res == 0); + } } { SELECT_LEX_UNIT *unit= sl->master_unit(); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 09c0a8e4daf..8817c270b47 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -669,8 +669,8 @@ int JOIN::prepare(Item ***rref_pointer_array, TABLE_LIST *tables_init, uint wild_num, COND *conds_init, uint og_num, - ORDER *order_init, ORDER *group_init, - Item *having_init, + ORDER *order_init, bool skip_order_by, + ORDER *group_init, Item *having_init, ORDER *proc_param_init, SELECT_LEX *select_lex_arg, SELECT_LEX_UNIT *unit_arg) { @@ -784,7 +784,16 @@ JOIN::prepare(Item ***rref_pointer_array, DBUG_RETURN(-1); /* purecov: inspected */ ref_pointer_array= *rref_pointer_array; - + + /* Resolve the ORDER BY that was skipped, then remove it. */ + if (skip_order_by && select_lex != select_lex->master_unit()->global_parameters) + { + if (setup_order(thd, (*rref_pointer_array), tables_list, fields_list, + all_fields, select_lex->order_list.first)) + DBUG_RETURN(-1); + select_lex->order_list.empty(); + } + if (having) { nesting_map save_allow_sum_func= thd->lex->allow_sum_func; @@ -3237,7 +3246,7 @@ mysql_select(THD *thd, Item ***rref_pointer_array, else { if ((err= join->prepare(rref_pointer_array, tables, wild_num, - conds, og_num, order, group, having, + conds, og_num, order, false, group, having, proc_param, select_lex, unit))) { goto err; @@ -3261,7 +3270,7 @@ mysql_select(THD *thd, Item ***rref_pointer_array, thd_proc_info(thd, "init"); thd->lex->used_tables=0; if ((err= join->prepare(rref_pointer_array, tables, wild_num, - conds, og_num, order, group, having, proc_param, + conds, og_num, order, false, group, having, proc_param, select_lex, unit))) { goto err; diff --git a/sql/sql_select.h b/sql/sql_select.h index 509e5ba67a6..b05bdd68689 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1360,8 +1360,8 @@ public: bool exec_saved_explain; int prepare(Item ***rref_pointer_array, TABLE_LIST *tables, uint wind_num, - COND *conds, uint og_num, ORDER *order, ORDER *group, - Item *having, ORDER *proc_param, SELECT_LEX *select, + COND *conds, uint og_num, ORDER *order, bool skip_order_by, + ORDER *group, Item *having, ORDER *proc_param, SELECT_LEX *select, SELECT_LEX_UNIT *unit); bool prepare_stage2(); int optimize(); diff --git a/sql/sql_union.cc b/sql/sql_union.cc index cf7fd4f8280..864f5a8d91a 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -327,6 +327,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, sl->group_list.elements, can_skip_order_by ? NULL : sl->order_list.first, + can_skip_order_by, sl->group_list.first, sl->having, (is_union_select ? NULL : @@ -510,7 +511,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, 0, 0, global_parameters->order_list.elements, // og_num global_parameters->order_list.first, // order - NULL, NULL, NULL, + false, NULL, NULL, NULL, fake_select_lex, this); fake_select_lex->table_list.empty(); } diff --git a/storage/maria/ma_checksum.c b/storage/maria/ma_checksum.c index 61ec638053a..da2d40d3687 100644 --- a/storage/maria/ma_checksum.c +++ b/storage/maria/ma_checksum.c @@ -58,7 +58,7 @@ ha_checksum _ma_checksum(MARIA_HA *info, const uchar *record) length= _ma_calc_blob_length(blob_size_length, pos); if (length) { - memcpy((char*) &pos, pos + blob_size_length, sizeof(char*)); + memcpy(&pos, pos + blob_size_length, sizeof(char*)); crc= my_checksum(crc, pos, length); } continue; diff --git a/storage/maria/ma_key.c b/storage/maria/ma_key.c index a3553801eaa..53a7479cd4e 100644 --- a/storage/maria/ma_key.c +++ b/storage/maria/ma_key.c @@ -589,7 +589,7 @@ static int _ma_put_key_in_record(register MARIA_HA *info, uint keynr, if (unpack_blobs) { memcpy(record+keyseg->start+keyseg->bit_start, - (char*) &blob_ptr,sizeof(char*)); + &blob_ptr, sizeof(char*)); memcpy(blob_ptr,key,length); blob_ptr+=length; diff --git a/storage/myisam/mi_checksum.c b/storage/myisam/mi_checksum.c index 4c10b28a653..e0a8672760d 100644 --- a/storage/myisam/mi_checksum.c +++ b/storage/myisam/mi_checksum.c @@ -40,7 +40,8 @@ ha_checksum mi_checksum(MI_INFO *info, const uchar *buf) length=_mi_calc_blob_length(column->length- portable_sizeof_char_ptr, buf); - memcpy(&pos, buf+column->length- portable_sizeof_char_ptr, sizeof(char*)); + memcpy(&pos, buf+column->length - portable_sizeof_char_ptr, + sizeof(char*)); break; } case FIELD_VARCHAR: diff --git a/storage/myisam/mi_key.c b/storage/myisam/mi_key.c index dc078ebd401..9a2526ad2cf 100644 --- a/storage/myisam/mi_key.c +++ b/storage/myisam/mi_key.c @@ -424,7 +424,7 @@ static int _mi_put_key_in_record(register MI_INFO *info, uint keynr, if (unpack_blobs) { memcpy(record+keyseg->start+keyseg->bit_start, - &blob_ptr,sizeof(char*)); + &blob_ptr, sizeof(char *)); memcpy(blob_ptr,key,length); blob_ptr+=length; |