[GitHub] [incubator-shardingsphere] tristaZero commented on issue #4852: Add start-index and stop-index assertion for OrderByItem

[GitBox <gi...@apache.org>]

tristaZero commented on issue #4852: Add start-index and stop-index assertion for OrderByItem
URL: https://github.com/apache/incubator-shardingsphere/issues/4852#issuecomment-601115961
 
 
   This issue is to count the `start-index` and `stop-index` to assert `IndexOrderByItem ` and `ExpressionOrderByItem`
   
   ### How to find the targets to be fixed?
   1. Open class `OrderByItemAssert` and clear the comment of `SQLSegmentAssert.assertIs(assertContext, actual, expected)` in `assertIndexOrderByItem` and `assertExpressionOrderByItem`.
   2. run `SQLParserParameterizedTest`
   3. All the SQL cases which have the error assertion results are our targets to be fixed.
   
   ### How to fix the above assertion results?
   1. Look at the exception log and find the corresponding `SQL Case ID` and `SQL`.
   2. Find out the `parsing result` of this `SQL Case ID` in `./incubator-shardingsphere/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/test/resources/sql/dml`
   3. Calculate the correct `start-index` and `stop-index` of `index-item` and `expression-item` segments of this `SQL`.
   4. Add the calculated `start-index` and `stop-index` labels of `index-item` and `expression-item` segments to `parsing result` of this `SQL Case ID`, like
   ```
               <index-item index="2" order-direction="DESC" start-index="10" stop-index="11" />
   ```
   5. Add the correct `literal-start-index` and `literal-stop-index` of `index-item` and `expression-item` segments to `parsing result` of this `SQL Case ID` when it is necessary, like
   ```
               <index-item index="2" order-direction="DESC"  literal-start-index="10" literal-stop-index="11" />
   ```
   6. Run `SQLParserParameterizedTest` again to check whether this `SQL Case ID` does not stay in the list of `SQL` with  `bad assertion result`.
   
   ### Note
   
   1. What are the definitions of `index-item` and `expression-item` segments?
   
   For instance, here is a SQL,
   > SELECT user_id, SUM(order_id) AS orders_sum FROM t_order GROUP BY user_id ORDER BY SUM(order_id) LIMIT 5
   
   The value of `SUM(order_id)` is a `expression-item`, which always locates behind `order by` or `group by` clause.
   
   >  SELECT user_id, SUM(order_id) AS orders_sum FROM t_order ORDER BY 1
   
   The value of `1` is `index-item`, which means item index used for `order by` or `group by`.
   
   2. What is the difference between `literal-start-index` and `start-index`?
   
   When a SQL does not contain parameter maker, i.e.,`?`, this SQL only has one form; Otherwise, you will see two forms of this SQL, i.e, a SQL form filled with parameter and a SQL form only with parameter marks. For instance,
   ```
   Case1 One SQL without parameter mark,
   select * from table where id = 1;
   
   Case2 One SQL with parameter mark,
   Original SQL: select * from table where id = ?;
   SQL filled with Parameter: select * from table where id = 1;
   ```
   Replace `the parameter mark` with `the actual parameter` will change the position of the segment. Therefore we need to add `start-index` to represent the start position of a segment for `Original SQL`, and `literal-start-index` is used to represent the start position of a segment for `SQL filled with Parameter`.
   

----------------------------------------------------------------
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:
users@infra.apache.org


With regards,
Apache Git Services