From d256f7838b85ccbc816f4f2db631fe9fbdba284c Mon Sep 17 00:00:00 2001
From: lanzaoxu <lanzaoxu@tencent.com>
Date: Mon, 4 Mar 2024 11:14:30 +0800
Subject: [PATCH] Bug#107160 Upgrade from 5.7 to 8.0 fails with syntax error
 while parsing an event

    Problem:
    ========
    Execute SQL like 'SELECT * FROM ((SELECT * FROM test.t1) t)' which using alias in subquery outside '()' will cause syntax error.
    This syntax is allowed in MySQL5.7 so when users upgrade from 5.7 to 8.0 may encounter this error.

    Solution:
    =========
    Add derived_table_parens to support derived table with '()'.
---
 mysql-test/r/subselect.result | 15 +++++++++++++++
 mysql-test/t/subselect.test   | 15 +++++++++++++++
 sql/sql_yacc.yy               |  8 +++++++-
 3 files changed, 37 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index db05715a811..98fa7db0226 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -49,3 +49,18 @@ drop table t1;
 #
 SELECT * FROM (SELECT ST_EXTERIORRING(1)) AS t1, (SELECT 1) AS t2;
 ERROR 22023: Invalid GIS data provided to function st_exteriorring.
+# 
+# Bug#107160: Upgrade from 5.7 to 8.0 fails with syntax error while parsing an event.
+#
+CREATE TABLE t1(id int);
+INSERT INTO t1 VALUES(1),(3),(5),(7),(9);
+SELECT count(*) FROM ((SELECT * FROM test.t1) t);
+count(*)
+5
+SELECT * FROM ((SELECT * FROM test.t1 LIMIT 1) t);
+id
+1
+SELECT id FROM ((SELECT * FROM test.t1 ORDER BY id LIMIT 1) t);
+id
+1
+DROP TABLE t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index d924e965885..4a4a09fa257 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -82,3 +82,18 @@ drop table t1;
 # Used to hit an assertion when run with --ps-protocol.
 --error ER_GIS_INVALID_DATA
 SELECT * FROM (SELECT ST_EXTERIORRING(1)) AS t1, (SELECT 1) AS t2;
+
+--echo # 
+--echo # Bug#107160: Upgrade from 5.7 to 8.0 fails with syntax error while parsing an event.
+--echo #
+
+--disconnect default
+--connect (default,localhost,root,,test)
+CREATE TABLE t1(id int);
+INSERT INTO t1 VALUES(1),(3),(5),(7),(9);
+
+SELECT count(*) FROM ((SELECT * FROM test.t1) t);
+SELECT * FROM ((SELECT * FROM test.t1 LIMIT 1) t);
+SELECT id FROM ((SELECT * FROM test.t1 ORDER BY id LIMIT 1) t);
+
+DROP TABLE t1;
\ No newline at end of file
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 6c3dd85db23..d29ad4bcc83 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1865,7 +1865,7 @@ void warn_on_deprecated_user_defined_collation(
 
 %type <subquery> subquery row_subquery table_subquery
 
-%type <derived_table> derived_table
+%type <derived_table> derived_table derived_table_parens
 
 %type <param_marker> param_marker
 
@@ -12025,6 +12025,7 @@ table_factor:
           single_table
         | single_table_parens
         | derived_table { $$ = $1; }
+        | derived_table_parens { $$ = $1; }
         | joined_table_parens
           { $$= NEW_PTN PT_table_factor_joined_table($1); }
         | table_reference_list_parens
@@ -12082,6 +12083,11 @@ derived_table:
             $$= NEW_PTN PT_derived_table(true, $2, $3, &$4);
           }
         ;
+        
+derived_table_parens:
+          '(' derived_table_parens ')' { $$= $2; }
+        | '(' derived_table ')' { $$= $2; }
+        ;
 
 table_function:
           JSON_TABLE_SYM '(' expr ',' text_literal columns_clause ')'
-- 
2.19.1