KomachiSion opened a new issue #4287: ShardingProxy can't get columnMetadata for PostgreSQL URL: https://github.com/apache/incubator-shardingsphere/issues/4287 ## Bug Report Similar problem with #4182 . ### Which version of ShardingSphere did you use? 4.0.0 and 5.0.0-RC1-SNAPSHOT ### Which project did you use? Sharding-JDBC or Sharding-Proxy? Sharding-Proxy ### Expected behavior getColumns interface get right columnMetadata. ### Actual behavior #### For 4.0.0 version Throw exception: ``` line 1:167 no viable alternative at input '(SELECTn.nspname,c.relname,a.attname,a.atttypid,a.attnotnullOR(t.typtype='d'ANDt.typnotnull)ASattnotnull,a.atttypmod,a.attlen,row_number()OVER(' [ERROR] 16:59:15.575 [pool-4-thread-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur: java.lang.UnsupportedOperationException: Cannot support multiple schemas in one SQL at org.apache.shardingsphere.sql.parser.relation.segment.table.TablesContext.setSchema(TablesContext.java:75) at org.apache.shardingsphere.sql.parser.relation.segment.table.TablesContext.<init>(TablesContext.java:63) at org.apache.shardingsphere.sql.parser.relation.statement.impl.CommonSQLStatementContext.<init>(CommonSQLStatementContext.java:41) at org.apache.shardingsphere.sql.parser.relation.statement.impl.SelectSQLStatementContext.<init>(SelectSQLStatementContext.java:79) at org.apache.shardingsphere.sql.parser.relation.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:54) at org.apache.shardingsphere.core.route.router.sharding.ShardingRouter.route(ShardingRouter.java:103) at org.apache.shardingsphere.core.route.PreparedStatementRoutingEngine.route(PreparedStatementRoutingEngine.java:63) at org.apache.shardingsphere.core.PreparedQueryShardingEngine.route(PreparedQueryShardingEngine.java:59) at org.apache.shardingsphere.core.BaseShardingEngine.executeRoute(BaseShardingEngine.java:85) at org.apache.shardingsphere.core.BaseShardingEngine.shard(BaseShardingEngine.java:68) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.wrapper.PreparedStatementExecutorWrapper.doShardingRoute(PreparedStatementExecutorWrapper.java:83) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.wrapper.PreparedStatementExecutorWrapper.route(PreparedStatementExecutorWrapper.java:69) at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:83) at org.apache.shardingsphere.shardingproxy.frontend.postgresql.command.query.binary.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:81) at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:92) at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:72) 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) ``` sql.show don't print. #### For 5.0.0-RC1-SNAPSHOT can't return right result. sql.show is ``` line 1:167 missing ')' at '(' line 1:178 mismatched input 'BY' expecting {')', ','} [INFO ] 16:54:30.806 [pool-4-thread-1] ShardingSphere-SQL - Rule Type: sharding [INFO ] 16:54:30.806 [pool-4-thread-1] ShardingSphere-SQL - Logic SQL: SELECT * FROM (SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, null as attidentity,pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE c.relkind in ('r','p','v','f','m') and a.attnum > 0 AND NOT a.attisdropped AND c.relname LIKE E't_order') c WHERE true AND attname LIKE E'%' ORDER BY nspname,c.relname,attnum [INFO ] 16:54:30.806 [pool-4-thread-1] ShardingSphere-SQL - SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5c510d54, tablesContext=TablesContext(tables=[], schema=Optional.absent())), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.absent())], columnLabels=[]), groupByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.groupby.GroupByContext@72dd9bf7, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@7747736b, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@6a8c3439, containsSubquery=false) [INFO ] 16:54:30.806 [pool-4-thread-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT * FROM (SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, null as attidentity,pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE c.relkind in ('r','p','v','f','m') and a.attnum > 0 AND NOT a.attisdropped AND c.relname LIKE E't_order') c WHERE true AND attname LIKE E'%' ORDER BY nspname,c.relname,attnum ``` ### Reason analyze (If you can) It seems that `getColumns` will be translate to SQL ```sql SELECT * FROM (SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, null as attidentity,pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE c.relkind in ('r','p','v','f','m') and a.attnum > 0 AND NOT a.attisdropped AND c.relname LIKE E't_order') c WHERE true AND attname LIKE E'%' ORDER BY nspname,c.relname,attnum ``` but ShardingSphere can't support this SQL. ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. configuration: ```yaml schemaName: sharding_db dataSources: ds_0: url: jdbc:postgresql://127.0.0.1:5432/demo_ds_0?serverTimezone=UTC&useSSL=false username: root password: 1 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 ds_1: url: jdbc:postgresql://127.0.0.1:5432/demo_ds_1?serverTimezone=UTC&useSSL=false username: root password: 1 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 shardingRule: tables: t_order: actualDataNodes: ds_${0..1}.t_order_${0..1} tableStrategy: inline: shardingColumn: order_id algorithmExpression: t_order_${order_id % 2} keyGenerator: type: SNOWFLAKE column: order_id t_order_item: actualDataNodes: ds_${0..1}.t_order_item_${0..1} tableStrategy: inline: shardingColumn: order_id algorithmExpression: t_order_item_${order_id % 2} keyGenerator: type: SNOWFLAKE column: order_item_id bindingTables: - t_order,t_order_item defaultDatabaseStrategy: inline: shardingColumn: user_id algorithmExpression: ds_${user_id % 2} defaultTableStrategy: none: ``` ### Example codes for reproduce this issue (such as a github link). ```java try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:3307/sharding_db", "root", "root"){ DatabaseMetaData metaData = connection.getMetaData(); ResultSet rs = metaData.getColumns("sharding_db", null, "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: [email protected] With regards, Apache Git Services
