revision-id: 7172ff780bd6c1d131d6134c4f92ae71604a0b1c (mariadb-10.5.2-1036-g7172ff780bd) parent(s): 03b8b21749294e8455879ae82f91e7f45dacda97 author: Oleksandr Byelkin committer: Sergei Petrunia timestamp: 2021-06-18 00:43:25 +0300 message: MENT-1062 Feature Sponsorship - Support of Column Alias for Sybase compatibility Add RESOLVE_IN_WHERE sql_mode switch (which makes alias resolved in SELECT LIST (backward)/WHERE/ON) Use alias resolving branch created for ORDER BY to resolve aliases as references. To make VIEW independent of the switch, print the resolved alias in view text as expression it refer to. --- mysql-test/main/alias_in_where.result | 377 +++++++++++++++++++++ mysql-test/main/alias_in_where.test | 268 +++++++++++++++ mysql-test/main/information_schema.result | 2 +- mysql-test/main/mysqld--help.result | 2 +- mysql-test/main/sql_mode.result | 4 +- mysql-test/main/sql_mode.test | 2 +- mysql-test/main/system_mysql_db.result | 4 +- mysql-test/main/system_mysql_db_fix40123.result | 4 +- mysql-test/main/system_mysql_db_fix50030.result | 4 +- mysql-test/main/system_mysql_db_fix50117.result | 4 +- mysql-test/suite/funcs_1/r/is_columns_mysql.result | 8 +- mysql-test/suite/sys_vars/r/sql_mode_basic.result | 8 +- .../sys_vars/r/sysvars_server_embedded.result | 2 +- .../sys_vars/r/sysvars_server_notembedded.result | 2 +- mysql-test/suite/sys_vars/t/sql_mode_basic.test | 4 +- scripts/mysql_system_tables.sql | 4 +- scripts/mysql_system_tables_fix.sql | 6 +- sql/item.cc | 202 +++++++++++ sql/item.h | 48 +++ sql/item_cmpfunc.cc | 21 ++ sql/item_cmpfunc.h | 2 + sql/sp.cc | 2 +- sql/sql_base.cc | 82 ++++- sql/sql_base.h | 3 +- sql/sql_class.h | 1 + sql/sql_lex.cc | 3 + sql/sql_lex.h | 10 + sql/sql_prepare.cc | 5 +- sql/sql_select.cc | 10 +- sql/sys_vars.cc | 2 +- 30 files changed, 1060 insertions(+), 36 deletions(-) diff --git a/mysql-test/main/alias_in_where.result b/mysql-test/main/alias_in_where.result new file mode 100644 index 00000000000..05094d1d545 --- /dev/null +++ b/mysql-test/main/alias_in_where.result @@ -0,0 +1,377 @@ +set @save_sql_mode= @@sql_mode; +# +# basic tests of resolving +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +select a+b as c from t1 where c > 5; +ERROR 42S22: Unknown column 'c' in 'where clause' +set sql_mode="ALIASES_IN_WHERE"; +# field +select a as c from t1 where c > 5; +c +10 +# expression +select a+b as c from t1 where c > 10; +c +20 +select a+b as c, c*2 as d from t1 where c <= 10; +c d +2 4 +10 20 +select a+b as c, c*2 as d from t1 where c <= 10 and d < 20; +c d +2 4 +# stored procedures +prepare stmt1 from +"select a+b as c, c*2 as d from t1 where c <= 10 and d < 20"; +prepare stmt2 from +"select a as c from t1 where c > 5"; +execute stmt1; +c d +2 4 +execute stmt1; +c d +2 4 +execute stmt2; +c +10 +execute stmt2; +c +10 +# prepared statements keep the mode +set sql_mode=""; +execute stmt1; +c d +2 4 +execute stmt2; +c +10 +set sql_mode="ALIASES_IN_WHERE"; +execute stmt1; +c d +2 4 +execute stmt1; +c d +2 4 +execute stmt2; +c +10 +execute stmt2; +c +10 +# priority have alias +select a+a as b from t1 where b > 10; +b +20 +explain extended +select a+a as b from t1 where b > 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` + `test`.`t1`.`a` AS `b` from `test`.`t1` where `b` > 10 +create view v1 as select a+a as b from t1 where b > 10; +select * from v1; +b +20 +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` + `t1`.`a` AS `b` from `t1` where `t1`.`a` + `t1`.`a` > 10 latin1 latin1_swedish_ci +drop view v1; +drop table t1; +set @@sql_mode= @save_sql_mode; +# +# resolving in ON +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +create table t2 (d int, e int); +insert into t2 values (1,2), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +# +# ON is not supported +# +select a+b as c, a,b,c,d from t1 join t2 on c=t2.e; +ERROR 42S22: Unknown column 'c' in 'on clause' +drop table t1, t2; +set @@sql_mode= @save_sql_mode; +# +# VIEW test +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +# expression +create view v1 as select a+b as c from t1 where c > 10; +# field +create view v2 as select a as c from t1 where c > 5; +select * from v1; +c +20 +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` + `t1`.`b` AS `c` from `t1` where `t1`.`a` + `t1`.`b` > 10 latin1 latin1_swedish_ci +select * from v2; +c +10 +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `c` from `t1` where `t1`.`a` > 5 latin1 latin1_swedish_ci +set sql_mode=""; +select * from v1; +c +20 +select * from v2; +c +10 +set sql_mode="ALIASES_IN_WHERE"; +prepare stmt1 from +"select * from v1"; +prepare stmt2 from +"select * from v2"; +execute stmt1; +c +20 +execute stmt1; +c +20 +execute stmt2; +c +10 +execute stmt2; +c +10 +# views are not dependent on the mode after creation +set sql_mode=""; +execute stmt1; +c +20 +execute stmt1; +c +20 +execute stmt2; +c +10 +execute stmt2; +c +10 +set sql_mode="ALIASES_IN_WHERE"; +execute stmt1; +c +20 +execute stmt1; +c +20 +drop view v1, v2; +drop table t1; +# +# PROCEDURES test +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +# expression +create procedure p1() select a+b as c from t1 where c > 10; +call p1(); +c +20 +call p1(); +c +20 +set sql_mode=""; +call p1(); +c +20 +call p1(); +c +20 +set sql_mode="ALIASES_IN_WHERE"; +call p1(); +c +20 +call p1(); +c +20 +drop procedure p1; +drop table t1; +# +# aggregate functions test +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +select sum(a) as c from t1 where c > 10; +ERROR 42S22: Reference 'c' not supported (reference to group function) +drop table t1; +# +# +# FORWARD resolving tests +# +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +create table t3 (d int); +insert into t3 values (1), (2), (6); +set sql_mode="ALIASES_IN_WHERE"; +select a, t1.a + 1 as a, t1.a + 1 as a from t1; +ERROR 23000: Column 'a' in SELECT-list is ambiguous +select a as b, b as a from t1; +ERROR 42S22: Reference 'b' not supported (cyclic reference) +# +# forward reference (first a+1) and +# don't resolve inside alias (second a+1) +# +select a+1, a + 1 as a, t1.a from t1; +a+1 a a +3 2 1 +7 6 5 +12 11 10 +prepare stmt from "select a+1, a + 1 as a, t1.a from t1"; +execute stmt; +a+1 a a +3 2 1 +7 6 5 +12 11 10 +execute stmt; +a+1 a a +3 2 1 +7 6 5 +12 11 10 +# +# WHERE reference expression +# +select a + 1 as a, t1.a from t1 where a = 6; +a a +6 5 +prepare stmt from "select a + 1 as a, t1.a from t1 where a = 6"; +execute stmt; +a a +6 5 +execute stmt; +a a +6 5 +# +# WHERE reference no expression +# +select a - 1 as a, t1.a from t1 where a; +a a +4 5 +9 10 +prepare stmt from "select a - 1 as a, t1.a from t1 where a"; +execute stmt; +a a +4 5 +9 10 +execute stmt; +a a +4 5 +9 10 +# +# inside aggregate and forward reference +# +select max(a), a + 1 as a, t1.a from t1; +max(a) a a +11 2 1 +prepare stmt from "select max(a), a + 1 as a, t1.a from t1"; +execute stmt; +max(a) a a +11 2 1 +execute stmt; +max(a) a a +11 2 1 +# +# reference on aggregate from SELECT LIST +# +select a, max(a + 1) as a, t1.a from t1; +ERROR 42S22: Reference 'a' not supported (reference to group function) +# +# reference on aggregate from SELECT LIST +# +select max(a + 1) as a, t1.a from t1 where a > 2; +ERROR 42S22: Reference 'a' not supported (reference to group function) +# +# normal outer field resolution still works +# +select a from t1 where (select 1 from t3 where a=d); +a +1 +# +# outer alias reference (forward) +# +explain extended +select (select 1 from t3 where a=d), a+1 as a from t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select <expr_cache><`a`>((/* select#2 */ select 1 from `test`.`t3` where `a` = `test`.`t3`.`d`)) AS `(select 1 from t3 where a=d)`,`test`.`t1`.`a` + 1 AS `a` from `test`.`t1` +select (select 1 from t3 where a=d), a+1 as a from t1; +(select 1 from t3 where a=d) a +1 2 +1 6 +NULL 11 +prepare stmt from "select (select 1 from t3 where a=d), a+1 as a from t1"; +execute stmt; +(select 1 from t3 where a=d) a +1 2 +1 6 +NULL 11 +execute stmt; +(select 1 from t3 where a=d) a +1 2 +1 6 +NULL 11 +# +# outer reference from WHERE +# +explain extended +select a+1 as a from t1 where (select 1 from t3 where a=d); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` + 1 AS `a` from `test`.`t1` where <expr_cache><`a`>((/* select#2 */ select 1 from `test`.`t3` where `a` = `test`.`t3`.`d`)) +select a+1 as a from t1 where (select 1 from t3 where a=d); +a +2 +6 +prepare stmt from "select a+1 as a from t1 where (select 1 from t3 where a=d)"; +execute stmt; +a +2 +6 +execute stmt; +a +2 +6 +# +# do not resolve outer alias in the same expression +# +explain extended +select (select 1 from t3 where a=d) as a, t1.a from t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select <expr_cache><`test`.`t1`.`a`>((/* select#2 */ select 1 from `test`.`t3` where `test`.`t1`.`a` = `test`.`t3`.`d`)) AS `a`,`test`.`t1`.`a` AS `a` from `test`.`t1` +select (select 1 from t3 where a=d) as a, t1.a from t1; +a a +1 1 +NULL 5 +NULL 10 +prepare stmt from "select (select 1 from t3 where a=d) as a, t1.a from t1"; +execute stmt; +a a +1 1 +NULL 5 +NULL 10 +execute stmt; +a a +1 1 +NULL 5 +NULL 10 +drop tables t1,t3; +set @@sql_mode= @save_sql_mode; diff --git a/mysql-test/main/alias_in_where.test b/mysql-test/main/alias_in_where.test new file mode 100644 index 00000000000..5073446e435 --- /dev/null +++ b/mysql-test/main/alias_in_where.test @@ -0,0 +1,268 @@ + +set @save_sql_mode= @@sql_mode; + +--echo # +--echo # basic tests of resolving +--echo # +create table t1 (a int, b int); + +insert into t1 values (1,1), (5,5), (10, 10); + +--error ER_BAD_FIELD_ERROR +select a+b as c from t1 where c > 5; + +set sql_mode="ALIASES_IN_WHERE"; + +--echo # field +select a as c from t1 where c > 5; + +--echo # expression +select a+b as c from t1 where c > 10; + +select a+b as c, c*2 as d from t1 where c <= 10; + +select a+b as c, c*2 as d from t1 where c <= 10 and d < 20; + +--echo # stored procedures +prepare stmt1 from + "select a+b as c, c*2 as d from t1 where c <= 10 and d < 20"; +prepare stmt2 from + "select a as c from t1 where c > 5"; + +execute stmt1; +execute stmt1; + +execute stmt2; +execute stmt2; + + +--echo # prepared statements keep the mode +set sql_mode=""; +execute stmt1; +execute stmt2; + +set sql_mode="ALIASES_IN_WHERE"; +execute stmt1; +execute stmt1; + +execute stmt2; +execute stmt2; + + +--echo # priority have alias +select a+a as b from t1 where b > 10; +explain extended +select a+a as b from t1 where b > 10; +create view v1 as select a+a as b from t1 where b > 10; +select * from v1; +show create view v1; + +drop view v1; +drop table t1; +set @@sql_mode= @save_sql_mode; + +--echo # +--echo # resolving in ON +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); + +create table t2 (d int, e int); +insert into t2 values (1,2), (5,5), (10, 10); + +set sql_mode="ALIASES_IN_WHERE"; + +--echo # +--echo # ON is not supported +--echo # +--error ER_BAD_FIELD_ERROR +select a+b as c, a,b,c,d from t1 join t2 on c=t2.e; + +drop table t1, t2; +set @@sql_mode= @save_sql_mode; + +--echo # +--echo # VIEW test +--echo # +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +--echo # expression +create view v1 as select a+b as c from t1 where c > 10; +--echo # field +create view v2 as select a as c from t1 where c > 5; + +select * from v1; +show create view v1; +select * from v2; +show create view v2; + +set sql_mode=""; +select * from v1; +select * from v2; + +set sql_mode="ALIASES_IN_WHERE"; +prepare stmt1 from + "select * from v1"; +prepare stmt2 from + "select * from v2"; + + +execute stmt1; +execute stmt1; + +execute stmt2; +execute stmt2; + + +--echo # views are not dependent on the mode after creation +set sql_mode=""; +execute stmt1; +execute stmt1; +execute stmt2; +execute stmt2; + +set sql_mode="ALIASES_IN_WHERE"; +execute stmt1; +execute stmt1; + +drop view v1, v2; +drop table t1; + + +--echo # +--echo # PROCEDURES test +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +--echo # expression +create procedure p1() select a+b as c from t1 where c > 10; +call p1(); +call p1(); +set sql_mode=""; +call p1(); +call p1(); +set sql_mode="ALIASES_IN_WHERE"; +call p1(); +call p1(); + + +drop procedure p1; +drop table t1; + +--echo # +--echo # aggregate functions test +--echo # +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; + +--error ER_ILLEGAL_REFERENCE +select sum(a) as c from t1 where c > 10; + +drop table t1; + +--echo # +--echo # +--echo # FORWARD resolving tests +--echo # +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +create table t3 (d int); +insert into t3 values (1), (2), (6); + +set sql_mode="ALIASES_IN_WHERE"; + +--error ER_NON_UNIQ_ERROR +select a, t1.a + 1 as a, t1.a + 1 as a from t1; +--error ER_ILLEGAL_REFERENCE +select a as b, b as a from t1; + +--echo # +--echo # forward reference (first a+1) and +--echo # don't resolve inside alias (second a+1) +--echo # +select a+1, a + 1 as a, t1.a from t1; +prepare stmt from "select a+1, a + 1 as a, t1.a from t1"; +execute stmt; +execute stmt; + +--echo # +--echo # WHERE reference expression +--echo # +select a + 1 as a, t1.a from t1 where a = 6; +prepare stmt from "select a + 1 as a, t1.a from t1 where a = 6"; +execute stmt; +execute stmt; + +--echo # +--echo # WHERE reference no expression +--echo # +select a - 1 as a, t1.a from t1 where a; +prepare stmt from "select a - 1 as a, t1.a from t1 where a"; +execute stmt; +execute stmt; + +--echo # +--echo # inside aggregate and forward reference +--echo # +select max(a), a + 1 as a, t1.a from t1; +prepare stmt from "select max(a), a + 1 as a, t1.a from t1"; +execute stmt; +execute stmt; + +--echo # +--echo # reference on aggregate from SELECT LIST +--echo # +--error ER_ILLEGAL_REFERENCE +select a, max(a + 1) as a, t1.a from t1; + +--echo # +--echo # reference on aggregate from SELECT LIST +--echo # +--error ER_ILLEGAL_REFERENCE +select max(a + 1) as a, t1.a from t1 where a > 2; + +--echo # +--echo # normal outer field resolution still works +--echo # +select a from t1 where (select 1 from t3 where a=d); + +--echo # +--echo # outer alias reference (forward) +--echo # +explain extended +select (select 1 from t3 where a=d), a+1 as a from t1; +select (select 1 from t3 where a=d), a+1 as a from t1; +prepare stmt from "select (select 1 from t3 where a=d), a+1 as a from t1"; +execute stmt; +execute stmt; + +--echo # +--echo # outer reference from WHERE +--echo # +explain extended +select a+1 as a from t1 where (select 1 from t3 where a=d); +select a+1 as a from t1 where (select 1 from t3 where a=d); +prepare stmt from "select a+1 as a from t1 where (select 1 from t3 where a=d)"; +execute stmt; +execute stmt; + +--echo # +--echo # do not resolve outer alias in the same expression +--echo # +explain extended +select (select 1 from t3 where a=d) as a, t1.a from t1; +select (select 1 from t3 where a=d) as a, t1.a from t1; +prepare stmt from "select (select 1 from t3 where a=d) as a, t1.a from t1"; +execute stmt; +execute stmt; + +drop tables t1,t3; + +set @@sql_mode= @save_sql_mode; diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index 8d0425049ea..01cc680f7d1 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -676,7 +676,7 @@ proc body longblob proc definer varchar(384) proc created timestamp proc modified timestamp -proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') +proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') proc comment text proc character_set_client char(32) proc collation_connection char(32) diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 7646dc99bce..31640d5e67a 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -1311,7 +1311,7 @@ The following specify which files/extra groups are read (specified before remain NO_AUTO_CREATE_USER, HIGH_NOT_PRECEDENCE, NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH, EMPTY_STRING_IS_NULL, SIMULTANEOUS_ASSIGNMENT, - TIME_ROUND_FRACTIONAL + TIME_ROUND_FRACTIONAL, ALIASES_IN_WHERE --sql-safe-updates If set to 1, UPDATEs and DELETEs need either a key in the WHERE clause, or a LIMIT clause, or else they will aborted. Prevents the common mistake of accidentally diff --git a/mysql-test/main/sql_mode.result b/mysql-test/main/sql_mode.result index 25a90703bf5..2c1604e2234 100644 --- a/mysql-test/main/sql_mode.result +++ b/mysql-test/main/sql_mode.result @@ -479,8 +479,8 @@ set sql_mode=2147483648*2*2*2; select @@sql_mode; @@sql_mode TIME_ROUND_FRACTIONAL -set sql_mode=2147483648*2*2*2*2; -ERROR 42000: Variable 'sql_mode' can't be set to the value of '34359738368' +set sql_mode=2147483648*2*2*2*2*2; +ERROR 42000: Variable 'sql_mode' can't be set to the value of '68719476736' select @@sql_mode; @@sql_mode TIME_ROUND_FRACTIONAL diff --git a/mysql-test/main/sql_mode.test b/mysql-test/main/sql_mode.test index 97f5cf42791..b9a8ae255fc 100644 --- a/mysql-test/main/sql_mode.test +++ b/mysql-test/main/sql_mode.test @@ -266,7 +266,7 @@ select @@sql_mode; set sql_mode=2147483648*2*2*2; select @@sql_mode; --error 1231 -set sql_mode=2147483648*2*2*2*2; # that mode does not exist +set sql_mode=2147483648*2*2*2*2*2; # that mode does not exist select @@sql_mode; # diff --git a/mysql-test/main/system_mysql_db.result b/mysql-test/main/system_mysql_db.result index c9e337617a1..7929a25eeb2 100644 --- a/mysql-test/main/system_mysql_db.result +++ b/mysql-test/main/system_mysql_db.result @@ -153,7 +153,7 @@ proc CREATE TABLE `proc` ( `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, @@ -179,7 +179,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/main/system_mysql_db_fix40123.result b/mysql-test/main/system_mysql_db_fix40123.result index 32609117fd2..068f9d66231 100644 --- a/mysql-test/main/system_mysql_db_fix40123.result +++ b/mysql-test/main/system_mysql_db_fix40123.result @@ -191,7 +191,7 @@ proc CREATE TABLE `proc` ( `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, @@ -217,7 +217,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/main/system_mysql_db_fix50030.result b/mysql-test/main/system_mysql_db_fix50030.result index 461da43af88..5fe077b6d1f 100644 --- a/mysql-test/main/system_mysql_db_fix50030.result +++ b/mysql-test/main/system_mysql_db_fix50030.result @@ -195,7 +195,7 @@ proc CREATE TABLE `proc` ( `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, @@ -221,7 +221,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/main/system_mysql_db_fix50117.result b/mysql-test/main/system_mysql_db_fix50117.result index 4198cda4590..2a96348498f 100644 --- a/mysql-test/main/system_mysql_db_fix50117.result +++ b/mysql-test/main/system_mysql_db_fix50117.result @@ -175,7 +175,7 @@ proc CREATE TABLE `proc` ( `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, @@ -201,7 +201,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index c20547f7a89..bdb0311a70a 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -61,7 +61,7 @@ def mysql event modified 9 '0000-00-00 00:00:00' NO timestamp NULL NULL NULL NUL def mysql event name 2 '' NO char 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci char(64) PRI select,insert,update,references NEVER NULL def mysql event on_completion 14 'DROP' NO enum 8 24 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('DROP','PRESERVE') select,insert,update,references NEVER NULL def mysql event originator 17 NULL NO int NULL NULL 10 0 NULL NULL NULL int(10) unsigned select,insert,update,references NEVER NULL -def mysql event sql_mode 15 '' NO set 561 1683 NULL NULL NULL utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') select,insert,update,references NEVER NULL +def mysql event sql_mode 15 '' NO set 578 1734 NULL NULL NULL utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') select,insert,update,references NEVER NULL def mysql event starts 11 NULL YES datetime NULL NULL NULL NULL 0 NULL NULL datetime select,insert,update,references NEVER NULL def mysql event status 13 'ENABLED' NO enum 18 54 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') select,insert,update,references NEVER NULL def mysql event time_zone 18 'SYSTEM' NO char 64 64 NULL NULL NULL latin1 latin1_swedish_ci char(64) select,insert,update,references NEVER NULL @@ -136,7 +136,7 @@ def mysql proc returns 10 NULL NO longblob 4294967295 4294967295 NULL NULL NULL def mysql proc security_type 8 'DEFINER' NO enum 7 21 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('INVOKER','DEFINER') select,insert,update,references NEVER NULL def mysql proc specific_name 4 '' NO char 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci char(64) select,insert,update,references NEVER NULL def mysql proc sql_data_access 6 'CONTAINS_SQL' NO enum 17 51 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') select,insert,update,references NEVER NULL -def mysql proc sql_mode 15 '' NO set 561 1683 NULL NULL NULL utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') select,insert,update,references NEVER NULL +def mysql proc sql_mode 15 '' NO set 578 1734 NULL NULL NULL utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') select,insert,update,references NEVER NULL def mysql proc type 3 NULL NO enum 12 36 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') PRI select,insert,update,references NEVER NULL def mysql procs_priv Db 2 '' NO char 64 192 NULL NULL NULL utf8mb3 utf8mb3_bin char(64) PRI select,insert,update,references NEVER NULL def mysql procs_priv Grantor 6 '' NO varchar 384 1152 NULL NULL NULL utf8mb3 utf8mb3_bin varchar(384) MUL select,insert,update,references NEVER NULL @@ -389,7 +389,7 @@ NULL mysql event starts datetime NULL NULL NULL NULL datetime NULL mysql event ends datetime NULL NULL NULL NULL datetime 3.0000 mysql event status enum 18 54 utf8mb3 utf8mb3_general_ci enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') 3.0000 mysql event on_completion enum 8 24 utf8mb3 utf8mb3_general_ci enum('DROP','PRESERVE') -3.0000 mysql event sql_mode set 561 1683 utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') +3.0000 mysql event sql_mode set 578 1734 utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') 3.0000 mysql event comment char 64 192 utf8mb3 utf8mb3_bin char(64) NULL mysql event originator int NULL NULL NULL NULL int(10) unsigned 1.0000 mysql event time_zone char 64 64 latin1 latin1_swedish_ci char(64) @@ -463,7 +463,7 @@ NULL mysql innodb_table_stats sum_of_other_index_sizes bigint NULL NULL NULL NUL 3.0000 mysql proc definer varchar 384 1152 utf8mb3 utf8mb3_bin varchar(384) NULL mysql proc created timestamp NULL NULL NULL NULL timestamp NULL mysql proc modified timestamp NULL NULL NULL NULL timestamp -3.0000 mysql proc sql_mode set 561 1683 utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') +3.0000 mysql proc sql_mode set 578 1734 utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') 1.0000 mysql proc comment text 65535 65535 utf8mb3 utf8mb3_bin text 3.0000 mysql proc character_set_client char 32 96 utf8mb3 utf8mb3_bin char(32) 3.0000 mysql proc collation_connection char 32 96 utf8mb3 utf8mb3_bin char(32) diff --git a/mysql-test/suite/sys_vars/r/sql_mode_basic.result b/mysql-test/suite/sys_vars/r/sql_mode_basic.result index d911e80b780..6efa239297e 100644 --- a/mysql-test/suite/sys_vars/r/sql_mode_basic.result +++ b/mysql-test/suite/sys_vars/r/sql_mode_basic.result @@ -370,12 +370,12 @@ SET @@global.sql_mode = 17179869184; SELECT @@global.sql_mode; @@global.sql_mode TIME_ROUND_FRACTIONAL -SET @@global.sql_mode = 34359738367; +SET @@global.sql_mode = 34359738368*2-1; SELECT @@global.sql_mode; @@global.sql_mode -REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL -SET @@global.sql_mode = 34359738368; -ERROR 42000: Variable 'sql_mode' can't be set to the value of '34359738368' +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL,ALIASES_IN_WHERE +SET @@global.sql_mode = 34359738368*2; +ERROR 42000: Variable 'sql_mode' can't be set to the value of '68719476736' SET @@global.sql_mode = 0.4; ERROR 42000: Incorrect argument type to variable 'sql_mode' '#---------------------FN_DYNVARS_152_08----------------------#' diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 0c818a5b0f9..47cb074b622 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -3269,7 +3269,7 @@ VARIABLE_COMMENT Sets the sql mode NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL +ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL,ALIASES_IN_WHERE READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SQL_NOTES diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 598aee71abd..851a6beeef4 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -3929,7 +3929,7 @@ VARIABLE_COMMENT Sets the sql mode NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL +ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL,ALIASES_IN_WHERE READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SQL_NOTES diff --git a/mysql-test/suite/sys_vars/t/sql_mode_basic.test b/mysql-test/suite/sys_vars/t/sql_mode_basic.test index 68be8ba969b..2220f34fa4c 100644 --- a/mysql-test/suite/sys_vars/t/sql_mode_basic.test +++ b/mysql-test/suite/sys_vars/t/sql_mode_basic.test @@ -313,11 +313,11 @@ SELECT @@global.sql_mode; SET @@global.sql_mode = 17179869184; SELECT @@global.sql_mode; -SET @@global.sql_mode = 34359738367; +SET @@global.sql_mode = 34359738368*2-1; SELECT @@global.sql_mode; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.sql_mode = 34359738368; +SET @@global.sql_mode = 34359738368*2; # use of decimal values diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index 3bb24eb4c8f..b9e145464c6 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -149,7 +149,7 @@ CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsign CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY /*TranTime*/ (Transition_time) ) engine=Aria transactional=1 CHARACTER SET utf8 comment='Leap seconds information for time zones'; -CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob NOT NULL, body longblob NOT NULL, definer varchar(384) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE ', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', 'TIME_ROUND_FRACTIONAL') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL, PRIMARY KEY (db,name,type)) engine=Aria transactional=1 character set utf8 comment='Stored Procedures'; +CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob NOT NULL, body longblob NOT NULL, definer varchar(384) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE ', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', 'TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL, PRIMARY KEY (db,name,type)) engine=Aria transactional=1 character set utf8 comment='Stored Procedures'; CREATE TABLE IF NOT EXISTS procs_priv ( Host char(255) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(128) binary DEFAULT '' NOT NULL, Routine_name char(64) COLLATE utf8_general_ci DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL, Grantor varchar(384) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; @@ -170,7 +170,7 @@ PREPARE stmt FROM @str; EXECUTE stmt; DROP PREPARE stmt; -CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer varchar(384) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', originator INTEGER UNSIGNED NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 lon gblob, PRIMARY KEY (db, name) ) engine=Aria transactional=1 DEFAULT CHARSET=utf8 COMMENT 'Events'; +CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer varchar(384) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', originator INTEGER UNSIGNED NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_ bin, body_utf8 longblob, PRIMARY KEY (db, name) ) engine=Aria transactional=1 DEFAULT CHARSET=utf8 COMMENT 'Events'; SET @create_innodb_table_stats="CREATE TABLE IF NOT EXISTS innodb_table_stats ( database_name VARCHAR(64) NOT NULL, diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index ed89cc1fd6c..8077324e279 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -460,7 +460,8 @@ ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL, 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', - 'TIME_ROUND_FRACTIONAL' + 'TIME_ROUND_FRACTIONAL', + 'ALIASES_IN_WHERE' ) DEFAULT '' NOT NULL, DEFAULT CHARACTER SET utf8; @@ -597,7 +598,8 @@ ALTER TABLE event MODIFY sql_mode 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', - 'TIME_ROUND_FRACTIONAL' + 'TIME_ROUND_FRACTIONAL', + 'ALIASES_IN_WHERE' ) DEFAULT '' NOT NULL AFTER on_completion; ALTER TABLE event MODIFY name char(64) CHARACTER SET utf8 NOT NULL default ''; diff --git a/sql/item.cc b/sql/item.cc index b98270022cc..10107d137dd 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -41,6 +41,7 @@ // find_item_in_list, // RESOLVED_AGAINST_ALIAS, ... #include "sql_expression_cache.h" +#include "sql_parse.h" // check_stack_overrun const String my_null_string("NULL", 4, default_charset_info); const String my_default_string("DEFAULT", 7, default_charset_info); @@ -740,6 +741,142 @@ bool Item_ident::collect_outer_ref_processor(void *param) } +/** + Create Item_ref_alias based on this Item and put it to the reference + + @param thd thread handle + @param found_ref reference of the item + @param outer_context for local resolving - NULL otherwise outer context + @param reference pointer where to put the new item + + @retval TRUE Error + @retval FALSE OK + +*/ + +bool +Item_ident::create_ref_alias(THD *thd, Item **found_ref, + Name_resolution_context *outer_context, + Item **reference) +{ + Item_ref_alias *rf= + new(thd->mem_root) Item_ref_alias(thd, found_ref, context, outer_context); + if (!rf) + return TRUE; + rf->name= name; + if (is_explicit_name()) + rf->base_flags|= item_base_t::IS_EXPLICIT_NAME; + thd->change_item_tree(reference, rf); + return FALSE; +} + + +/** + Resolve aliases with loop check data prepare if needed + + @param thd thread handle + @param reference resolving Item refrence + @param curr current resolving expression descriptor + @param all_dep array of all descriptors or NULL if it is not + SELECT-list + + @retval TRUE Error + @retval FALSE OK +*/ + +bool Item_ident::pre_fix_aliases(THD *thd, Item **reference, + Cyclic_dep_check* curr, + Cyclic_dep_check* all_dep) +{ + DBUG_ENTER("Item_ident::pre_fix_aliases"); + if (!field_name.length || // alas has field name + ((db_name.str && db_name.str[0]) || + (table_name.str && table_name.str[0]))) + DBUG_RETURN(0); // alias can not have database or table + + List_iterator<Item> it(context->select_lex->item_list); + Item *item, **found_ref= NULL; + uint found_index= 0; + + for(uint i= 0; (item= it++); i++) + { + if (!item->is_explicit_name() || // no explicit name == no alias + (all_dep && curr == (all_dep + i))) // do not resolve self-references + continue; + if (item->name.length == field_name.length && + strncmp(item->name.str, field_name.str, field_name.length) == 0) + { + if (found_ref) + { + // duplicates check + my_error(ER_NON_UNIQ_ERROR, MYF(0), + field_name.str, "SELECT-list"); + DBUG_RETURN (1); + } + found_ref= it.ref(); + found_index= i; + } + } + if (found_ref) + { + if (create_ref_alias(thd, found_ref, NULL, reference)) + DBUG_RETURN(1); + if (all_dep) // infor for loop detection + curr->push_back(all_dep + found_index); + DBUG_RETURN(0); + } + + // check outer references + Name_resolution_context *cnt= context; + for(;;) + { + + Field *fld= find_field_in_tables(thd, this, + cnt->first_name_resolution_table, + cnt->last_name_resolution_table, + cnt->ignored_tables, + NULL, IGNORE_ERRORS, + FALSE, FALSE); + if (fld != not_found_field) + break; // the field found or an error happend (fix_field will handle it) + + cnt= cnt->outer_context; + + if (!cnt) + break; // top of name resolution scope is reached + + // check outer context + Item **self_ref= cnt->select_lex->current_select_list_fix; + List_iterator<Item> it(cnt->select_lex->item_list); + while ((item= it++)) + { + if (!item->is_explicit_name() || // no explicit name == no alias + it.ref() == self_ref) // skip self reference + continue; + if (item->name.length == field_name.length && + strncmp(item->name.str, field_name.str, field_name.length) == 0) + { + if (found_ref) + { + // duplicates check + my_error(ER_NON_UNIQ_ERROR, MYF(0), + field_name.str, "SELECT-list"); + DBUG_RETURN (1); + } + found_ref= it.ref(); + } + } + if (found_ref) + { + if (create_ref_alias(thd, found_ref, cnt, reference)) + DBUG_RETURN(1); + DBUG_RETURN(0); + } + } + + DBUG_RETURN(0); +} + /** Store the pointer to this item field into a list if not already there. @@ -2675,6 +2812,29 @@ Item* Item_func_or_sum::build_clone(THD *thd) return copy; } +bool Item_func_or_sum::pre_fix_aliases(THD *thd, Item **, + Cyclic_dep_check* curr, + Cyclic_dep_check* all_dep) +{ + Item **arg,**arg_end; + uchar buff[sizeof(char*)]; // Max local vars in function + DBUG_ENTER("Item_func::pre_fix_aliases"); + + if (check_stack_overrun(thd, STACK_MIN_SIZE, buff)) + DBUG_RETURN(TRUE); // Fatal error flag is set! + + if (arg_count) + { + for (arg= args, arg_end= args + arg_count; arg != arg_end ; arg++) + { + if ((*arg)->pre_fix_aliases(thd, arg, curr, all_dep)) + DBUG_RETURN(TRUE); + } + } + DBUG_RETURN(FALSE); +} + + Item_sp::Item_sp(THD *thd, Name_resolution_context *context_arg, sp_name *name_arg) : context(context_arg), m_name(name_arg), m_sp(NULL), func_ctx(NULL), @@ -8085,6 +8245,48 @@ bool Item_ref::fix_fields(THD *thd, Item **reference) } +bool Item_ref_alias::fix_fields(THD *thd, Item **reference) +{ + if (outer_finding) + { + // It is outer resolved field + Item_outer_ref *rf= new(thd->mem_root) Item_outer_ref(thd, context, ref, + {0, 0}, name, true); + rf->base_flags&= ~item_base_t::FIXED; + if (!rf) + return TRUE; + thd->change_item_tree(reference, rf); + context->select_lex->inner_refs_list.push_back(rf, thd->mem_root); + rf->in_sum_func= thd->lex->in_sum_func; + + mark_as_dependent(thd, outer_finding->select_lex, context->select_lex, + this, rf, FALSE); + + (*reference)= rf; + + if (rf->fix_fields(thd, reference)) + return TRUE; + + return FALSE; + } + + // it is probably forard reference, but loops was checked + if ((*ref)->fix_fields_if_needed_for_scalar(thd, ref)) + return TRUE; + + if ((*ref)->with_sum_func()) + { + my_error(ER_ILLEGAL_REFERENCE, MYF(0), + name.str,"reference to group function"); + + context->process_error(thd); + return TRUE; + } + set_properties(); + return FALSE; +} + + void Item_ref::set_properties() { Type_std_attributes::set(*ref); diff --git a/sql/item.h b/sql/item.h index 004a56e7371..6e0e9f707c3 100644 --- a/sql/item.h +++ b/sql/item.h @@ -286,6 +286,8 @@ struct Name_resolution_context: Sql_alloc } }; +typedef List<void> Cyclic_dep_check; + /* Store and restore the current state of a name resolution context. @@ -1153,6 +1155,14 @@ class Item :public Value_source, DBUG_ASSERT(0); } + virtual bool pre_fix_aliases(THD *, Item **, + Cyclic_dep_check*, Cyclic_dep_check*) + { + DBUG_ENTER("Item::pre_fix_aliases"); + // most Items do nothing for preliminry aliases resolvimg + DBUG_RETURN(0); + } + /* Fix after some tables has been pulled out. Basically re-calculate all attributes that are dependent on the tables. @@ -1511,6 +1521,7 @@ class Item :public Value_source, - Item_ref - Item_cache_wrapper - Item_direct_ref + - Item_ref_alias - Item_direct_view_ref - Item_ref_null_helper - Item_name_const @@ -3474,6 +3485,12 @@ class Item_ident :public Item_result_field Collect outer references */ bool collect_outer_ref_processor(void *arg) override; + bool pre_fix_aliases(THD *, Item **, + Cyclic_dep_check*, Cyclic_dep_check*) override; + + bool create_ref_alias(THD *thd, Item **found_ref, + Name_resolution_context *outer_context, + Item **reference); friend bool insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, const char *table_name, List_iterator<Item> *it, @@ -5432,6 +5449,8 @@ class Item_func_or_sum: public Item_result_field, { return Item_args::value_depends_on_sql_mode_bit_or().soft_to_hard(); } + bool pre_fix_aliases(THD *, Item**, Cyclic_dep_check*, + Cyclic_dep_check*) override; }; class sp_head; @@ -5807,6 +5826,35 @@ class Item_direct_ref_to_ident :public Item_direct_ref }; +/** + This class is the same as Item_direct_ref but created to point to + an expression with alias in SELECT list for preliminary alias resolving +*/ + +class Item_ref_alias :public Item_direct_ref +{ + Name_resolution_context *outer_finding; +public: + Item_ref_alias(THD *thd, Item **item, Name_resolution_context *context, + Name_resolution_context *outer): + Item_direct_ref(thd, context, item, {NULL, 0}, + item[0]->name, TRUE), outer_finding(outer) + { + ref= item; + base_flags&= ~item_base_t::FIXED; + } + + bool fix_fields(THD *thd, Item **it) override; + + void print(String *str, enum_query_type query_type) override + { + if (query_type & QT_VIEW_INTERNAL) + (*ref)->print(str, query_type); + else + Item_direct_ref::print(str, query_type); + } +}; + class Item_cache; class Expression_cache; class Expression_cache_tracker; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 2a6f8ae57db..cbceff7a798 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4940,6 +4940,27 @@ Item_cond::fix_fields(THD *thd, Item **ref) return FALSE; } +bool +Item_cond::pre_fix_aliases(THD *thd, Item **, Cyclic_dep_check* curr, + Cyclic_dep_check* all_dep) + +{ + List_iterator<Item> li(list); + Item *item; + uchar buff[sizeof(char*)]; // Max local vars in function + DBUG_ENTER("Item_cond::pre_fix_aliases"); + + if (check_stack_overrun(thd, STACK_MIN_SIZE, buff)) + DBUG_RETURN(TRUE); // Fatal error flag is set! + + while ((item= li++)) + { + if (item->pre_fix_aliases(thd, li.ref(), curr, all_dep)) + DBUG_RETURN(TRUE); + } + DBUG_RETURN(FALSE); +} + bool Item_cond::eval_not_null_tables(void *opt_arg) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index bc7441c2530..f59e66e1f88 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -3162,6 +3162,8 @@ class Item_cond :public Item_bool_func bool fix_fields(THD *, Item **ref) override; void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge) override; + bool pre_fix_aliases(THD *, Item**, Cyclic_dep_check*, + Cyclic_dep_check*) override; enum Type type() const override { return COND_ITEM; } List<Item>* argument_list() { return &list; } diff --git a/sql/sp.cc b/sql/sp.cc index 73c9c960cf7..902d68d7735 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -202,7 +202,7 @@ TABLE_FIELD_TYPE proc_table_fields[MYSQL_PROC_FIELD_COUNT] = "'ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER'," "'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'," "'EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'," - "'TIME_ROUND_FRACTIONAL')") }, + "'TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE')") }, { NULL, 0 } }, { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index e10abd77f71..62c7af44256 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6465,7 +6465,7 @@ find_field_in_tables(THD *thd, Item_ident *item, If the field was an outer referencee, mark all selects using this sub query as dependent on the outer query */ - if (!all_merged && current_sel != last_select) + if (!all_merged && current_sel != last_select && ref) { mark_select_range_as_dependent(thd, last_select, current_sel, found, *ref, item, true); @@ -7632,6 +7632,79 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, DBUG_RETURN(0); } +enum check_dep_loops_res {LOOP_OK, LOOP_DETECTED, LOOP_ERROR}; + +static enum check_dep_loops_res check_dep_loops(THD* thd, + Cyclic_dep_check *base, + Cyclic_dep_check *check) +{ + if (check->elements == 0) + return LOOP_OK; + void *i; + if (check_stack_overrun(thd, STACK_MIN_SIZE, (unsigned char*)&i)) + return LOOP_ERROR; // Fatal error flag is set! + List_iterator_fast<void> it(*check); + while((i= it++)) + { + if (base == i) + return LOOP_DETECTED; + enum check_dep_loops_res res= check_dep_loops(thd, base, + (Cyclic_dep_check *)i); + if (res != LOOP_OK) + return res; + } + return LOOP_OK; +} + +bool pre_setup_aliases(THD *thd, List<Item> &fields, Item **conds, + Item ***current_select_list_fix) +{ + List_iterator<Item> it(fields); + uint num= fields.elements; + DBUG_ENTER("pre_setup_aliases"); + + // Process SELECT-list + + Cyclic_dep_check *ctrl= (Cyclic_dep_check *) + alloc_root(thd->mem_root, num * sizeof(Cyclic_dep_check)); + if (!ctrl) + DBUG_RETURN(1); + for (uint i= 0; i < num; i++) + { + ctrl[i].empty(); + } + for (uint i= 0; i < num; i++) + { + Item *item= it++; + (*current_select_list_fix)= it.ref(); + if (item->pre_fix_aliases(thd, it.ref(), ctrl+i, ctrl)) + DBUG_RETURN(1); + } + (*current_select_list_fix)= NULL; + + // check loops + it.rewind(); + for (uint i= 0; i < num; i++) + { + enum check_dep_loops_res res= check_dep_loops(thd, ctrl + i, ctrl + i ); + Item *item= it++; + if (res == LOOP_DETECTED) + my_error(ER_ILLEGAL_REFERENCE, MYF(0), + item->name.str, "cyclic reference"); + if (res != LOOP_OK) + DBUG_RETURN(1); + } + + // Process conditions + if (conds[0]) + { + if (conds[0]->pre_fix_aliases(thd, conds, NULL, NULL)) + DBUG_RETURN(0); + } + + DBUG_RETURN(0); +} + /**************************************************************************** ** Check that all given fields exists and fill struct with current data ****************************************************************************/ @@ -7639,9 +7712,10 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, List<Item> &fields, enum_column_usage column_usage, List<Item> *sum_func_list, List<Item> *pre_fix, - bool allow_sum_func) + bool allow_sum_func, Item ***current_select_list_fix) { Item *item; + Item **unused= NULL; enum_column_usage saved_column_usage= thd->column_usage; nesting_map save_allow_sum_func= thd->lex->allow_sum_func; List_iterator<Item> it(fields); @@ -7649,6 +7723,8 @@ bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, bool make_pre_fix= (pre_fix && (pre_fix->elements == 0)); DBUG_ENTER("setup_fields"); DBUG_PRINT("enter", ("ref_pointer_array: %p", ref_pointer_array.array())); + if (!current_select_list_fix) + current_select_list_fix= &unused; thd->column_usage= column_usage; DBUG_PRINT("info", ("thd->column_usage: %d", thd->column_usage)); @@ -7695,6 +7771,7 @@ bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, thd->lex->current_select->cur_pos_in_select_list= 0; while ((item= it++)) { + (*current_select_list_fix) = it.ref(); if (make_pre_fix) pre_fix->push_back(item, thd->stmt_arena->mem_root); @@ -7727,6 +7804,7 @@ bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, thd->lex->used_tables|= item->used_tables(); thd->lex->current_select->cur_pos_in_select_list++; } + (*current_select_list_fix) = NULL; thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; thd->lex->current_select->cur_pos_in_select_list= UNDEF_POS; diff --git a/sql/sql_base.h b/sql/sql_base.h index cafb5967480..31a64a63d2d 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -185,7 +185,8 @@ int setup_returning_fields(THD* thd, TABLE_LIST* table_list); bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, List<Item> &item, enum_column_usage column_usage, List<Item> *sum_func_list, List<Item> *pre_fix, - bool allow_sum_func); + bool allow_sum_func, Item ***progress_pointer= NULL); +bool pre_setup_aliases(THD*, List<Item> &, Item **, Item ***); void unfix_fields(List<Item> &items); bool fill_record(THD * thd, TABLE *table_arg, List<Item> &fields, List<Item> &values, bool ignore_errors, bool update); diff --git a/sql/sql_class.h b/sql/sql_class.h index 1f42dd6dec4..30777fd27ea 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -182,6 +182,7 @@ enum enum_binlog_row_image { #define MODE_EMPTY_STRING_IS_NULL (1ULL << 32) #define MODE_SIMULTANEOUS_ASSIGNMENT (1ULL << 33) #define MODE_TIME_ROUND_FRACTIONAL (1ULL << 34) +#define MODE_ALIASES_IN_WHERE (1ULL << 35) /* The following modes are specific to MySQL */ #define MODE_MYSQL80_TIME_TRUNCATE_FRACTIONAL (1ULL << 32) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 05574daf42b..53bf876eb75 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1318,6 +1318,8 @@ void LEX::start(THD *thd_arg) wild= 0; exchange= 0; + nm_resolve_mode= NMRESOLVE_NONE; + DBUG_VOID_RETURN; } @@ -3009,6 +3011,7 @@ void st_select_lex::init_select() type= 0; db= null_clex_str; having= 0; + current_select_list_fix= 0; table_join_options= 0; select_lock= select_lock_type::NONE; in_sum_expr= with_wild= 0; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 27895af80d8..8b4bdc3a19e 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -253,6 +253,12 @@ enum enum_view_suid VIEW_SUID_DEFAULT= 2 }; +enum enum_nmresolve +{ + NMRESOLVE_NONE= 0, + NMRESOLVE_NORMAL= 1, + NMRESOLVE_ALIAS= 2 +}; enum plsql_cursor_attr_t { @@ -1119,6 +1125,8 @@ class st_select_lex: public st_select_lex_node Item *prep_having;/* saved HAVING clause for prepared statement processing */ Item *cond_pushed_into_where; /* condition pushed into WHERE */ Item *cond_pushed_into_having; /* condition pushed into HAVING */ + Item **current_select_list_fix; /* during prepare reference on current + select list element */ /* nest_levels are local to the query or VIEW, @@ -3448,6 +3456,8 @@ struct LEX: public Query_tables_list uint8 context_analysis_only; uint8 lex_options; // see OPTION_LEX_* + enum_nmresolve nm_resolve_mode; + Alter_info alter_info; Lex_prepared_stmt prepared_stmt; /* diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 0ed21fc12b1..59006638294 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -4206,7 +4206,6 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len) if (! (lex= new (mem_root) st_lex_local)) DBUG_RETURN(TRUE); lex->stmt_lex= lex; - if (set_db(&thd->db)) DBUG_RETURN(TRUE); @@ -4263,6 +4262,10 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len) thd->is_error() || init_param_array(this)); + lex->nm_resolve_mode= (thd->variables.sql_mode & MODE_ALIASES_IN_WHERE ? + NMRESOLVE_ALIAS : + NMRESOLVE_NORMAL); + if (thd->security_ctx->password_expired && lex->sql_command != SQLCOM_SET_OPTION) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a7ba0c9ffff..9d40e238cbf 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1392,8 +1392,16 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num, } } + if ((thd->lex->nm_resolve_mode == NMRESOLVE_ALIAS || + (thd->lex->nm_resolve_mode == NMRESOLVE_NONE && + thd->variables.sql_mode & MODE_ALIASES_IN_WHERE)) && + pre_setup_aliases(thd, fields_list, &conds, + &select_lex->current_select_list_fix)) + DBUG_RETURN(-1); + if (setup_fields(thd, ref_ptrs, fields_list, MARK_COLUMNS_READ, - &all_fields, &select_lex->pre_fix, 1)) + &all_fields, &select_lex->pre_fix, 1, + &select_lex->current_select_list_fix)) DBUG_RETURN(-1); thd->lex->current_select->context_analysis_place= save_place; rand_table_in_field_list= select_lex->select_list_tables & RAND_TABLE_BIT; diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index d50eddb6652..2da40aa976d 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3691,7 +3691,7 @@ static const char *sql_mode_names[]= "ALLOW_INVALID_DATES", "ERROR_FOR_DIVISION_BY_ZERO", "TRADITIONAL", "NO_AUTO_CREATE_USER", "HIGH_NOT_PRECEDENCE", "NO_ENGINE_SUBSTITUTION", "PAD_CHAR_TO_FULL_LENGTH", "EMPTY_STRING_IS_NULL", "SIMULTANEOUS_ASSIGNMENT", - "TIME_ROUND_FRACTIONAL", + "TIME_ROUND_FRACTIONAL", "ALIASES_IN_WHERE", 0 };