This is an automated email from the ASF dual-hosted git repository.
tuichenchuxin 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 77c1aceea3c Support PostgreSQL, openGauss join statement with using
condition (#22367)
77c1aceea3c is described below
commit 77c1aceea3c21a472e5ffaf2092dfdde412884d0
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Thu Nov 24 17:01:38 2022 +0800
Support PostgreSQL, openGauss join statement with using condition (#22367)
* Support PostgreSQL, openGauss join statement with using condition
* optimize test case format
* fix integration test
* optimize code style
* fix integration test case
* fix integration test case
* optimize logic when shorthand projection with owner
---
.../select/projection/engine/ProjectionEngine.java | 66 +++++++++++++++++--
.../projection/engine/ProjectionEngineTest.java | 67 +++++++++++++++++++-
.../cases/dql/dql-integration-test-cases.xml | 74 ++++++++++++++++++++--
3 files changed, 193 insertions(+), 14 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 0b153d31721..1e2623cbd15 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
@@ -33,6 +33,7 @@ import
org.apache.shardingsphere.infra.exception.SchemaNotFoundException;
import
org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereSchema;
import
org.apache.shardingsphere.infra.util.exception.ShardingSpherePreconditions;
import org.apache.shardingsphere.sql.parser.sql.common.enums.AggregationType;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.AggregationDistinctProjectionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.AggregationProjectionSegment;
@@ -51,6 +52,7 @@ import java.util.Collection;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.LinkedList;
+import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
@@ -117,7 +119,7 @@ public final class ProjectionEngine {
Collection<Projection> projections = new LinkedHashSet<>();
projections.addAll(getShorthandColumnsFromSimpleTableSegment(table,
owner));
projections.addAll(getShorthandColumnsFromSubqueryTableSegment(table));
- projections.addAll(getShorthandColumnsFromJoinTableSegment(table,
projectionSegment));
+ projections.addAll(getShorthandColumnsFromJoinTableSegment(table,
owner, projectionSegment));
return new ShorthandProjection(owner, projections);
}
@@ -177,20 +179,29 @@ public final class ProjectionEngine {
SelectStatement subSelectStatement = ((SubqueryTableSegment)
table).getSubquery().getSelect();
Collection<Projection> projections =
subSelectStatement.getProjections().getProjections().stream().map(each ->
createProjection(subSelectStatement.getFrom(), each).orElse(null))
.filter(Objects::nonNull).collect(Collectors.toList());
- return getProjections(projections);
+ return getActualProjections(projections);
}
- private Collection<Projection>
getShorthandColumnsFromJoinTableSegment(final TableSegment table, final
ProjectionSegment projectionSegment) {
+ private Collection<Projection>
getShorthandColumnsFromJoinTableSegment(final TableSegment table, final String
owner, final ProjectionSegment projectionSegment) {
if (!(table instanceof JoinTableSegment)) {
return Collections.emptyList();
}
+ JoinTableSegment joinTable = (JoinTableSegment) table;
Collection<Projection> projections = new LinkedList<>();
- createProjection(((JoinTableSegment) table).getLeft(),
projectionSegment).ifPresent(projections::add);
- createProjection(((JoinTableSegment) table).getRight(),
projectionSegment).ifPresent(projections::add);
- return getProjections(projections);
+ createProjection(joinTable.getLeft(),
projectionSegment).ifPresent(projections::add);
+ createProjection(joinTable.getRight(),
projectionSegment).ifPresent(projections::add);
+ Collection<Projection> result = new LinkedList<>();
+ for (Projection each : projections) {
+ if (joinTable.getUsing().isEmpty() || (null != owner &&
each.getExpression().contains(owner))) {
+
result.addAll(getActualProjections(Collections.singletonList(each)));
+ } else {
+ result.addAll(getJoinUsingActualProjections(projections,
joinTable.getUsing()));
+ }
+ }
+ return result;
}
- private Collection<Projection> getProjections(final Collection<Projection>
projections) {
+ private Collection<Projection> getActualProjections(final
Collection<Projection> projections) {
Collection<Projection> result = new LinkedList<>();
for (Projection each : projections) {
if (each instanceof ColumnProjection) {
@@ -204,6 +215,47 @@ public final class ProjectionEngine {
return result;
}
+ private Collection<Projection> getJoinUsingActualProjections(final
Collection<Projection> projections, final List<ColumnSegment> usingColumns) {
+ Collection<Projection> result = new LinkedList<>();
+ Collection<Projection> actualColumns =
getActualProjections(projections);
+ Collection<String> usingColumnNames =
getUsingColumnNames(usingColumns);
+ result.addAll(getJoinUsingColumns(actualColumns, usingColumnNames));
+ result.addAll(getRemainingColumns(actualColumns, usingColumnNames));
+ return result;
+ }
+
+ private Collection<String> getUsingColumnNames(final List<ColumnSegment>
usingColumns) {
+ Collection<String> result = new LinkedHashSet<>();
+ for (ColumnSegment each : usingColumns) {
+ result.add(each.getIdentifier().getValue().toLowerCase());
+ }
+ return result;
+ }
+
+ private Collection<Projection> getJoinUsingColumns(final
Collection<Projection> actualColumns, final Collection<String>
usingColumnNames) {
+ Collection<Projection> result = new LinkedList<>();
+ for (String each : usingColumnNames) {
+ for (Projection projection : actualColumns) {
+ if (each.equals(projection.getColumnLabel().toLowerCase())) {
+ result.add(projection);
+ break;
+ }
+ }
+ }
+ return result;
+ }
+
+ private Collection<Projection> getRemainingColumns(final
Collection<Projection> actualColumns, final Collection<String>
usingColumnNames) {
+ Collection<Projection> result = new LinkedList<>();
+ for (Projection each : actualColumns) {
+ if
(usingColumnNames.contains(each.getColumnLabel().toLowerCase())) {
+ continue;
+ }
+ result.add(each);
+ }
+ return result;
+ }
+
private void appendAverageDistinctDerivedProjection(final
AggregationDistinctProjection averageDistinctProjection) {
String innerExpression =
averageDistinctProjection.getInnerExpression();
String distinctInnerExpression =
averageDistinctProjection.getDistinctInnerExpression();
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 526b77d8491..a6901ec5118 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
@@ -29,6 +29,7 @@ import
org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.exception.SchemaNotFoundException;
import
org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereSchema;
import org.apache.shardingsphere.sql.parser.sql.common.enums.AggregationType;
+import org.apache.shardingsphere.sql.parser.sql.common.enums.JoinType;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
@@ -211,7 +212,7 @@ public final class ProjectionEngineTest {
}
@Test
- public void
assertGetActualColumnsWhenShorthandProjectionContainsColumnProjectionAndExpressionProjection()
{
+ public void
assertCreateProjectionWhenShorthandProjectionContainsColumnProjectionAndExpressionProjection()
{
ProjectionsSegment subQuerySegment = new ProjectionsSegment(0, 0);
ColumnSegment columnSegment = new ColumnSegment(0, 0, new
IdentifierValue("name"));
subQuerySegment.getProjections().add(new
ColumnProjectionSegment(columnSegment));
@@ -237,4 +238,68 @@ public final class ProjectionEngineTest {
actualColumns.put("nvl(leave_date, '20991231')", new
ExpressionProjection("nvl(leave_date, '20991231')", "leave_date"));
assertThat(((ShorthandProjection) actual.get()).getActualColumns(),
is(actualColumns));
}
+
+ @Test
+ public void
assertCreateProjectionWhenShorthandProjectionContainsJoinUsingColumn() {
+
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),
databaseType).createProjection(createJoinTableSegment(), 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(),
is(crateExpectedColumnsWithoutOwner()));
+ }
+
+ @Test
+ public void
assertCreateProjectionWhenShorthandProjectionContainsJoinUsingColumnAndOwner() {
+
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),
databaseType).createProjection(createJoinTableSegment(), 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() {
+ 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.setJoinType(JoinType.LEFT.name());
+ result.setUsing(Arrays.asList(new ColumnSegment(0, 0, new
IdentifierValue("user_id")), new ColumnSegment(0, 0, new
IdentifierValue("order_id"))));
+ return result;
+ }
+
+ private Map<String, Projection> crateExpectedColumnsWithoutOwner() {
+ 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.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;
+ }
+
+ private Map<String, Projection> crateExpectedColumnsWithOwner() {
+ Map<String, Projection> result = new LinkedHashMap<>();
+ result.put("o.order_id", new ColumnProjection("o", "order_id", null));
+ result.put("o.user_id", new ColumnProjection("o", "user_id", 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;
+ }
}
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 b77c7a62d55..2aff53e2cb9 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
@@ -376,7 +376,8 @@
<assertion parameters="1000:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i
USING(order_id) WHERE o.order_id = ?" db-types="MySQL,PostgreSQL"
scenario-types="tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <!-- TODO support mysql using condition -->
+ <test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i
USING(order_id) WHERE o.order_id = ?" db-types="PostgreSQL"
scenario-types="tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1000:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -651,6 +652,15 @@
<test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON
o.order_id = i.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>
+
+ <!-- TODO support MySQL USING statement -->
+ <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"
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 INNER JOIN t_order_item i
USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 1, 2"
db-types="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" />
@@ -661,15 +671,15 @@
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item i ON
o.order_id = i.item_id WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item i ON
o.order_id = i.order_id 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 RIGHT JOIN t_order_item i ON
o.order_id = i.item_id WHERE i.user_id = ? ORDER BY i.item_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_order_item i ON
o.order_id = i.order_id WHERE i.user_id = ? ORDER BY i.item_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 FULL JOIN t_order_item i ON
o.order_id = i.item_id WHERE o.user_id = ? OR i.user_id = ? ORDER BY
o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o FULL JOIN t_order_item i ON
o.order_id = i.order_id 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>
@@ -681,6 +691,26 @@
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item i
USING(order_id) WHERE o.user_id = ? 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_order o RIGHT JOIN t_order_item i
USING(order_id) 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 FULL JOIN t_order_item i
USING(order_id) 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 INNER JOIN t_merchant m
USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id"
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 INNER JOIN t_merchant m
USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id LIMIT 1, 2"
db-types="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" />
</test-case>
@@ -693,11 +723,11 @@
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_merchant m ON
o.order_id = m.merchant_id WHERE m.country_id = 1 ORDER BY m.merchant_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_merchant m ON
o.merchant_id = m.merchant_id WHERE m.country_id = 1 ORDER BY o.order_id,
m.merchant_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o FULL JOIN t_merchant m ON
o.order_id = m.merchant_id where o.user_id = ? OR m.country_id = 1 ORDER BY
o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o FULL JOIN t_merchant m ON
o.merchant_id = m.merchant_id 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>
@@ -709,6 +739,26 @@
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m
USING(merchant_id) WHERE o.user_id = ? 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_order o RIGHT JOIN t_merchant m
USING(merchant_id) 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 FULL JOIN t_merchant m
USING(merchant_id) 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 INNER JOIN t_product_detail d
USING(product_id) WHERE p.product_id > ? ORDER BY p.product_id DESC"
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 INNER JOIN t_product_detail d
USING(product_id) WHERE p.product_id > ? ORDER BY p.product_id DESC LIMIT 2, 5"
db-types="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>
@@ -728,6 +778,18 @@
<test-case sql="SELECT * FROM t_product p FULL JOIN t_product_detail d ON
d.product_id = p.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 LEFT JOIN t_product_detail d
USING(product_id) WHERE p.category_id = ? ORDER BY p.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 RIGHT JOIN t_product_detail d
USING(product_id) 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 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 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" />
