lwtdev commented on issue #7880:
URL: https://github.com/apache/shardingsphere/issues/7880#issuecomment-738481874
@jingshanglu Hi, I have redesigned the `expr` assertion, and then I will add
its implementation, add more test cases and readjust the old test cases.
I hope you can review the main part of the design before proceeding to the
next work.
- **ExpectedExpression**
All sub-expressions will be in the first level. It's make `expr` more
conducive to reuse.
```java
public class ExpectedExpression extends AbstractExpectedSQLSegment {
@XmlElement(name = "between-expression")
private ExpectedBetweenExpression betweenExpression;
@XmlElement(name = "binary-operation-expression")
private ExpectedBinaryOperationExpression binaryOperationExpression;
@XmlElement(name = "column")
private ExpectedColumn column;
@XmlElement(name = "common-expression")
private ExpectedCommonExpression commonExpression;
@XmlElement(name = "exists-subquery")
private ExpectedExistsSubquery existsSubquery;
@XmlElement(name = "expression-projection")
private ExpectedExpressionProjection expressionProjection;
@XmlElement(name = "in-expression")
private ExpectedInExpression inExpression;
@XmlElement(name = "list-expression")
private ExpectedListExpression listExpression;
@XmlElement(name = "literal-expression")
private ExpectedLiteralExpression literalExpression;
@XmlElement(name = "not-expression")
private ExpectedNotExpression notExpression;
@XmlElement(name = "parameter-marker-expression")
private ExpectedParameterMarkerExpression parameterMarkerExpression;
@XmlElement(name = "subquery")
private ExpectedSubquery subquery;
}
```
- **OtherExpression**
Only `ExpectedBinaryOperationExpression` is displayed here, and others are
similar to it.
```java
public class ExpectedBinaryOperationExpression extends
AbstractExpectedSQLSegment {
@XmlElement(name = "left")
private ExpectedExpression left;
@XmlElement(name = "operator")
private ExpectedOperator operator;
@XmlElement(name = "right")
private ExpectedExpression right;
}
```
- **Test Case Example**
This is an example that includes all types of sub-expressions.
- `SQL`
```sql
SELECT * FROM t_order
WHERE (SELECT order_id FROM t_order_item WHERE status > ?)
OR EXISTS (SELECT order_id FROM t_order_item)
OR order_id ->"$[1]"
OR NOT order_id IN (1, 2, 3)
OR order_id BETWEEN 1 AND 3
OR order_id + INTERVAL 1 SECOND;
```
- `Test Case`
```xml
<select>
<from start-index="14" stop-index="20">
<simple-table name="t_order" start-index="14" stop-index="20" />
</from>
<projections distinct-row="false" start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
</projections>
<where start-index="22" stop-index="235">
<expr start-index="28" stop-index="235">
<binary-operation-expression start-index="28" stop-index="235">
<left start-index="28" stop-index="203">
<binary-operation-expression start-index="28"
stop-index="203">
<left start-index="28" stop-index="175">
<binary-operation-expression start-index="28"
stop-index="175">
<left start-index="28" stop-index="146">
<binary-operation-expression start-index="28"
stop-index="146">
<left start-index="28" stop-index="125">
<binary-operation-expression
start-index="28" stop-index="125">
<left start-index="28" stop-index="79">
<subquery start-index="28"
stop-index="79">
<select>
<from start-index="50"
stop-index="61">
<simple-table
name="t_order_item" start-index="50" stop-index="61" />
</from>
<projections
distinct-row="false" start-index="36" stop-index="43">
<column-projection
name="order_id" start-index="36" stop-index="43" />
</projections>
<where start-index="63"
stop-index="78">
<expr start-index="69"
stop-index="78">
<binary-operation-expression start-index="69" stop-index="78">
<left start-index="69"
stop-index="74">
<column
name="status" start-index="69" stop-index="74" />
</left>
<operator type=">"
/>
<right
start-index="78" stop-index="78">
<parameter-marker-expression value="0" start-index="78" stop-index="78" />
</right>
</binary-operation-expression>
</expr>
</where>
</select>
</subquery>
</left>
<operator type="OR" />
<right start-index="84" stop-index="125">
<exists-subquery start-index="84"
stop-index="125">
<not>false</not>
<subquery start-index="91"
stop-index="125">
<select>
<from start-index="113"
stop-index="124">
<simple-table
name="t_order_item" start-index="113" stop-index="124" />
</from>
<projections
distinct-row="false" start-index="99" stop-index="106">
<column-projection
name="order_id" start-index="99" stop-index="106" />
</projections>
</select>
</subquery>
</exists-subquery>
</right>
</binary-operation-expression>
</left>
<operator type="OR" />
<right start-index="130" stop-index="146">
<common-expression text="order_id
->"$[1]"" start-index="130" stop-index="146" />
</right>
</binary-operation-expression>
</left>
<operator type="OR" />
<right start-index="151" stop-index="175">
<in-expression start-index="151" stop-index="175">
<left start-index="151" stop-index="162">
<not-expression start-index="151"
stop-index="162">
<expr start-index="155" stop-index="162">
<column name="order_id"
start-index="155" stop-index="162" />
</expr>
</not-expression>
</left>
<not>false</not>
<right start-index="167" stop-index="175">
<list-expression start-index="167"
stop-index="175">
<items start-index="168"
stop-index="168">
<literal-expression value="1"
start-index="168" stop-index="168" />
</items>
<items start-index="171"
stop-index="171">
<literal-expression value="2"
start-index="171" stop-index="171" />
</items>
<items start-index="174"
stop-index="174">
<literal-expression value="3"
start-index="174" stop-index="174" />
</items>
</list-expression>
</right>
</in-expression>
</right>
</binary-operation-expression>
</left>
<operator type="OR" />
<right start-index="180" stop-index="203">
<between-expression start-index="180" stop-index="203">
<left start-index="180" stop-index="187">
<column name="order_id" start-index="180"
stop-index="187" />
</left>
<between-expr start-index="197" stop-index="197">
<literal-expression value="1" start-index="197"
stop-index="197" />
</between-expr>
<and-expr start-index="203" stop-index="203">
<literal-expression value="3" start-index="203"
stop-index="203" />
</and-expr>
<not>false</not>
</between-expression>
</right>
</binary-operation-expression>
</left>
<operator type="OR" />
<right start-index="208" stop-index="235">
<binary-operation-expression start-index="208"
stop-index="235">
<left start-index="208" stop-index="215">
<column name="order_id" start-index="208"
stop-index="215" />
</left>
<operator type="+" />
<right start-index="219" stop-index="235">
<expression-projection start-index="219"
stop-index="235">
<text>INTERVAL1SECOND</text>
</expression-projection>
</right>
</binary-operation-expression>
</right>
</binary-operation-expression>
</expr>
</where>
</select>
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
