You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by yx...@apache.org on 2022/11/26 10:05:51 UTC
[shardingsphere] branch master updated: Support MySQL, PostgreSQL and openGauss select natural join statement (#22439)
This is an automated email from the ASF dual-hosted git repository.
yx9o 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 a9c3c1878e1 Support MySQL, PostgreSQL and openGauss select natural join statement (#22439)
a9c3c1878e1 is described below
commit a9c3c1878e1e1e0d73656f1c3537f34608ead815
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Sat Nov 26 18:05:43 2022 +0800
Support MySQL, PostgreSQL and openGauss select natural join statement (#22439)
---
.../select/projection/engine/ProjectionEngine.java | 25 ++++--
.../projection/engine/ProjectionEngineTest.java | 89 +++++++++++++++++++---
.../cases/dql/dql-integration-test-cases.xml | 63 +++++++++++++++
3 files changed, 159 insertions(+), 18 deletions(-)
diff --git a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngine.java b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngine.java
index 53ca0970982..f05fc85d0cc 100644
--- a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngine.java
+++ b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngine.java
@@ -52,6 +52,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectState
import java.util.Collection;
import java.util.Collections;
+import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.LinkedList;
import java.util.Map;
@@ -192,19 +193,19 @@ public final class ProjectionEngine {
Collection<Projection> remainingProjections = new LinkedList<>();
for (Projection each : getOriginalProjections(joinTable, projectionSegment)) {
Collection<Projection> actualProjections = getActualProjections(Collections.singletonList(each));
- if (joinTable.getUsing().isEmpty() || (null != owner && each.getExpression().contains(owner))) {
+ if ((joinTable.getUsing().isEmpty() && !joinTable.isNatural()) || (null != owner && each.getExpression().contains(owner))) {
result.addAll(actualProjections);
} else {
remainingProjections.addAll(actualProjections);
}
}
- result.addAll(getUsingActualProjections(remainingProjections, joinTable.getUsing()));
+ result.addAll(getUsingActualProjections(remainingProjections, joinTable.getUsing(), joinTable.isNatural()));
return result;
}
private Collection<Projection> getOriginalProjections(final JoinTableSegment joinTable, final ProjectionSegment projectionSegment) {
Collection<Projection> result = new LinkedList<>();
- if (databaseType instanceof MySQLDatabaseType && !joinTable.getUsing().isEmpty() && JoinType.RIGHT.name().equalsIgnoreCase(joinTable.getJoinType())) {
+ if (databaseType instanceof MySQLDatabaseType && (!joinTable.getUsing().isEmpty() || joinTable.isNatural()) && JoinType.RIGHT.name().equalsIgnoreCase(joinTable.getJoinType())) {
createProjection(joinTable.getRight(), projectionSegment).ifPresent(result::add);
createProjection(joinTable.getLeft(), projectionSegment).ifPresent(result::add);
return result;
@@ -228,11 +229,11 @@ public final class ProjectionEngine {
return result;
}
- private Collection<Projection> getUsingActualProjections(final Collection<Projection> actualProjections, final Collection<ColumnSegment> usingColumns) {
- if (usingColumns.isEmpty()) {
+ private Collection<Projection> getUsingActualProjections(final Collection<Projection> actualProjections, final Collection<ColumnSegment> usingColumns, final boolean natural) {
+ if (usingColumns.isEmpty() && !natural) {
return Collections.emptyList();
}
- Collection<String> usingColumnNames = getUsingColumnNames(usingColumns);
+ Collection<String> usingColumnNames = usingColumns.isEmpty() ? getUsingColumnNamesByNaturalJoin(actualProjections) : getUsingColumnNames(usingColumns);
Collection<Projection> result = new LinkedList<>();
if (databaseType instanceof MySQLDatabaseType) {
result.addAll(getJoinUsingColumnsByOriginalColumnSequence(actualProjections, usingColumnNames));
@@ -243,6 +244,18 @@ public final class ProjectionEngine {
return result;
}
+ private Collection<String> getUsingColumnNamesByNaturalJoin(final Collection<Projection> actualProjections) {
+ Collection<String> result = new LinkedHashSet<>();
+ Map<String, Projection> uniqueProjections = new LinkedHashMap<>(actualProjections.size(), 1);
+ for (Projection each : actualProjections) {
+ Projection previousProjection = uniqueProjections.put(each.getColumnLabel().toLowerCase(), each);
+ if (null != previousProjection) {
+ result.add(previousProjection.getColumnLabel().toLowerCase());
+ }
+ }
+ return result;
+ }
+
private Collection<String> getUsingColumnNames(final Collection<ColumnSegment> usingColumns) {
Collection<String> result = new LinkedHashSet<>();
for (ColumnSegment each : usingColumns) {
diff --git a/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngineTest.java b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngineTest.java
index c1ed51069bb..90a4d276709 100644
--- a/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngineTest.java
+++ b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngineTest.java
@@ -245,10 +245,10 @@ public final class ProjectionEngineTest {
when(schema.getVisibleColumnNames("t_order")).thenReturn(Arrays.asList("order_id", "user_id", "status", "merchant_id", "remark", "creation_date"));
when(schema.getVisibleColumnNames("t_order_item")).thenReturn(Arrays.asList("item_id", "order_id", "user_id", "product_id", "quantity", "creation_date"));
Optional<Projection> actual = new ProjectionEngine("public", Collections.singletonMap("public", schema), DatabaseTypeFactory.getInstance("PostgreSQL"))
- .createProjection(createJoinTableSegment(), new ShorthandProjectionSegment(0, 0));
+ .createProjection(createJoinTableSegmentWithUsingColumn(), new ShorthandProjectionSegment(0, 0));
assertTrue(actual.isPresent());
assertThat(actual.get(), instanceOf(ShorthandProjection.class));
- assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(10));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(9));
assertThat(((ShorthandProjection) actual.get()).getActualColumns(), is(crateExpectedColumnsWithoutOwnerForPostgreSQL()));
}
@@ -257,11 +257,11 @@ public final class ProjectionEngineTest {
when(schema.getVisibleColumnNames("t_order")).thenReturn(Arrays.asList("order_id", "user_id", "status", "merchant_id", "remark", "creation_date"));
when(schema.getVisibleColumnNames("t_order_item")).thenReturn(Arrays.asList("item_id", "order_id", "user_id", "product_id", "quantity", "creation_date"));
Optional<Projection> actual = new ProjectionEngine(DefaultDatabase.LOGIC_NAME,
- Collections.singletonMap(DefaultDatabase.LOGIC_NAME, schema), DatabaseTypeFactory.getInstance("MySQL")).createProjection(createJoinTableSegment(),
+ Collections.singletonMap(DefaultDatabase.LOGIC_NAME, schema), DatabaseTypeFactory.getInstance("MySQL")).createProjection(createJoinTableSegmentWithUsingColumn(),
new ShorthandProjectionSegment(0, 0));
assertTrue(actual.isPresent());
assertThat(actual.get(), instanceOf(ShorthandProjection.class));
- assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(10));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(9));
assertThat(((ShorthandProjection) actual.get()).getActualColumns(), is(crateExpectedColumnsWithoutOwnerForMySQL()));
}
@@ -271,7 +271,8 @@ public final class ProjectionEngineTest {
ShorthandProjectionSegment projectionSegment = new ShorthandProjectionSegment(0, 0);
projectionSegment.setOwner(new OwnerSegment(0, 0, new IdentifierValue("o")));
Optional<Projection> actual =
- new ProjectionEngine("public", Collections.singletonMap("public", schema), DatabaseTypeFactory.getInstance("PostgreSQL")).createProjection(createJoinTableSegment(), projectionSegment);
+ new ProjectionEngine("public", Collections.singletonMap("public", schema), DatabaseTypeFactory.getInstance("PostgreSQL")).createProjection(createJoinTableSegmentWithUsingColumn(),
+ projectionSegment);
assertTrue(actual.isPresent());
assertThat(actual.get(), instanceOf(ShorthandProjection.class));
assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(6));
@@ -284,14 +285,14 @@ public final class ProjectionEngineTest {
ShorthandProjectionSegment projectionSegment = new ShorthandProjectionSegment(0, 0);
projectionSegment.setOwner(new OwnerSegment(0, 0, new IdentifierValue("o")));
Optional<Projection> actual = new ProjectionEngine(DefaultDatabase.LOGIC_NAME,
- Collections.singletonMap(DefaultDatabase.LOGIC_NAME, schema), DatabaseTypeFactory.getInstance("MySQL")).createProjection(createJoinTableSegment(), projectionSegment);
+ Collections.singletonMap(DefaultDatabase.LOGIC_NAME, schema), DatabaseTypeFactory.getInstance("MySQL")).createProjection(createJoinTableSegmentWithUsingColumn(), projectionSegment);
assertTrue(actual.isPresent());
assertThat(actual.get(), instanceOf(ShorthandProjection.class));
assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(6));
assertThat(((ShorthandProjection) actual.get()).getActualColumns(), is(crateExpectedColumnsWithOwner()));
}
- private JoinTableSegment createJoinTableSegment() {
+ private JoinTableSegment createJoinTableSegmentWithUsingColumn() {
SimpleTableSegment left = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("t_order")));
left.setAlias(new AliasSegment(0, 0, new IdentifierValue("o")));
SimpleTableSegment right = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("t_order_item")));
@@ -300,7 +301,8 @@ public final class ProjectionEngineTest {
result.setLeft(left);
result.setRight(right);
result.setJoinType(JoinType.RIGHT.name());
- result.setUsing(Arrays.asList(new ColumnSegment(0, 0, new IdentifierValue("user_id")), new ColumnSegment(0, 0, new IdentifierValue("order_id"))));
+ result.setUsing(Arrays.asList(new ColumnSegment(0, 0, new IdentifierValue("user_id")), new ColumnSegment(0, 0, new IdentifierValue("order_id")),
+ new ColumnSegment(0, 0, new IdentifierValue("creation_date"))));
return result;
}
@@ -308,14 +310,13 @@ public final class ProjectionEngineTest {
Map<String, Projection> result = new LinkedHashMap<>();
result.put("o.user_id", new ColumnProjection("o", "user_id", null));
result.put("o.order_id", new ColumnProjection("o", "order_id", null));
+ result.put("o.creation_date", new ColumnProjection("o", "creation_date", null));
result.put("o.status", new ColumnProjection("o", "status", null));
result.put("o.merchant_id", new ColumnProjection("o", "merchant_id", null));
result.put("o.remark", new ColumnProjection("o", "remark", null));
- result.put("o.creation_date", new ColumnProjection("o", "creation_date", null));
result.put("i.item_id", new ColumnProjection("i", "item_id", null));
result.put("i.product_id", new ColumnProjection("i", "product_id", null));
result.put("i.quantity", new ColumnProjection("i", "quantity", null));
- result.put("i.creation_date", new ColumnProjection("i", "creation_date", null));
return result;
}
@@ -323,14 +324,13 @@ public final class ProjectionEngineTest {
Map<String, Projection> result = new LinkedHashMap<>();
result.put("i.order_id", new ColumnProjection("i", "order_id", null));
result.put("i.user_id", new ColumnProjection("i", "user_id", null));
+ result.put("i.creation_date", new ColumnProjection("i", "creation_date", null));
result.put("i.item_id", new ColumnProjection("i", "item_id", null));
result.put("i.product_id", new ColumnProjection("i", "product_id", null));
result.put("i.quantity", new ColumnProjection("i", "quantity", null));
- result.put("i.creation_date", new ColumnProjection("i", "creation_date", null));
result.put("o.status", new ColumnProjection("o", "status", null));
result.put("o.merchant_id", new ColumnProjection("o", "merchant_id", null));
result.put("o.remark", new ColumnProjection("o", "remark", null));
- result.put("o.creation_date", new ColumnProjection("o", "creation_date", null));
return result;
}
@@ -344,4 +344,69 @@ public final class ProjectionEngineTest {
result.put("o.creation_date", new ColumnProjection("o", "creation_date", null));
return result;
}
+
+ @Test
+ public void assertCreateProjectionWhenShorthandProjectionContainsNaturalJoinForPostgreSQL() {
+ when(schema.getVisibleColumnNames("t_order")).thenReturn(Arrays.asList("order_id", "user_id", "status", "merchant_id", "remark", "creation_date"));
+ when(schema.getVisibleColumnNames("t_order_item")).thenReturn(Arrays.asList("item_id", "order_id", "user_id", "product_id", "quantity", "creation_date"));
+ Optional<Projection> actual = new ProjectionEngine("public", Collections.singletonMap("public", schema), DatabaseTypeFactory.getInstance("PostgreSQL"))
+ .createProjection(createJoinTableSegmentWithNaturalJoin(), new ShorthandProjectionSegment(0, 0));
+ assertTrue(actual.isPresent());
+ assertThat(actual.get(), instanceOf(ShorthandProjection.class));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(9));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns(), is(crateExpectedColumnsWithoutOwnerForPostgreSQL()));
+ }
+
+ @Test
+ public void assertCreateProjectionWhenShorthandProjectionContainsNaturalJoinForMySQL() {
+ when(schema.getVisibleColumnNames("t_order")).thenReturn(Arrays.asList("order_id", "user_id", "status", "merchant_id", "remark", "creation_date"));
+ when(schema.getVisibleColumnNames("t_order_item")).thenReturn(Arrays.asList("item_id", "order_id", "user_id", "product_id", "quantity", "creation_date"));
+ Optional<Projection> actual = new ProjectionEngine(DefaultDatabase.LOGIC_NAME,
+ Collections.singletonMap(DefaultDatabase.LOGIC_NAME, schema), DatabaseTypeFactory.getInstance("MySQL")).createProjection(createJoinTableSegmentWithNaturalJoin(),
+ new ShorthandProjectionSegment(0, 0));
+ assertTrue(actual.isPresent());
+ assertThat(actual.get(), instanceOf(ShorthandProjection.class));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(9));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns(), is(crateExpectedColumnsWithoutOwnerForMySQL()));
+ }
+
+ @Test
+ public void assertCreateProjectionWhenShorthandProjectionContainsNaturalJoinAndOwnerForPostgreSQL() {
+ when(schema.getVisibleColumnNames("t_order")).thenReturn(Arrays.asList("order_id", "user_id", "status", "merchant_id", "remark", "creation_date"));
+ ShorthandProjectionSegment projectionSegment = new ShorthandProjectionSegment(0, 0);
+ projectionSegment.setOwner(new OwnerSegment(0, 0, new IdentifierValue("o")));
+ Optional<Projection> actual =
+ new ProjectionEngine("public", Collections.singletonMap("public", schema), DatabaseTypeFactory.getInstance("PostgreSQL")).createProjection(createJoinTableSegmentWithNaturalJoin(),
+ projectionSegment);
+ assertTrue(actual.isPresent());
+ assertThat(actual.get(), instanceOf(ShorthandProjection.class));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(6));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns(), is(crateExpectedColumnsWithOwner()));
+ }
+
+ @Test
+ public void assertCreateProjectionWhenShorthandProjectionContainsNaturalJoinAndOwnerForMySQL() {
+ when(schema.getVisibleColumnNames("t_order")).thenReturn(Arrays.asList("order_id", "user_id", "status", "merchant_id", "remark", "creation_date"));
+ ShorthandProjectionSegment projectionSegment = new ShorthandProjectionSegment(0, 0);
+ projectionSegment.setOwner(new OwnerSegment(0, 0, new IdentifierValue("o")));
+ Optional<Projection> actual = new ProjectionEngine(DefaultDatabase.LOGIC_NAME,
+ Collections.singletonMap(DefaultDatabase.LOGIC_NAME, schema), DatabaseTypeFactory.getInstance("MySQL")).createProjection(createJoinTableSegmentWithNaturalJoin(), projectionSegment);
+ assertTrue(actual.isPresent());
+ assertThat(actual.get(), instanceOf(ShorthandProjection.class));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns().size(), is(6));
+ assertThat(((ShorthandProjection) actual.get()).getActualColumns(), is(crateExpectedColumnsWithOwner()));
+ }
+
+ private JoinTableSegment createJoinTableSegmentWithNaturalJoin() {
+ SimpleTableSegment left = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("t_order")));
+ left.setAlias(new AliasSegment(0, 0, new IdentifierValue("o")));
+ SimpleTableSegment right = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("t_order_item")));
+ right.setAlias(new AliasSegment(0, 0, new IdentifierValue("i")));
+ JoinTableSegment result = new JoinTableSegment();
+ result.setLeft(left);
+ result.setRight(right);
+ result.setNatural(true);
+ result.setJoinType(JoinType.RIGHT.name());
+ return result;
+ }
}
diff --git a/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml b/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index fdad56d324d..b55ebaf0c9d 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -659,6 +659,14 @@
<test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_order_item i WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_order_item i WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
<test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id), i.product_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? GROUP BY i.product_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
@@ -709,6 +717,27 @@
<test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_order_item i WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- TODO support MySQL natural right join statement when calcite support natural right join -->
+ <test-case sql="SELECT * FROM t_order o NATURAL RIGHT JOIN t_order_item i WHERE i.user_id = ? ORDER BY i.item_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL FULL JOIN t_order_item i WHERE o.user_id = ? OR i.user_id = ? ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int, 10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
<test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id), MIN(m.merchant_name) FROM t_order o INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? GROUP BY m.merchant_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
@@ -759,6 +788,27 @@
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
+ <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- TODO support MySQL natural right join statement when calcite support natural right join -->
+ <test-case sql="SELECT * FROM t_order o NATURAL RIGHT JOIN t_merchant m WHERE m.country_id = 1 ORDER BY o.order_id, m.merchant_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL FULL JOIN t_merchant m where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p NATURAL JOIN t_product_detail d WHERE p.product_id > ? ORDER BY p.product_id DESC" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p NATURAL JOIN t_product_detail d WHERE p.product_id > ? ORDER BY p.product_id DESC LIMIT 2, 5" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
<test-case sql="SELECT MIN(d.detail_id), MIN(p.category_id), p.product_id FROM t_product p INNER JOIN t_product_detail d ON p.product_id = d.product_id WHERE p.product_id = ? GROUP BY p.product_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
@@ -791,6 +841,19 @@
<test-case sql="SELECT * FROM t_product p FULL JOIN t_product_detail d USING(product_id) WHERE d.detail_id = ? OR p.category_id = 10 ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
+
+ <test-case sql="SELECT * FROM t_product p NATURAL LEFT JOIN t_product_detail d WHERE p.category_id = ? ORDER BY p.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- TODO support MySQL natural right join statement when calcite support natural right join -->
+ <test-case sql="SELECT * FROM t_product p NATURAL RIGHT JOIN t_product_detail d WHERE d.detail_id = ? ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p NATURAL FULL JOIN t_product_detail d WHERE d.detail_id = ? OR p.category_id = 10 ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
<test-case sql="SELECT o.order_id, o.user_id, o.status, o.merchant_id, (SELECT t.merchant_name FROM t_merchant t WHERE t.merchant_id = o.merchant_id) AS merchant_name FROM t_order o WHERE o.order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="1000:long" expected-data-source-name="read_dataset" />