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 <[email protected]>
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" />
