You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by ch...@apache.org on 2026/03/19 02:30:58 UTC

(shardingsphere) branch master updated: Add SQL Bind IT case for LEFT JOIN with outer table column in WHERE clause (#38497)

This is an automated email from the ASF dual-hosted git repository.

chengzhang 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 e4515f00694 Add SQL Bind IT case for LEFT JOIN with outer table column in WHERE clause (#38497)
e4515f00694 is described below

commit e4515f006942423eaa4606fef3bf8d1e5b3dea9e
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Thu Mar 19 10:30:52 2026 +0800

    Add SQL Bind IT case for LEFT JOIN with outer table column in WHERE clause (#38497)
---
 .../binder/src/test/resources/cases/dml/select.xml | 154 +++++++++++++++++++++
 .../binder/src/test/resources/sqls/dml/select.xml  |   1 +
 2 files changed, 155 insertions(+)

diff --git a/test/it/binder/src/test/resources/cases/dml/select.xml b/test/it/binder/src/test/resources/cases/dml/select.xml
index 537ee3f19fd..26c9e8ef97e 100644
--- a/test/it/binder/src/test/resources/cases/dml/select.xml
+++ b/test/it/binder/src/test/resources/cases/dml/select.xml
@@ -1411,4 +1411,158 @@
             </expr>
         </where>
     </select>
+    
+    <select sql-case-id="select_with_left_join_where_outer_column">
+        <projections start-index="7" stop-index="61">
+            <column-projection name="user_id" start-index="7" stop-index="16">
+                <column-bound>
+                    <original-database name="foo_db_1" />
+                    <original-schema name="foo_db_1" />
+                    <original-table name="t_user" />
+                    <original-column name="user_id" start-delimiter="`" end-delimiter="`" />
+                    <table-source-type name="PHYSICAL_TABLE"/>
+                </column-bound>
+                <owner name="t1" start-index="7" stop-index="8">
+                    <table-bound>
+                        <original-database name="foo_db_1" />
+                        <original-schema name="foo_db_1" />
+                    </table-bound>
+                </owner>
+            </column-projection>
+            <column-projection name="user_name" start-index="19" stop-index="30">
+                <column-bound>
+                    <original-database name="foo_db_1" />
+                    <original-schema name="foo_db_1" />
+                    <original-table name="t_user" />
+                    <original-column name="user_name" start-delimiter="`" end-delimiter="`" />
+                    <table-source-type name="PHYSICAL_TABLE"/>
+                </column-bound>
+                <owner name="t1" start-index="19" stop-index="20">
+                    <table-bound>
+                        <original-database name="foo_db_1" />
+                        <original-schema name="foo_db_1" />
+                    </table-bound>
+                </owner>
+            </column-projection>
+            <column-projection name="order_id" start-index="33" stop-index="43">
+                <column-bound>
+                    <original-database name="foo_db_1" />
+                    <original-schema name="foo_db_1" />
+                    <original-table name="t_order" />
+                    <original-column name="order_id" start-delimiter="`" end-delimiter="`" />
+                    <table-source-type name="PHYSICAL_TABLE"/>
+                </column-bound>
+                <owner name="t2" start-index="33" stop-index="34">
+                    <table-bound>
+                        <original-database name="foo_db_1" />
+                        <original-schema name="foo_db_1" />
+                    </table-bound>
+                </owner>
+            </column-projection>
+            <column-projection name="creation_date" start-index="46" stop-index="61">
+                <column-bound>
+                    <original-database name="foo_db_1" />
+                    <original-schema name="foo_db_1" />
+                    <original-table name="t_order" />
+                    <original-column name="creation_date" start-delimiter="`" end-delimiter="`" />
+                    <table-source-type name="PHYSICAL_TABLE"/>
+                </column-bound>
+                <owner name="t2" start-index="46" stop-index="47">
+                    <table-bound>
+                        <original-database name="foo_db_1" />
+                        <original-schema name="foo_db_1" />
+                    </table-bound>
+                </owner>
+            </column-projection>
+        </projections>
+        <from start-index="68" stop-index="124">
+            <join-table join-type="LEFT">
+                <left>
+                    <simple-table alias="t1" name="t_user" start-index="68" stop-index="76">
+                        <table-bound>
+                            <original-database name="foo_db_1" />
+                            <original-schema name="foo_db_1" />
+                        </table-bound>
+                    </simple-table>
+                </left>
+                <right>
+                    <simple-table alias="t2" name="t_order" start-index="88" stop-index="97">
+                        <table-bound>
+                            <original-database name="foo_db_1" />
+                            <original-schema name="foo_db_1" />
+                        </table-bound>
+                    </simple-table>
+                </right>
+                <on-condition>
+                    <binary-operation-expression start-index="102" stop-index="124">
+                        <left>
+                            <column name="user_id" start-index="102" stop-index="111">
+                                <column-bound>
+                                    <original-database name="foo_db_1" />
+                                    <original-schema name="foo_db_1" />
+                                    <original-table name="t_user" />
+                                    <original-column name="user_id" start-delimiter="`" end-delimiter="`" />
+                                    <table-source-type name="PHYSICAL_TABLE"/>
+                                </column-bound>
+                                <owner name="t1" start-index="102" stop-index="103">
+                                    <table-bound>
+                                        <original-database name="foo_db_1" />
+                                        <original-schema name="foo_db_1" />
+                                    </table-bound>
+                                </owner>
+                            </column>
+                        </left>
+                        <operator>=</operator>
+                        <right>
+                            <column name="user_id" start-index="115" stop-index="124">
+                                <column-bound>
+                                    <original-database name="foo_db_1" />
+                                    <original-schema name="foo_db_1" />
+                                    <original-table name="t_order" />
+                                    <original-column name="user_id" start-delimiter="`" end-delimiter="`" />
+                                    <table-source-type name="PHYSICAL_TABLE"/>
+                                </column-bound>
+                                <owner name="t2" start-index="115" stop-index="116">
+                                    <table-bound>
+                                        <original-database name="foo_db_1" />
+                                        <original-schema name="foo_db_1" />
+                                    </table-bound>
+                                </owner>
+                            </column>
+                        </right>
+                    </binary-operation-expression>
+                </on-condition>
+            </join-table>
+        </from>
+        <where start-index="126" stop-index="147">
+            <expr start-index="132" stop-index="147">
+                <binary-operation-expression start-index="132" stop-index="147">
+                    <left start-index="132" stop-index="143">
+                        <column name="user_name" start-index="132" stop-index="143">
+                            <column-bound>
+                                <original-database name="foo_db_1" />
+                                <original-schema name="foo_db_1" />
+                                <original-table name="t_user" />
+                                <original-column name="user_name" start-delimiter="`" end-delimiter="`" />
+                                <table-source-type name="PHYSICAL_TABLE"/>
+                            </column-bound>
+                            <owner name="t1" start-index="132" stop-index="133">
+                                <table-bound>
+                                    <original-database name="foo_db_1" />
+                                    <original-schema name="foo_db_1" />
+                                </table-bound>
+                            </owner>
+                        </column>
+                    </left>
+                    <operator>=</operator>
+                    <right start-index="147" stop-index="147">
+                        <parameter-marker-expression parameter-marker-index="0" start-index="147" stop-index="147" />
+                    </right>
+                </binary-operation-expression>
+            </expr>
+        </where>
+        <parameters>
+            <parameter-marker-expression parameter-index="0" start-index="147" stop-index="147" />
+        </parameters>
+    </select>
 </sql-parser-test-cases>
diff --git a/test/it/binder/src/test/resources/sqls/dml/select.xml b/test/it/binder/src/test/resources/sqls/dml/select.xml
index 3f161f36136..add47cb1630 100644
--- a/test/it/binder/src/test/resources/sqls/dml/select.xml
+++ b/test/it/binder/src/test/resources/sqls/dml/select.xml
@@ -28,5 +28,6 @@
     <sql-case id="select_with_with_clause_same_name_as_physical_table" value="WITH t_user AS (SELECT user_id, UPPER(user_name) AS user_name, email FROM t_user WHERE user_id &lt;= ?) SELECT * FROM t_user ORDER BY user_id" db-types="MySQL"/>
     <sql-case id="select_with_current_select_projection_reference" value="SELECT order_id AS orderId, (SELECT orderId) AS tempOrderId FROM t_order" db-types="MySQL"/>
     <sql-case id="select_with_subquery_table_alias_columns_and_order_by" value="SELECT * FROM (SELECT user_id, user_name FROM t_user WHERE user_id &lt;= 10 UNION SELECT merchant_id, merchant_name FROM t_merchant WHERE merchant_id &lt;= 5) AS combined(id, name) ORDER BY id" db-types="MySQL"/>
+    <sql-case id="select_with_left_join_where_outer_column" value="SELECT t1.user_id, t1.user_name, t2.order_id, t2.creation_date FROM t_user t1 LEFT JOIN t_order t2 ON t1.user_id = t2.user_id WHERE t1.user_name = ?" db-types="MySQL" case-types="PLACEHOLDER"/>
     <sql-case id="select_with_outer_join_operator" value="SELECT o.order_id FROM t_order o, t_order_item i WHERE o.order_id = i.order_id(+)" db-types="Oracle"/>
 </sql-cases>