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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
