MDEV-30151 parse error 1=2 not between/inbb-10.3-bar-MDEV-30151

This patch fixes the problem by adding a new rule booleat_test.
This makes the grammar clearer and less conflicting.

Additionally, fixing %prec in this grammar branch:

-        | boolean_test IS NULL_SYM %prec PREC_BELOW_NOT
+        | boolean_test IS NULL_SYM %prec IS

to have consistently "%prec IS" in all grammar branches starting
with "boolean_test IS ...".
It's not clear why these three rules needed different %prec before the fix:

- boolean_test IS TRUE
- boolean_test IS UNKNOWN
- boolean_test IS NULL

4 files changed, 130 insertions, 30 deletions

diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result
index 0bb4e82c8b8..a8ee4440b5e 100644
--- a/mysql-test/main/parser.result
+++ b/mysql-test/main/parser.result
@@ -1866,4 +1866,35 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 EXECUTE IMMEDIATE 'CREATE PROCEDURE p() UPDATE t SET c=\'\'"abc';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"abc' at line 1
 SET @@sql_mode=@save_sql_mode;
+#
+# MDEV-30151 parse error 1=2 not between/in
+#
+SELECT 1=2 NOT IN (3,4);
+1=2 NOT IN (3,4)
+1
+SELECT 1=2 NOT BETWEEN 3 AND 4;
+1=2 NOT BETWEEN 3 AND 4
+1
+CREATE TABLE t1 ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f` int(11) GENERATED ALWAYS AS (1 = 2 not between 3 and 4) VIRTUAL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t1;
+CREATE TABLE t1 ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f` int(11) DEFAULT NULL,
+  CONSTRAINT `CONSTRAINT_1` CHECK (1 = 2 not between 3 and 4)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t1;
+CREATE VIEW v1 AS SELECT 1 IN ( 2 NOT BETWEEN 3 AND 4 );
+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 1 = 2 not between 3 and 4 AS `1 IN ( 2 NOT BETWEEN 3 AND 4 )`	latin1	latin1_swedish_ci
+DROP VIEW v1;
+#
 # End of 10.3 tests
+#
diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test
index 9df18c50ee3..9e46f859d5c 100644
--- a/mysql-test/main/parser.test
+++ b/mysql-test/main/parser.test
@@ -1673,4 +1673,25 @@ EXECUTE IMMEDIATE 'CREATE PROCEDURE p() UPDATE t SET c=\'\'"abc';
 
 SET @@sql_mode=@save_sql_mode;
 
+--echo #
+--echo # MDEV-30151 parse error 1=2 not between/in
+--echo #
+
+SELECT 1=2 NOT IN (3,4);
+SELECT 1=2 NOT BETWEEN 3 AND 4;
+
+CREATE TABLE t1 ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE VIEW v1 AS SELECT 1 IN ( 2 NOT BETWEEN 3 AND 4 );
+SHOW CREATE VIEW v1;
+DROP VIEW v1;
+
+--echo #
 --echo # End of 10.3 tests
+--echo #
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 7766049c104..e03bde31832 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -899,7 +899,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 /*
   We should not introduce any further shift/reduce conflicts.
 */
-%expect 85
+%expect 78
 
 /*
    Comments for TOKENS.
@@ -1687,7 +1687,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 
 %left   PREC_BELOW_NOT
 
-%nonassoc NOT_SYM
+/* The precendence of boolean NOT is in fact here. See the comment below. */
+
 %left   '=' EQUAL_SYM GE '>' LE '<' NE
 %nonassoc IS
 %right BETWEEN_SYM
@@ -1699,6 +1700,24 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 %left   '*' '/' '%' DIV_SYM MOD_SYM
 %left   '^'
 %left   MYSQL_CONCAT_SYM
+/*
+  Boolean negation has a special branch in "expr" starting with NOT_SYM.
+  The precedence of logical negation is determined by the grammar itself
+  (without using Bison terminal symbol precedence) in this order
+  - Boolean factor (i.e. logical AND)
+  - Boolean NOT
+  - Boolean test (such as '=', IS NULL, IS TRUE)
+
+  But we also need a precedence for NOT_SYM in other contexts,
+  to shift (without reduce) in these cases:
+     predicate <here> NOT IN ...
+     predicate <here> NOT BETWEEN ...
+     predicate <here> NOT LIKE ...
+     predicate <here> NOT REGEXP ...
+  If the precedence of NOT_SYM was low, it would reduce immediately
+  after scanning "predicate" and then produce a syntax error on "NOT".
+*/
+%nonassoc NOT_SYM
 %nonassoc NEG '~' NOT2_SYM BINARY
 %nonassoc COLLATE_SYM
 
@@ -1938,6 +1957,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         literal insert_ident order_ident temporal_literal
         simple_ident expr sum_expr in_sum_expr
         variable variable_aux
+        boolean_test
         predicate bit_expr parenthesized_expr
         table_wild simple_expr column_default_non_parenthesized_expr udf_expr
         primary_expr string_factor_expr mysql_concatenation_expr
@@ -9840,79 +9860,83 @@ expr:
                 MYSQL_YYABORT;
             }
           }
-        | NOT_SYM expr %prec NOT_SYM
+        | NOT_SYM expr
           {
             $$= negate_expression(thd, $2);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS TRUE_SYM %prec IS
+        | boolean_test %prec PREC_BELOW_NOT
+        ;
+
+boolean_test:
+          boolean_test IS TRUE_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_istrue(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS not TRUE_SYM %prec IS
+        | boolean_test IS not TRUE_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnottrue(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS FALSE_SYM %prec IS
+        | boolean_test IS FALSE_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isfalse(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS not FALSE_SYM %prec IS
+        | boolean_test IS not FALSE_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnotfalse(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS UNKNOWN_SYM %prec IS
+        | boolean_test IS UNKNOWN_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnull(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS not UNKNOWN_SYM %prec IS
+        | boolean_test IS not UNKNOWN_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnotnull(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS NULL_SYM %prec PREC_BELOW_NOT
+        | boolean_test IS NULL_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnull(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS not NULL_SYM %prec IS
+        | boolean_test IS not NULL_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnotnull(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr EQUAL_SYM predicate %prec EQUAL_SYM
+        | boolean_test EQUAL_SYM predicate %prec EQUAL_SYM
           {
             $$= new (thd->mem_root) Item_func_equal(thd, $1, $3);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr comp_op predicate %prec '='
+        | boolean_test comp_op predicate %prec '='
           {
             $$= (*$2)(0)->create(thd, $1, $3);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr comp_op all_or_any '(' subselect ')' %prec '='
+        | boolean_test comp_op all_or_any '(' subselect ')' %prec '='
           {
             $$= all_any_subquery_creator(thd, $1, $2, $3, $5);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | predicate
+        | predicate %prec BETWEEN_SYM
         ;
 
 predicate:
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index a5ee1892e5e..89f7412ea89 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -293,7 +293,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 /*
   We should not introduce any further shift/reduce conflicts.
 */
-%expect 87
+%expect 80
 
 /*
    Comments for TOKENS.
@@ -1081,7 +1081,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 
 %left   PREC_BELOW_NOT
 
-%nonassoc NOT_SYM
+/* The precendence of boolean NOT is in fact here. See the comment below. */
+
 %left   '=' EQUAL_SYM GE '>' LE '<' NE
 %nonassoc IS
 %right BETWEEN_SYM
@@ -1093,6 +1094,24 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 %left   '*' '/' '%' DIV_SYM MOD_SYM
 %left   '^'
 %left   MYSQL_CONCAT_SYM
+/*
+  Boolean negation has a special branch in "expr" starting with NOT_SYM.
+  The precedence of logical negation is determined by the grammar itself
+  (without using Bison terminal symbol precedence) in this order
+  - Boolean factor (i.e. logical AND)
+  - Boolean NOT
+  - Boolean test (such as '=', IS NULL, IS TRUE)
+
+  But we also need a precedence for NOT_SYM in other contexts,
+  to shift (without reduce) in these cases:
+     predicate <here> NOT IN ...
+     predicate <here> NOT BETWEEN ...
+     predicate <here> NOT LIKE ...
+     predicate <here> NOT REGEXP ...
+  If the precedence of NOT_SYM was low, it would reduce immediately
+  after scanning "predicate" and then produce a syntax error on "NOT".
+*/
+%nonassoc NOT_SYM
 %nonassoc NEG '~' NOT2_SYM BINARY
 %nonassoc COLLATE_SYM
 
@@ -1339,6 +1358,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         literal insert_ident order_ident temporal_literal
         simple_ident expr sum_expr in_sum_expr
         variable variable_aux
+        boolean_test
         predicate bit_expr parenthesized_expr
         table_wild simple_expr column_default_non_parenthesized_expr udf_expr
         primary_expr string_factor_expr mysql_concatenation_expr
@@ -9797,79 +9817,83 @@ expr:
                 MYSQL_YYABORT;
             }
           }
-        | NOT_SYM expr %prec NOT_SYM
+        | NOT_SYM expr
           {
             $$= negate_expression(thd, $2);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS TRUE_SYM %prec IS
+        | boolean_test %prec PREC_BELOW_NOT
+        ;
+
+boolean_test:
+          boolean_test IS TRUE_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_istrue(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS not TRUE_SYM %prec IS
+        | boolean_test IS not TRUE_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnottrue(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS FALSE_SYM %prec IS
+        | boolean_test IS FALSE_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isfalse(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS not FALSE_SYM %prec IS
+        | boolean_test IS not FALSE_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnotfalse(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS UNKNOWN_SYM %prec IS
+        | boolean_test IS UNKNOWN_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnull(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS not UNKNOWN_SYM %prec IS
+        | boolean_test IS not UNKNOWN_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnotnull(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS NULL_SYM %prec PREC_BELOW_NOT
+        | boolean_test IS NULL_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnull(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr IS not NULL_SYM %prec IS
+        | boolean_test IS not NULL_SYM %prec IS
           {
             $$= new (thd->mem_root) Item_func_isnotnull(thd, $1);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr EQUAL_SYM predicate %prec EQUAL_SYM
+        | boolean_test EQUAL_SYM predicate %prec EQUAL_SYM
           {
             $$= new (thd->mem_root) Item_func_equal(thd, $1, $3);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr comp_op predicate %prec '='
+        | boolean_test comp_op predicate %prec '='
           {
             $$= (*$2)(0)->create(thd, $1, $3);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | expr comp_op all_or_any '(' subselect ')' %prec '='
+        | boolean_test comp_op all_or_any '(' subselect ')' %prec '='
           {
             $$= all_any_subquery_creator(thd, $1, $2, $3, $5);
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
-        | predicate
+        | predicate %prec BETWEEN_SYM
         ;
 
 predicate: