AlbertTao opened a new issue #2258: Unknown column error occurred when select field from table without alias URL: https://github.com/apache/incubator-shardingsphere/issues/2258 ## Unknown column error occurred when select field from table without alias When execute sql as follow : ```mysql select ts_order.id from ts_order where id = 1 ``` Get error : Unknown column 'ts_order.id' in 'field list' if I use sql like this : ```sql select td.id from ts_order td where id = 1 ``` Will get right result with out error. ### Which version of ShardingSphere did you use? 4.0.0-RC1 ### Which project did you use? Sharding-JDBC or Sharding-Proxy? Sharding-JDBC ### Expected behavior When I use version 3.0.1 get right result : ```txt [main] INFO ShardingSphere-SQL - Actual SQL: ds_0 ::: select ts_order_0001.id from ts_order_0001 where id = 1 ------------------------------------------------------------ id 1 ------------------------------------------------------------ ``` ### Actual behavior ```txt [main] INFO ShardingSphere-SQL - Actual SQL: ds_0 ::: select ts_order.id from ts_order_0001 where id = 1 Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'ts_order.id' in 'field list' ``` ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. **Dependency** ```xml <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.9</version> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>4.0.0-RC1</version> </dependency> ``` **SQL** ```sql -- prepare schemas create database test_d_0; create database test_d_1; -- prepare tables -- prepare table ts_order CREATE TABLE test_d_0.`ts_order_0000` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)); CREATE TABLE test_d_0.`ts_order_0001` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)); CREATE TABLE test_d_1.`ts_order_0002` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)); CREATE TABLE test_d_1.`ts_order_0003` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)); -- prepare init data INSERT INTO `test_d_0`.`ts_order_0001` (`id`) VALUES ('1'); ``` **Java Code** ```java // ignore package and import public class ShardingTest { public static void main(String[] args) throws Exception { queryTest("sharding-config-table-alias-test.yaml", "select ts_order.id from ts_order where id = 1"); } private static void queryTest(String conifgResourcePath, String querySql) throws Exception { String shardingConfig = loadFromResource(conifgResourcePath); DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(shardingConfig.getBytes("utf-8")); Connection connection = dataSource.getConnection(); Statement st = connection.createStatement(); ResultSet rs = st.executeQuery(querySql); showData(rs); connection.close(); } private static String loadFromResource(String path) throws Exception{ BufferedReader reader = new BufferedReader(new InputStreamReader(Class.class .getResourceAsStream("/" + path), "utf-8")); StringBuffer sb = new StringBuffer(); CharBuffer charBuffer = CharBuffer.allocate(1024); for (int count = reader.read(charBuffer); count > 0; count = reader.read(charBuffer)) { sb.append(charBuffer.flip()); } return sb.toString(); } private static void showData(ResultSet rs) throws SQLException { System.out.println("------------------------------------------------------------"); int colCount = rs.getMetaData().getColumnCount(); for (int i = 0; i < colCount; i++) { System.out.print(String.format("%-20s", rs.getMetaData().getColumnName(i + 1))); } System.out.println(); int dataCount = 0; while (rs.next()) { dataCount++; for (int i = 0; i < colCount; i++) { System.out.print(String.format("%-20s", rs.getObject(i + 1) + " ")); } System.out.println(); } System.out.println("------------------------------------------------------------"); System.out.println("Get Data rows " + dataCount); } } ``` **Sharding Rule** sharding-config-table-alias-test.yaml ```yaml dataSources: ds_0: !!com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/test_d_0 username: root password: root135 ds_1: !!com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/test_d_1 username: root password: root135 shardingRule: tables: ts_order: actualDataNodes: ds_0.ts_order_000${0..1},ds_1.ts_order_000${2..3} databaseStrategy: inline: shardingColumn: id algorithmExpression: ds_${new BigDecimal(id).abs().divideAndRemainder(4)[1].longValue().intdiv(2)} tableStrategy: inline: shardingColumn: id algorithmExpression: ts_order_${String.format("%04d",new BigDecimal(id).abs().divideAndRemainder(4)[1].longValue())} props: sql.show: true ``` ### 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
