[I] WITH clause RECURSIVE subquery Shorthand Projection Error [shardingsphere]

["Yash-cor (via GitHub)" <gi...@apache.org>]

Yash-cor opened a new issue, #35164:
URL: https://github.com/apache/shardingsphere/issues/35164

   ## Bug Report
   
   **For English only**, other languages will not accept.
   
   Before report a bug, make sure you have:
   
   - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
   - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
   
   Please pay attention on issues you submitted, because we maybe need more details. 
   If no response anymore and we cannot reproduce it on current information, we will **close it**.
   
   Please answer these questions before submitting your issue. Thanks!
   
   ### Which version of ShardingSphere did you use?
   
   5.5.3-SNAPSHOT
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   
   ShardingSphere-JDBC and ShardingSphere-Proxy
   
   ### Expected behavior
   
   `WITH RECURSIVE cte AS ( SELECT * from t_user UNION ALL  SELECT user_id+1, name  FROM cte WHERE user_id < 5  ) SELECT * FROM cte`
   
   Above query runs successfully without showing any Error
   
   ### Actual behavior
   
   ![Image](https://github.com/user-attachments/assets/935053e0-a445-4c97-bf43-ba86657ed178)
   
   ### Reason analyze (If you can)
   
   ```
     if (recursive && segment.getAliasName().isPresent()) {
                 binderContext.getExternalTableBinderContexts().put(
     new CaseInsensitiveString(segment.getAliasName().get()), new SimpleTableSegmentBinderContext(segment.getColumns().stream().map(ColumnProjectionSegment::new).collect(Collectors.toList()), TableSourceType.TEMPORARY_TABLE));
   }
   
   ```
   
   In this when getExternalTableBinderContexts() is created Shorthand Projections are not caught correctly as Subquery is not binded first but for subquery to be binded we need getExternalTableBinderContexts
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   ### Example codes for reproduce this issue (such as a github link).
   


-- 
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.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] WITH clause RECURSIVE subquery Unknown column exception when shorhand projection are used. [shardingsphere]

["strongduanmu (via GitHub)" <gi...@apache.org>]

strongduanmu commented on issue #35164:
URL: https://github.com/apache/shardingsphere/issues/35164#issuecomment-2803530303

   Hi @Yash-cor, can you explain the meaning of this sql - `WITH RECURSIVE cte AS ( SELECT * from t_user UNION ALL  SELECT user_id+1, name  FROM cte WHERE user_id < 5  ) SELECT * FROM cte`?


-- 
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.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] WITH clause RECURSIVE subquery Unknown column exception when shorhand projection are used. [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu closed issue #35164: WITH clause RECURSIVE subquery Unknown column exception when shorhand projection are used.
URL: https://github.com/apache/shardingsphere/issues/35164


-- 
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.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] WITH clause RECURSIVE subquery Unknown column exception when shorhand projection are used. [shardingsphere]

["Yash-cor (via GitHub)" <gi...@apache.org>]

Yash-cor commented on issue #35164:
URL: https://github.com/apache/shardingsphere/issues/35164#issuecomment-2803841494

   > Hi [@Yash-cor](https://github.com/Yash-cor), can you explain the meaning of this sql - `WITH RECURSIVE cte AS ( SELECT * from t_user UNION ALL SELECT user_id+1, name FROM cte WHERE user_id < 5 ) SELECT * FROM cte`?
   
   Ok so t_user is the physical table which exists in our database and it contains two columns `user_id` and `name`.
   By using the RECURSIVE segment we can apply recursion in our queries to create temporary table.
   
   RECURSIVE subquery contains two parts one non recursive part - `SELECT * from t_user`
   Other is recursive part - `SELECT user_id+1, name FROM cte WHERE user_id < 5`
   In recursive part we have to use the same common table expression alias table.
   
   And it is necessary to combine them with UNION.
   
   For Example if t_user contains  - (1, 'Yash').
   
   Now this part starts using the rows from cte (which initially has just (1, 'Yash')) and will keep generating:
   
   (2, 'Yash')
   
   (3, 'Yash')
   
   (4, 'Yash')
   
   (5, 'Yash')
   
   Because the condition is user_id < 5, it stops once user_id becomes 5.


-- 
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.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org