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 <= ?) 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 <= 10 UNION SELECT merchant_id, merchant_name FROM t_merchant WHERE merchant_id <= 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>