[GitHub] [shardingsphere] chana71 commented on issue #6497: Can subqueries be supported in select？

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

chana71 commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-692548058


   @tristaZero @jingshanglu Hi, I have met these subquery sqls not working very well. Could you help with this? 
   Following are errors  given by proxy when executing the example sql. Tested on master branch. 
   1. error-must have sharding column in subquery  ,`select count(0) from (select * from t_order where createdDate > '2020-02-01') a;`  
   2. error-Can not find owner from table , `select count(*) from (select * from t_order) a join (select * from t_order)b on a.id = b.id where a.id=10;`
   3. limit syntax in subquery,  not rewrite as the log shows and will  return 40 rows finally ------------ `select id from (select * from t_order limit 10)a` 
   ```
   Actual SQL: ds0 ::: select id from (select * from t_order limit 10)a
   Actual SQL: ds1 ::: select id from (select * from t_order limit 10)a
   Actual SQL: ds2 ::: select id from (select * from t_order limit 10)a
   Actual SQL: ds3 ::: select id from (select * from t_order limit 10)a
   ```
   
   config
   ```
   
   t_order:
           actualDataNodes: ds${0..3}.t_order
           databaseStrategy:
             standard:
               shardingAlgorithmName: database_mod
               shardingColumn: id
   ```


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