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 2022/10/10 06:42:47 UTC
[GitHub] [shardingsphere] cherubimLV opened a new issue, #13929: Insert sql and use function to cause parameter loss problem
cherubimLV opened a new issue, #13929:
URL: https://github.com/apache/shardingsphere/issues/13929
### version：5.0.0
### project：ShardingSphere-JDBC
### Expected behavior：
Use sql with mysql function to insert data, for example：
```
insert into user (id,name,phone,create_time,create_id)
value
(#{id},#{name},#{phone},FROM_UNIXTIME(#{createTime}/1000,'%Y-%m-%d %H:%i:%S'),#{createId})
```
The parameters passed in strictly in accordance with normal conditions should be able to execute successfully.
But the actual situation is that the parameter ``#{createTime}`` will be lost, causing an exception to be thrown:
Error updating database. Cause: java.sql.SQLException: No value specified for parameter 5。
After searching for the reason, it was found in the method:
``org.apache.shardingsphere.sql.parser.sql.common.util.ExpressionExtractUtil#getParameterMarkerExpressions``
```java
public static List<ParameterMarkerExpressionSegment> getParameterMarkerExpressions(final Collection<ExpressionSegment> expressions) {
List<ParameterMarkerExpressionSegment> result = new ArrayList<>();
for (ExpressionSegment each : expressions) {
//There is no support for ExpressionProjectionSegment
if (each instanceof ParameterMarkerExpressionSegment) {
result.add((ParameterMarkerExpressionSegment) each);
}
// TODO support more expression type if necessary
if (each instanceof BinaryOperationExpression) {
result.addAll(getParameterMarkerExpressions(Collections.singletonList(((BinaryOperationExpression) each).getLeft())));
result.addAll(getParameterMarkerExpressions(Collections.singletonList(((BinaryOperationExpression) each).getRight())));
}
}
return result;
}
```
After inspection, it was found that the method:
``org.apache.shardingsphere.sql.parser.mysql.visitor.statement.impl.MySQLStatementSQLVisitor#visitFunctionCall``
The parameter objects generated under the method are all ``ExpressionProjectionSegment``, which leads to the fact that the parameters used in the function call when inserting are not added to ``List<ParameterMarkerExpressionSegment>``.
When the object ``org.apache.shardingsphere.infra.rewrite.context.SQLRewriteContext#SQLRewriteContext`` is created later, as long as it is ``instanceof InsertStatementContext``, it will execute ``new GroupedParameterBuilder(
((InsertStatementContext) sqlStatementContext).getGroupedParameters(), ((InsertStatementContext) sqlStatementContext).getOnDuplicateKeyUpdateParameters())``For parameter construction, the number of parameters inserted into the function will be lost at the beginning
```java
public SQLRewriteContext(final ShardingSphereSchema schema, final SQLStatementContext<?> sqlStatementContext, final String sql, final List<Object> parameters) {
this.schema = schema;
this.sqlStatementContext = sqlStatementContext;
this.sql = sql;
this.parameters = parameters;
addSQLTokenGenerators(new DefaultTokenGeneratorBuilder().getSQLTokenGenerators());
parameterBuilder = ((sqlStatementContext instanceof InsertStatementContext) && (null == ((InsertStatementContext) sqlStatementContext).getInsertSelectContext()))
? new GroupedParameterBuilder(
((InsertStatementContext) sqlStatementContext).getGroupedParameters(), ((InsertStatementContext) sqlStatementContext).getOnDuplicateKeyUpdateParameters())
: new StandardParameterBuilder(parameters);
}
```
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN closed issue #13929: Insert sql and use function to cause parameter loss problem
Posted by GitBox <gi...@apache.org>.
TeslaCN closed issue #13929: Insert sql and use function to cause parameter loss problem
URL: https://github.com/apache/shardingsphere/issues/13929
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] closed issue #13929: Insert sql and use function to cause parameter loss problem
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #13929: Insert sql and use function to cause parameter loss problem
URL: https://github.com/apache/shardingsphere/issues/13929
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #13929: Insert sql and use function to cause parameter loss problem
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #13929:
URL: https://github.com/apache/shardingsphere/issues/13929#issuecomment-1272350629
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org