[Commits] 7172ff780bd: MENT-1062 Feature Sponsorship - Support of Column Alias for Sybase compatibility

psergey sergey at mariadb.com
Fri Jun 18 00:43:25 EEST 2021


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
 };
 


More information about the commits mailing list