ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

[mty2015]

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:

SELECT `t_order`.`user_id`, `t_order`.`order_id`, `t_order`.`name` FROM `t_order` ORDER BY `t_order`.`order_id` ASC  ;

the follow error is raised:

ERROR 1054 (42S22): Unknown column 't_order.order_id' in 'field list'

the actual sql is wrong:

SELECT `t_order_0`.`user_id`, `t_order_0`.`order_id`, `t_order_0`.`name` , `t_order`.`order_id` AS ORDER_BY_DERIVED_0 FROM `t_order_0` ORDER BY `t_order_0`.`order_id` ASC

[yu199195]

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

[mty2015]

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

[mty2015]

meanwhile, I found that shardingsphere is not good compatible with the sql style with schema prefix, for example:

DELETE FROM t_order WHERE order_id IN (1);

is ok, but

DELETE FROM `t_order` WHERE `t_order`.`order_id` IN (1);

is not work with the follow error:

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)

[yu199195]

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

[mty2015]

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

[yu199195]

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.

[mty2015]

the project is not based java.

[kimmking]

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

[mty2015]

Great, thank you

[kimmking]

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

[kimmking]

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

[mty2015]

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

[kimmking]

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.

[mty2015]

ok, thanks

[kimmking]

@jingshanglu will track it.

[jingshanglu]

I'll track it.