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/08/09 03:49:23 UTC

[GitHub] [shardingsphere] sandynz opened a new issue #6729: PostgreSQL plain insert doesn't work

sandynz opened a new issue #6729:
URL: https://github.com/apache/shardingsphere/issues/6729


   ## Bug Report
   
   ### Which version of ShardingSphere did you use?
   5.0.0-RC1-SNAPSHOT . Commit: acc64bdf0b9b994ca465c2a63a06e6db8221935c
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-JDBC, and part of ShardingSphere-Proxy
   
   ### Expected behavior
   Insert data without exception, insert once.
   
   ### Actual behavior
   Exception got:
   ```
   2020-08-08 21:35:26 [INFO] [main] [ShardingSphere-SQL][73] Actual SQL: ds ::: insert into varchar_tc_0 (id, real_name) values ((?, ?))
   2020-08-08 21:35:26 [INFO] [main] [ShardingSphere-SQL][73] Actual SQL: ds ::: insert into varchar_tc_1 (id, real_name) values ((?, ?))
   Exception in thread "main" org.postgresql.util.PSQLException: 未设定参数值 1 的内容。
   	at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:270)
   	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:296)
   	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
   	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
   	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153)
   	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:119)
   	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
   	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
   	at org.apache.shardingsphere.driver.executor.PreparedStatementExecutor$2.executeSQL(PreparedStatementExecutor.java:117)
   	at org.apache.shardingsphere.driver.executor.PreparedStatementExecutor$2.executeSQL(PreparedStatementExecutor.java:113)
   	at org.apache.shardingsphere.infra.executor.sql.resourced.jdbc.executor.impl.DefaultSQLExecutorCallback.execute0(DefaultSQLExecutorCallback.java:75)
   	at org.apache.shardingsphere.infra.executor.sql.resourced.jdbc.executor.impl.DefaultSQLExecutorCallback.execute(DefaultSQLExecutorCallback.java:57)
   	at org.apache.shardingsphere.infra.executor.kernel.ExecutorKernel.syncExecute(ExecutorKernel.java:99)
   	at org.apache.shardingsphere.infra.executor.kernel.ExecutorKernel.parallelExecute(ExecutorKernel.java:95)
   	at org.apache.shardingsphere.infra.executor.kernel.ExecutorKernel.execute(ExecutorKernel.java:78)
   	at org.apache.shardingsphere.infra.executor.sql.resourced.jdbc.executor.SQLExecutor.execute(SQLExecutor.java:66)
   	at org.apache.shardingsphere.infra.executor.sql.resourced.jdbc.executor.SQLExecutor.execute(SQLExecutor.java:50)
   	at org.apache.shardingsphere.driver.executor.PreparedStatementExecutor.executeUpdate(PreparedStatementExecutor.java:105)
   	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeUpdate(ShardingSpherePreparedStatement.java:179)
   	at test.shardingsphere.sharding.pg.varchar.PgVarcharTcDao.insert(PgVarcharTcDao.java:41)
   	at test.shardingsphere.sharding.pg.varchar.ShardingPgVarcharJdbcTest.main(ShardingPgVarcharJdbcTest.java:25)
   ```
   
   Three problems from log and debugging:
   1. sql parameters not set
   2. sql route to 2 physics tables, but not one
   3. rewritten sql with wrong values clause "((?, ?))", it cause syntax error, it should be "(?, ?)"
   
   ### Reason analyze (If you can)
   PostgreSQL grammar parsing. Details see following comments
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   Run following example, `ShardingPgVarcharJdbcTest` / `ShardingPgVarcharProxyTest`
   
   ### Example codes for reproduce this issue (such as a github link).
   https://github.com/sandynz/shardingsphere-test
   


----------------------------------------------------------------
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] tuohai666 closed issue #6729: PostgreSQL plain insert doesn't work

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


   


----------------------------------------------------------------
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] sandynz commented on issue #6729: PostgreSQL plain insert doesn't work

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


   Reason analyze of "sql parameters not set".
   
   InsertValueContext.parameterCount is 0, since related ExpressionSegment is not instanceof ParameterMarkerExpressionSegment.
   
   ```
   InsertValueContext.java
       private int calculateParametersCount(final Collection<ExpressionSegment> assignments) {
           int result = 0;
           for (ExpressionSegment each : assignments) {
               if (each instanceof ParameterMarkerExpressionSegment) {
                   result++;
               }
           }
           return result;
       }
   ```
   
   ```
   PostgreSQLDMLVisitor.java
       private Collection<ExpressionSegment> createInsertValuesSegments(final ExprListContext ctx) {
           Collection<ExpressionSegment> result = new LinkedList<>();
           if (null != ctx.exprList()) {
               Collection<ExpressionSegment> tmpResult = createInsertValuesSegments(ctx.exprList());
               result.addAll(tmpResult);
           }
           visit(ctx.aExpr());
           CommonExpressionSegment expressionSegment = new CommonExpressionSegment(ctx.aExpr().start.getStartIndex(), ctx.aExpr().stop.getStopIndex(), ctx.aExpr().getText());
           result.add(expressionSegment);
           return result;
       }
   ```
   Underlying expressionSegment result is ignored, and a new CommonExpressionSegment is created.
   
   routeContext example:
   ![routeContext example](https://user-images.githubusercontent.com/42492540/89724609-f9f71f00-da37-11ea-9e61-659ff3d7332b.png)
   


----------------------------------------------------------------
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] tuohai666 commented on issue #6729: PostgreSQL plain insert doesn't work

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


   For MySQL:
   index [46, 51] means **(?, ?)**
   
   For PostgreSQL:
   index [47, 50] means **?, ?**
   
   I guess if we fix the parse problem, the other two problems will be fixed automatically.


----------------------------------------------------------------
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] sandynz commented on issue #6729: PostgreSQL plain insert doesn't work

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


   Reason analyze of "sql route to 2 physics tables, but not one".
   
   ```
   ShardingStandardRoutingEngine.java
       private Collection<DataNode> routeTables(final ShardingRule shardingRule, final TableRule tableRule, final String routedDataSource, final List<RouteValue> tableShardingValues) {
           Collection<String> availableTargetTables = tableRule.getActualTableNames(routedDataSource);
           Collection<String> routedTables = new LinkedHashSet<>(tableShardingValues.isEmpty() ? availableTargetTables
                   : shardingRule.getTableShardingStrategy(tableRule).doSharding(availableTargetTables, tableShardingValues, properties));
           Collection<DataNode> result = new LinkedList<>();
           for (String each : routedTables) {
               result.add(new DataNode(routedDataSource, each));
           }
           return result;
       }
   tableShardingValues is empty, just return availableTargetTables.
   	
       private List<RouteValue> getShardingValuesFromShardingConditions(final ShardingRule shardingRule, final Collection<String> shardingColumns, final ShardingCondition shardingCondition) {
           List<RouteValue> result = new ArrayList<>(shardingColumns.size());
           for (RouteValue each : shardingCondition.getRouteValues()) {
               Optional<BindingTableRule> bindingTableRule = shardingRule.findBindingTableRule(logicTableName);
               if ((logicTableName.equals(each.getTableName()) || bindingTableRule.isPresent() && bindingTableRule.get().hasLogicTable(logicTableName)) 
                       && shardingColumns.contains(each.getColumnName())) {
                   result.add(each);
               }
           }
           return result;
       }
   shardingCondition.getRouteValues() result is empty, so result is empty.
   ```
   
   Trace shardingCondition.routeValues
   ```
   ShardingRouteDecorator.java
       private ShardingConditions getShardingConditions(final List<Object> parameters, final SQLStatementContext sqlStatementContext,
                                                        final SchemaMetaData schemaMetaData, final ShardingRule shardingRule) {
           if (sqlStatementContext.getSqlStatement() instanceof DMLStatement) {
               if (sqlStatementContext instanceof InsertStatementContext) {
                   return new ShardingConditions(new InsertClauseShardingConditionEngine(shardingRule, schemaMetaData).createShardingConditions((InsertStatementContext) sqlStatementContext, parameters));
               }
               return new ShardingConditions(new WhereClauseShardingConditionEngine(shardingRule, schemaMetaData).createShardingConditions(sqlStatementContext, parameters));
           }
           return new ShardingConditions(Collections.emptyList());
       }
   ```
   
   ```
   InsertClauseShardingConditionEngine.java
       private ShardingCondition createShardingCondition(final String tableName, final Iterator<String> columnNames, final InsertValueContext insertValueContext, final List<Object> parameters) {
           ShardingCondition result = new ShardingCondition();
           SPITimeService timeService = new SPITimeService();
           for (ExpressionSegment each : insertValueContext.getValueExpressions()) {
               String columnName = columnNames.next();
               if (shardingRule.isShardingColumn(columnName, tableName)) {
                   if (each instanceof SimpleExpressionSegment) {
                       result.getRouteValues().add(new ListRouteValue<>(columnName, tableName, Collections.singletonList(getRouteValue((SimpleExpressionSegment) each, parameters))));
                   } else if (ExpressionConditionUtils.isNowExpression(each)) {
                       result.getRouteValues().add(new ListRouteValue<>(columnName, tableName, Collections.singletonList(timeService.getTime())));
                   } else if (ExpressionConditionUtils.isNullExpression(each)) {
                       throw new ShardingSphereException("Insert clause sharding column can't be null.");
                   }
               }
           }
           return result;
       }
   insertValueContext.getValueExpressions() elements are CommonExpressionSegment,
   not instanceof SimpleExpressionSegment, it should be ParameterMarkerExpressionSegment,
   the same reason as problem 1.
   ```
   createShardingCondition example:
   ![createShardingCondition example](https://user-images.githubusercontent.com/42492540/89725272-45adc680-da40-11ea-80ef-4e3fc87dab34.png)
   
   
   


----------------------------------------------------------------
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] tuohai666 edited a comment on issue #6729: PostgreSQL plain insert doesn't work

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


   For MySQL:
   index [46, 51] means `(?, ?)`
   
   For PostgreSQL:
   index [47, 50] means `?, ?`
   
   I guess if we fix the parse problem, the other two problems will be fixed automatically.


----------------------------------------------------------------
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] sandynz commented on issue #6729: PostgreSQL plain insert doesn't work

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


   Reason analyze of: rewritten sql with wrong values clause "((?, ?))", it cause syntax error, it should be "(?, ?)"
   
   Comparing PostgreSQL and MySQL execution process, some screenshots:
   
   ![pg-ctx](https://user-images.githubusercontent.com/42492540/89727752-44899300-da5a-11ea-934b-120812b7a744.png)
   ![mysql-ctx](https://user-images.githubusercontent.com/42492540/89727775-553a0900-da5a-11ea-9971-95102412b084.png)
   ShardingInsertValuesToken have different startIndex and stopIndex.
   
   ![pg-token](https://user-images.githubusercontent.com/42492540/89727797-7c90d600-da5a-11ea-9d15-cdc4a6d0b891.png)
   ![mysql-token](https://user-images.githubusercontent.com/42492540/89727800-83b7e400-da5a-11ea-9b74-1f692d9a1c5a.png)
   
   ```
   ShardingInsertValuesTokenGenerator.java
       @Override
       public InsertValuesToken generateSQLToken(final InsertStatementContext insertStatementContext) {
           Collection<InsertValuesSegment> insertValuesSegments = (insertStatementContext.getSqlStatement()).getValues();
           InsertValuesToken result = new ShardingInsertValuesToken(getStartIndex(insertValuesSegments), getStopIndex(insertValuesSegments));
           Iterator<Collection<DataNode>> originalDataNodesIterator = null == routeContext || routeContext.getRouteResult().getOriginalDataNodes().isEmpty()
                   ? null : routeContext.getRouteResult().getOriginalDataNodes().iterator();
           for (InsertValueContext each : insertStatementContext.getInsertValueContexts()) {
               List<ExpressionSegment> expressionSegments = each.getValueExpressions();
               Collection<DataNode> dataNodes = null == originalDataNodesIterator ? Collections.emptyList() : originalDataNodesIterator.next();
               result.getInsertValues().add(new ShardingInsertValue(expressionSegments, dataNodes));
           }
           return result;
       }
   ShardingInsertValuesToken is initialized here, index from InsertValuesSegment
   ```
   
   ```
   PostgreSQLDMLVisitor.java
       private Collection<InsertValuesSegment> createInsertValuesSegments(final ValuesClauseContext ctx) {
           Collection<InsertValuesSegment> result = new LinkedList<>();
           if (null != ctx.valuesClause()) {
               Collection<InsertValuesSegment> expressions = createInsertValuesSegments(ctx.valuesClause());
               result.addAll(expressions);
           }
           Collection<ExpressionSegment> expressions = createInsertValuesSegments(ctx.exprList());
           InsertValuesSegment insertValuesSegment = new InsertValuesSegment(ctx.exprList().start.getStartIndex(), ctx.exprList().stop.getStopIndex(), (List<ExpressionSegment>) expressions);
           result.add(insertValuesSegment);
           return result;
       }
   ctx.exprList().start.getStartIndex(), ctx.exprList().stop.getStopIndex(), 47,50
   insert into varchar_tc (id, real_name) values (?, ?) , substring(46,52) is "(?, ?)"
   ```
   
   Seems it caused by DMLStatement.g4, don't know exact reason
   


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