You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/05/30 14:31:09 UTC

[GitHub] [shardingsphere] hongfuli opened a new issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

hongfuli opened a new issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857


   Which version of ShardingSphere did you use?
   4.1.0
   
   Which project did you use? Sharding-JDBC or Sharding-Proxy?
   Sharding-Proxy
   
   Steps to reproduce the behavior
   
   when executing the follow sql: 
   ```sql
   SELECT `t_order`.`user_id`, `t_order`.`order_id`, `t_order`.`name` FROM `t_order` ORDER BY `t_order`.`order_id` ASC  LIMIT 1;
   ```
   the follow error is raised:
   ```bash
   ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'
   ```


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



[GitHub] [shardingsphere] yu199195 commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
yu199195 commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636420246


   please.show you sharding config,  and  you use db  is mysql ,pgsql?


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



[GitHub] [shardingsphere] kimmking commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-642553175


   @jingshanglu will track it.


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



[GitHub] [shardingsphere] hongfuli commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
hongfuli commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636438637


   meanwhile, I found that `shardingsphere` is not good dealt with the sql style with schema prefix, for example:
   
   ```sql
   DELETE FROM t_order WHERE order_id IN (1);
   ```
   is ok, but
   ```sql
   DELETE FROM `t_order` WHERE `t_order`.`order_id` IN (1);
   ```
   is not work with the follow error:
   ```java
   org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.statement.dml.DeleteStatement@3293c2dc'.
   	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:72)
   	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:69)
   	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:53)
   	at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:91)
   	at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
   	at org.apache.shardingsphere.underlying.pluggble.prepare.SimpleQueryPrepareEngine.route(SimpleQueryPrepareEngine.java:54)
   	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
   	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
   	at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.wrapper.StatementExecutorWrapper.doShardingRoute(StatementExecutorWrapper.java:80)
   	at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.wrapper.StatementExecutorWrapper.route(StatementExecutorWrapper.java:63)
   	at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:74)
   	at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.execute(QueryBackendHandler.java:52)
   	at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:73)
   	at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:93)
   	at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
   	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
   	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
   	at java.lang.Thread.run(Thread.java:748)
   ```


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



[GitHub] [shardingsphere] hongfuli commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
hongfuli commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636868887


   ok, thanks


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



[GitHub] [shardingsphere] hongfuli commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
hongfuli commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636595030


   the project is not based java.


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



[GitHub] [shardingsphere] yu199195 commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
yu199195 commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636439909


   ok,we'll keep track of it and fix it in the near future, you can use unprefixed ones for the time being.


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



[GitHub] [shardingsphere] jingshanglu commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-642662024


   I'll track it.


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



[GitHub] [shardingsphere] hongfuli commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
hongfuli commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636440461


   very unfortunately I use orm framework to access db, I can't completely control the style of sql. :(


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



[GitHub] [shardingsphere] kimmking edited a comment on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636694511


   Cases as we know:
   
   - [ ] `select status from t_order order by t_order.id asc;`
   - [ ] `select status from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order.id asc;`
   - [ ] `select t_order.t_order_id+1 from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order_item.order_id asc;  # two wrong prefix`  
   - [ ] `select t_order.order_id+1 from t_order;`
   


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



[GitHub] [shardingsphere] kimmking commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636694511


   Cases as we know:
   
   - [ ] `select t_order.status from t_order order by t_order.id asc;`
   - [ ] `select t_order.status from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order.id asc;`
   - [ ] `select status,t_order.order_id+1 from t_order;`
   


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



[GitHub] [shardingsphere] kimmking edited a comment on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636694511


   Cases as we know:
   
   - [ ] `select t_order.status from t_order order by t_order.id asc;`
   - [ ] `select t_order.status from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order.id asc;`
   - [ ] `select t_order.t_order_id+1 from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order_item.order_id asc;`  ## two wrong prefix
   - [ ] `select status,t_order.order_id+1 from t_order;`
   


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



[GitHub] [shardingsphere] hongfuli edited a comment on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
hongfuli edited a comment on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636438637


   meanwhile, I found that `shardingsphere` is not good compatible with the sql style with schema prefix, for example:
   
   ```sql
   DELETE FROM t_order WHERE order_id IN (1);
   ```
   is ok, but
   ```sql
   DELETE FROM `t_order` WHERE `t_order`.`order_id` IN (1);
   ```
   is not work with the follow error:
   ```java
   org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.statement.dml.DeleteStatement@3293c2dc'.
   	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:72)
   	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:69)
   	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:53)
   	at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:91)
   	at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
   	at org.apache.shardingsphere.underlying.pluggble.prepare.SimpleQueryPrepareEngine.route(SimpleQueryPrepareEngine.java:54)
   	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
   	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
   	at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.wrapper.StatementExecutorWrapper.doShardingRoute(StatementExecutorWrapper.java:80)
   	at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.wrapper.StatementExecutorWrapper.route(StatementExecutorWrapper.java:63)
   	at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:74)
   	at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.execute(QueryBackendHandler.java:52)
   	at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:73)
   	at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:93)
   	at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
   	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
   	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
   	at java.lang.Thread.run(Thread.java:748)
   ```


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



[GitHub] [shardingsphere] yu199195 commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
yu199195 commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636448002


   > very unfortunately I use orm framework to access db, I can't completely control the style of sql
   
   you can use shrding-jdbc. sharding-proxy,  I'm not sure I support that now.


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



[GitHub] [shardingsphere] kimmking commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636637395


   I reproduce it now.
   And will fix it in 4.1.1.


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



[GitHub] [shardingsphere] kimmking commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636709182


   
   @hongfuli  As an alternative choice, we suggest:
   - Plan A: You can remove all prefix of column: SELECT `user_id`, `order_id`, `name` FROM `t_order` ORDER BY `order_id` ASC  ;
   - Plan B: You also can add an alias for  table: SELECT `t_order`.`user_id`, `t_order`.`order_id`, `t_order`.`name` FROM `t_order` as `t_order`  ORDER BY `t_order`.`order_id` ASC  ;


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



[GitHub] [shardingsphere] hongfuli commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
hongfuli commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636817814


   thanks for your suggestion.
   But I use the orm framework based python, the generated SQL is not under my control


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



[GitHub] [shardingsphere] tristaZero closed issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857


   


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



[GitHub] [shardingsphere] hongfuli commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
hongfuli commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636655481


   Great, thank you


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



[GitHub] [shardingsphere] kimmking edited a comment on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636694511


   The root of this issue is derived projection is an expression projection, and expression will be generated as a string before rewriting, then it keep the original style to be a part of rewrited actual SQL.
   
   Cases as we know:
   
   - [ ] `select status from t_order order by t_order.id asc;`
   - [ ] `select status from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order.id asc;`
   - [ ] `select t_order.t_order_id+1 from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order_item.order_id asc;  # two wrong prefix`  
   - [ ] `select t_order.order_id+1 from t_order;`
   


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



[GitHub] [shardingsphere] kimmking edited a comment on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636694511


   The root of this issue is derived projection is an expression projection, and expression will be generated as a string before rewriting, then it keep the original style to be a part of rewrited actual SQL.
   
   Cases as we know:
   
   - [ ] 1. `select status from t_order order by t_order.id asc;`
   - [ ] 2. `select status from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order.id asc;`
   - [ ] 3. `select t_order.t_order_id+1 from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order_item.order_id asc;  # two wrong prefix`  
   - [ ] 4. `select t_order.order_id+1 from t_order;`
   
   we will try to fix case 1&2


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



[GitHub] [shardingsphere] kimmking commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636846919


   After make a deep investigation, it need to make more time to fix it, so we will pending it for the latest release version, and move to version 5.0.0.


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



[GitHub] [shardingsphere] hongfuli commented on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
hongfuli commented on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636433145


   default configuration of the official release without any changes, and mysql


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



[GitHub] [shardingsphere] kimmking edited a comment on issue #5857: ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #5857:
URL: https://github.com/apache/shardingsphere/issues/5857#issuecomment-636694511


   Cases as we know:
   
   - [ ] `select t_order.status from t_order order by t_order.id asc;`
   - [ ] `select t_order.status from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order.id asc;`
   - [ ] `select t_order.t_order_id+1 from t_order,t_order_item where t_order.order_id=t_order_item.order_id order by t_order_item.order_id asc;  # two wrong prefix`  
   - [ ] `select status,t_order.order_id+1 from t_order;`
   


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