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