You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2026/10/24 14:06:31 UTC
(shardingsphere) branch master updated: Fix MySQL and SQL Server arithmetic operator precedence (#40353)
This is an automated email from the ASF dual-hosted git repository.
terrymanu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 4236a4231b9 Fix MySQL and SQL Server arithmetic operator precedence (#40353)
4236a4231b9 is described below
commit 4236a4231b92e7b67547c7cc245cb78322e6dd6c
Author: Divyansh Yadav <an...@gmail.com>
AuthorDate: Sat Oct 24 19:36:25 2026 +0530
Fix MySQL and SQL Server arithmetic operator precedence (#40353)
---
.../src/main/antlr4/imports/doris/BaseRule.g4 | 19 +--
.../src/main/antlr4/imports/mysql/BaseRule.g4 | 19 +--
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 12 +-
.../parser/src/main/resources/case/dml/insert.xml | 28 ++---
.../main/resources/case/dml/select-expression.xml | 129 +++++++++++++++++++++
.../parser/src/main/resources/case/dml/select.xml | 52 ++++-----
.../sql/supported/dml/select-expression.xml | 3 +
7 files changed, 186 insertions(+), 76 deletions(-)
diff --git a/parser/sql/engine/dialect/doris/src/main/antlr4/imports/doris/BaseRule.g4 b/parser/sql/engine/dialect/doris/src/main/antlr4/imports/doris/BaseRule.g4
index 7b6313ac1fb..e3451df468f 100644
--- a/parser/sql/engine/dialect/doris/src/main/antlr4/imports/doris/BaseRule.g4
+++ b/parser/sql/engine/dialect/doris/src/main/antlr4/imports/doris/BaseRule.g4
@@ -1016,20 +1016,13 @@ predicate
;
bitExpr
- : bitExpr VERTICAL_BAR_ bitExpr
+ : bitExpr CARET_ bitExpr
+ | bitExpr (ASTERISK_ | SLASH_ | DIV | MOD | MOD_) bitExpr
+ | bitExpr (PLUS_ | MINUS_) bitExpr
+ | bitExpr (PLUS_ | MINUS_) intervalExpression
+ | bitExpr (SIGNED_LEFT_SHIFT_ | SIGNED_RIGHT_SHIFT_) bitExpr
| bitExpr AMPERSAND_ bitExpr
- | bitExpr SIGNED_LEFT_SHIFT_ bitExpr
- | bitExpr SIGNED_RIGHT_SHIFT_ bitExpr
- | bitExpr PLUS_ bitExpr
- | bitExpr MINUS_ bitExpr
- | bitExpr ASTERISK_ bitExpr
- | bitExpr SLASH_ bitExpr
- | bitExpr DIV bitExpr
- | bitExpr MOD bitExpr
- | bitExpr MOD_ bitExpr
- | bitExpr CARET_ bitExpr
- | bitExpr PLUS_ intervalExpression
- | bitExpr MINUS_ intervalExpression
+ | bitExpr VERTICAL_BAR_ bitExpr
| simpleExpr
;
diff --git a/parser/sql/engine/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4 b/parser/sql/engine/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
index 324023a12ac..c7d6895ac7f 100644
--- a/parser/sql/engine/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
+++ b/parser/sql/engine/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
@@ -438,20 +438,13 @@ predicate
;
bitExpr
- : bitExpr VERTICAL_BAR_ bitExpr
+ : bitExpr CARET_ bitExpr
+ | bitExpr (ASTERISK_ | SLASH_ | DIV | MOD | MOD_) bitExpr
+ | bitExpr (PLUS_ | MINUS_) bitExpr
+ | bitExpr (PLUS_ | MINUS_) intervalExpression
+ | bitExpr (SIGNED_LEFT_SHIFT_ | SIGNED_RIGHT_SHIFT_) bitExpr
| bitExpr AMPERSAND_ bitExpr
- | bitExpr SIGNED_LEFT_SHIFT_ bitExpr
- | bitExpr SIGNED_RIGHT_SHIFT_ bitExpr
- | bitExpr PLUS_ bitExpr
- | bitExpr MINUS_ bitExpr
- | bitExpr ASTERISK_ bitExpr
- | bitExpr SLASH_ bitExpr
- | bitExpr DIV bitExpr
- | bitExpr MOD bitExpr
- | bitExpr MOD_ bitExpr
- | bitExpr CARET_ bitExpr
- | bitExpr PLUS_ intervalExpression
- | bitExpr MINUS_ intervalExpression
+ | bitExpr VERTICAL_BAR_ bitExpr
| simpleExpr
;
diff --git a/parser/sql/engine/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4 b/parser/sql/engine/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
index c87a8b097ff..5bac6841a04 100644
--- a/parser/sql/engine/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
+++ b/parser/sql/engine/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
@@ -273,16 +273,8 @@ predicate
;
bitExpr
- : bitExpr VERTICAL_BAR_ bitExpr
- | bitExpr AMPERSAND_ bitExpr
- | bitExpr SIGNED_LEFT_SHIFT_ bitExpr
- | bitExpr SIGNED_RIGHT_SHIFT_ bitExpr
- | bitExpr PLUS_ bitExpr
- | bitExpr MINUS_ bitExpr
- | bitExpr ASTERISK_ bitExpr
- | bitExpr SLASH_ bitExpr
- | bitExpr MOD_ bitExpr
- | bitExpr CARET_ bitExpr
+ : bitExpr (ASTERISK_ | SLASH_ | MOD_) bitExpr
+ | bitExpr (PLUS_ | MINUS_ | AMPERSAND_ | CARET_ | VERTICAL_BAR_ | SIGNED_LEFT_SHIFT_ | SIGNED_RIGHT_SHIFT_) bitExpr
| simpleExpr
;
diff --git a/test/it/parser/src/main/resources/case/dml/insert.xml b/test/it/parser/src/main/resources/case/dml/insert.xml
index a7cc4d75d6f..a52a5bdd5db 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -1498,29 +1498,29 @@
<assignment-value>
<binary-operation-expression start-index="94" stop-index="126" literal-start-index="98" literal-stop-index="130">
<left>
- <binary-operation-expression start-index="94" stop-index="122" literal-start-index="98" literal-stop-index="126">
+ <function function-name="VALUES" text="VALUES(salary)" start-index="94" stop-index="107" literal-start-index="98" literal-stop-index="111">
+ <parameter>
+ <column name="salary" start-index="101" stop-index="106" literal-start-index="105" literal-stop-index="110" />
+ </parameter>
+ <literalText>VALUES(salary)</literalText>
+ </function>
+ </left>
+ <operator>+</operator>
+ <right>
+ <binary-operation-expression start-index="109" stop-index="126" literal-start-index="113" literal-stop-index="130">
<left>
- <function function-name="VALUES" text="VALUES(salary)" start-index="94" stop-index="107" literal-start-index="98" literal-stop-index="111">
- <parameter>
- <column name="salary" start-index="101" stop-index="106" literal-start-index="105" literal-stop-index="110" />
- </parameter>
- <literalText>VALUES(salary)</literalText>
- </function>
- </left>
- <operator>+</operator>
- <right>
<function function-name="VALUES" text="VALUES(salary)" start-index="109" stop-index="122" literal-start-index="113" literal-stop-index="126">
<parameter>
<column name="salary" start-index="116" stop-index="121" literal-start-index="120" literal-stop-index="125" />
</parameter>
<literalText>VALUES(salary)</literalText>
</function>
+ </left>
+ <operator>*</operator>
+ <right>
+ <literal-expression value="0.2" start-index="124" stop-index="126" literal-start-index="128" literal-stop-index="130" />
</right>
</binary-operation-expression>
- </left>
- <operator>*</operator>
- <right>
- <literal-expression value="0.2" start-index="124" stop-index="126" literal-start-index="128" literal-stop-index="130" />
</right>
</binary-operation-expression>
</assignment-value>
diff --git a/test/it/parser/src/main/resources/case/dml/select-expression.xml b/test/it/parser/src/main/resources/case/dml/select-expression.xml
index de4cb82a28d..f2c8f2581c6 100644
--- a/test/it/parser/src/main/resources/case/dml/select-expression.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-expression.xml
@@ -2233,6 +2233,135 @@
</where>
</select>
+ <select sql-case-id="select_where_with_bit_expr_with_plus_asterisk" parameters="1,2,3">
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <projections distinct-row="false" start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <where start-index="22" stop-index="55">
+ <expr>
+ <binary-operation-expression start-index="28" stop-index="55">
+ <left>
+ <binary-operation-expression start-index="28" stop-index="51">
+ <left>
+ <column name="order_id" start-index="28" stop-index="43">
+ <owner name="t_order" start-index="28" stop-index="34" />
+ </column>
+ </left>
+ <operator>+</operator>
+ <right>
+ <binary-operation-expression start-index="47" stop-index="51">
+ <left>
+ <literal-expression value="1" start-index="47" stop-index="47" />
+ <parameter-marker-expression parameter-index="0" start-index="47" stop-index="47" />
+ </left>
+ <operator>*</operator>
+ <right>
+ <literal-expression value="2" start-index="51" stop-index="51" />
+ <parameter-marker-expression parameter-index="1" start-index="51" stop-index="51" />
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="3" start-index="55" stop-index="55" />
+ <parameter-marker-expression parameter-index="2" start-index="55" stop-index="55" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_where_with_bit_expr_with_minus_plus" parameters="1,2,3">
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <projections distinct-row="false" start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <where start-index="22" stop-index="55">
+ <expr>
+ <binary-operation-expression start-index="28" stop-index="55">
+ <left>
+ <binary-operation-expression start-index="28" stop-index="51">
+ <left>
+ <binary-operation-expression start-index="28" stop-index="47">
+ <left>
+ <column name="order_id" start-index="28" stop-index="43">
+ <owner name="t_order" start-index="28" stop-index="34" />
+ </column>
+ </left>
+ <operator>-</operator>
+ <right>
+ <literal-expression value="1" start-index="47" stop-index="47" />
+ <parameter-marker-expression parameter-index="0" start-index="47" stop-index="47" />
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>+</operator>
+ <right>
+ <literal-expression value="2" start-index="51" stop-index="51" />
+ <parameter-marker-expression parameter-index="1" start-index="51" stop-index="51" />
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="3" start-index="55" stop-index="55" />
+ <parameter-marker-expression parameter-index="2" start-index="55" stop-index="55" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_where_with_bit_expr_with_vertical_bar_ampersand" parameters="1,2,3">
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <projections distinct-row="false" start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <where start-index="22" stop-index="55">
+ <expr>
+ <binary-operation-expression start-index="28" stop-index="55">
+ <left>
+ <binary-operation-expression start-index="28" stop-index="51">
+ <left>
+ <column name="order_id" start-index="28" stop-index="43">
+ <owner name="t_order" start-index="28" stop-index="34" />
+ </column>
+ </left>
+ <operator>|</operator>
+ <right>
+ <binary-operation-expression start-index="47" stop-index="51">
+ <left>
+ <literal-expression value="1" start-index="47" stop-index="47" />
+ <parameter-marker-expression parameter-index="0" start-index="47" stop-index="47" />
+ </left>
+ <operator>&</operator>
+ <right>
+ <literal-expression value="2" start-index="51" stop-index="51" />
+ <parameter-marker-expression parameter-index="1" start-index="51" stop-index="51" />
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="3" start-index="55" stop-index="55" />
+ <parameter-marker-expression parameter-index="2" start-index="55" stop-index="55" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
<select sql-case-id="select_where_with_simple_expr_with_literals" parameters="1">
<from start-index="14" stop-index="20">
<simple-table name="t_order" start-index="14" stop-index="20" />
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml b/test/it/parser/src/main/resources/case/dml/select.xml
index 4fe75a13909..fc1a63a3572 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -9778,42 +9778,42 @@
<expr>
<binary-operation-expression text="size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0" start-index="13" stop-index="79">
<left>
- <binary-operation-expression text="size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="13" stop-index="71">
+ <binary-operation-expression text="size / 128.0" start-index="13" stop-index="24">
<left>
<column name="size" start-index="13" stop-index="16" />
</left>
<right>
- <binary-operation-expression text="128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="20" stop-index="71">
- <left>
- <literal-expression value="128.0" start-index="20" stop-index="24" />
- </left>
- <right>
- <function function-name="CAST" text="CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="28" stop-index="71">
- <parameter>
- <function function-name="FILEPROPERTY" text="FILEPROPERTY(name, 'SpaceUsed')" start-index="33" stop-index="63">
- <parameter>
- <column name="name" start-index="46" stop-index="49" />
- </parameter>
- <parameter>
- <literal-expression value="SpaceUsed" start-index="52" stop-index="62" />
- </parameter>
- </function>
- </parameter>
- <parameter>
- <data-type value="INT" start-index="68" stop-index="70" />
- </parameter>
- </function>
- </right>
- <operator>-</operator>
- </binary-operation-expression>
+ <literal-expression value="128.0" start-index="20" stop-index="24" />
</right>
<operator>/</operator>
</binary-operation-expression>
</left>
<right>
- <literal-expression value="128.0" start-index="75" stop-index="79" />
+ <binary-operation-expression text="CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0" start-index="28" stop-index="79">
+ <left>
+ <function function-name="CAST" text="CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="28" stop-index="71">
+ <parameter>
+ <function function-name="FILEPROPERTY" text="FILEPROPERTY(name, 'SpaceUsed')" start-index="33" stop-index="63">
+ <parameter>
+ <column name="name" start-index="46" stop-index="49" />
+ </parameter>
+ <parameter>
+ <literal-expression value="SpaceUsed" start-index="52" stop-index="62" />
+ </parameter>
+ </function>
+ </parameter>
+ <parameter>
+ <data-type value="INT" start-index="68" stop-index="70" />
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <literal-expression value="128.0" start-index="75" stop-index="79" />
+ </right>
+ <operator>/</operator>
+ </binary-operation-expression>
</right>
- <operator>/</operator>
+ <operator>-</operator>
</binary-operation-expression>
</expr>
</expression-projection>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
index 69a49cf033a..7f25db73d65 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
@@ -94,6 +94,9 @@
<sql-case id="select_where_with_bit_expr_with_caret" value="SELECT * FROM t_order WHERE t_order.order_id ^ ?" db-types="MySQL" />
<sql-case id="select_where_with_bit_expr_with_plus_interval" value="SELECT * FROM t_order WHERE t_order.order_id + INTERVAL 1 SECOND" db-types="MySQL" />
<sql-case id="select_where_with_bit_expr_with_minus_interval" value="SELECT * FROM t_order WHERE t_order.order_id - INTERVAL 1 SECOND" db-types="MySQL" />
+ <sql-case id="select_where_with_bit_expr_with_plus_asterisk" value="SELECT * FROM t_order WHERE t_order.order_id + ? * ? = ?" db-types="MySQL,SQLServer,Doris" />
+ <sql-case id="select_where_with_bit_expr_with_minus_plus" value="SELECT * FROM t_order WHERE t_order.order_id - ? + ? = ?" db-types="MySQL,SQLServer,Doris" />
+ <sql-case id="select_where_with_bit_expr_with_vertical_bar_ampersand" value="SELECT * FROM t_order WHERE t_order.order_id | ? & ? = ?" db-types="MySQL,Doris" />
<sql-case id="select_where_with_simple_expr_with_literals" value="SELECT * FROM t_order WHERE ? < order_id" db-types="MySQL" />
<sql-case id="select_where_with_simple_expr_with_column" value="SELECT * FROM t_order WHERE t_order.order_id" db-types="MySQL" />
<sql-case id="select_where_with_simple_expr_with_function_call" value="SELECT * FROM t_order WHERE now() < order_id" db-types="MySQL" />