search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.8k stars 6.7k forks source link

Actual SQL Missing parameters #4051

Closed geyi closed 4 years ago

geyi commented 4 years ago

Bug Report

I found a similar problem #3136 but this sql still not working: insert into t_quiz_order_ref (ORDER_ID, QUESTION_ID, OP_ORDER_ID, START_TIME, EXPIRE_TIME) values (?, ?, ? ,date_add(now(), interval ? second) ,date_add(now(), interval ? second))

ShardingSphere version: 4.0.0 my project use: sharding-jdbc-spring-boot-starter

stack trace log: 2020-01-20 17:12:29.990[]|GEYI.local|debug|http-nio-8888-exec-1|com.airing.dubbo.mapper.QuizMapper.insertOrderRef|==> Preparing: INSERT INTO t_quiz_order_ref (ORDER_ID, QUESTION_ID, OP_ORDER_ID, START_TIME, EXPIRE_TIME) VALUES (?, ?, ? ,date_add(now(), interval ? second) ,date_add(now(), interval ? second)) 2020-01-20 17:12:30.016[]|GEYI.local|debug|http-nio-8888-exec-1|com.airing.dubbo.mapper.QuizMapper.insertOrderRef|==> Parameters: 61d25387afc34c74a3e4859f4a5cbf19(String), 1(Integer), fc8e54473c4c407fa5e52dec7802ec57(String), 3(Integer), 15(Integer) 2020-01-20 17:12:30.757[]|GEYI.local|info |http-nio-8888-exec-1|ShardingSphere-SQL|Rule Type: sharding 2020-01-20 17:12:30.758[]|GEYI.local|info |http-nio-8888-exec-1|ShardingSphere-SQL|Logic SQL: 

INSERT INTO t_quiz_order_ref
        (ORDER_ID, QUESTION_ID, OP_ORDER_ID, START_TIME, EXPIRE_TIME)
        VALUES

            (?, ?, ?
            ,date_add(now(), interval ? second)
            ,date_add(now(), interval ? second))

2020-01-20 17:12:30.758[]|GEYI.local|info |http-nio-8888-exec-1|ShardingSphere-SQL|SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.core.parse.sql.statement.dml.InsertStatement@6f7ba39b, tablesContext=TablesContext(tables=[Table(name=t_quiz_order_ref, alias=Optional.absent())], schema=Optional.absent())), columnNames=[order_id, question_id, op_order_id, start_time, expire_time], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=138, stopIndex=138, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=141, stopIndex=141, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=144, stopIndex=144, parameterMarkerIndex=2), CommonExpressionSegment(startIndex=159, stopIndex=192, text=date_add(now(),interval?second)), CommonExpressionSegment(startIndex=207, stopIndex=240, text=date_add(now(),interval?second))], parameters=[61d25387afc34c74a3e4859f4a5cbf19, 1, fc8e54473c4c407fa5e52dec7802ec57])]) 2020-01-20 17:12:30.759[]|GEYI.local|info |http-nio-8888-exec-1|ShardingSphere-SQL|Actual SQL: ds0 :::

INSERT INTO t_quiz_order_ref1
        (ORDER_ID, QUESTION_ID, OP_ORDER_ID, START_TIME, EXPIRE_TIME)
        VALUES

            (?, ?, ?, date_add(now(),interval?second), date_add(now(),interval?second))

::: [61d25387afc34c74a3e4859f4a5cbf19, 1, fc8e54473c4c407fa5e52dec7802ec57]

SteNicholas commented 4 years ago

@geyi Because parser doesn't support DATE_ADD function. I would like to add the function.

geyi commented 4 years ago

@geyi Because parser doesn't support DATE_ADD function. I would like to add the function. yes, I didn't see date_add function in the commit c351912092e40f3a41c79acde6b26e03d2316e3e

SteNicholas commented 4 years ago

@geyi The question has already fixed. You could try the dev branch.

geyi commented 4 years ago

@geyi The question has already fixed. You could try the dev branch.

thanks

geyi commented 4 years ago

@geyi The question has already fixed. You could try the dev branch.

i used dev branch, but the same bug happened......

SteNicholas commented 4 years ago

@geyi Does you mean that DATE_ADD doesn't support on dev branch? But I have already add this function unittest and the test worked well.

geyi commented 4 years ago

@geyi Does you mean that DATE_ADD doesn't support on dev branch? But I have already add this function unittest and the test worked well.

yes, i clone dev branch, then execute mvn clean install -Prelease import sharding-distribution/sharding-jdbc-distribution/target/apache-shardingsphere-incubating-5.0.0-RC1-SNAPSHOT-sharding-jdbc-bin/lib exception log: 2020-01-23 16:31:01.406[]|10.0.0.5|debug|http-nio-8888-exec-1|com.airing.dubbo.mapper.QuizMapper.insertOrderRef|==> Preparing: INSERT INTO t_quiz_order_ref (ID, ORDER_ID, QUESTION_ID, OP_ORDER_ID, START_TIME, EXPIRE_TIME) VALUES (?, ?, ?, ? ,date_add(now(), interval ? second) ,date_add(now(), interval ? second)) 2020-01-23 16:31:01.407[]|10.0.0.5|debug|http-nio-8888-exec-1|com.airing.dubbo.mapper.QuizMapper.insertOrderRef|==> Parameters: 10001001(Long), 616eff9eadc84b72a7be0b59855a7c8a(String), 1(Integer), 8abe71763a6b4fc7b28bd7970751b14c(String), 3(Integer), 15(Integer) 2020-01-23 16:31:01.515[]|10.0.0.5|info |http-nio-8888-exec-1|ShardingSphere-SQL|Rule Type: sharding 2020-01-23 16:31:01.515[]|10.0.0.5|info |http-nio-8888-exec-1|ShardingSphere-SQL|Logic SQL: INSERT INTO t_quiz_order_ref (ID, ORDER_ID, QUESTION_ID, OP_ORDER_ID, START_TIME, EXPIRE_TIME) VALUES

        (?, ?, ?, ?
        ,date_add(now(), interval ? second)
        ,date_add(now(), interval ? second))

2020-01-23 16:31:01.515[]|10.0.0.5|info |http-nio-8888-exec-1|ShardingSphere-SQL|SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@667b90ee, tablesContext=TablesContext(tables=[Table(name=t_quiz_order_ref, alias=Optional.absent())], schema=Optional.absent())), columnNames=[id, order_id, question_id, op_order_id, start_time, expire_time], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=142, stopIndex=142, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=145, stopIndex=145, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=148, stopIndex=148, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=151, stopIndex=151, parameterMarkerIndex=3), org.apache.shardingsphere.sql.parser.sql.segment.dml.item.ExpressionProjectionSegment@384b21df, org.apache.shardingsphere.sql.parser.sql.segment.dml.item.ExpressionProjectionSegment@4cdd92e9], parameters=[10001001, 616eff9eadc84b72a7be0b59855a7c8a, 1, 8abe71763a6b4fc7b28bd7970751b14c])]) 2020-01-23 16:31:01.515[]|10.0.0.5|info |http-nio-8888-exec-1|ShardingSphere-SQL|Actual SQL: ds0 ::: INSERT INTO t_quiz_order_ref0 (ID, ORDER_ID, QUESTION_ID, OP_ORDER_ID, START_TIME, EXPIRE_TIME) VALUES

        (?, ?, ?, ?, date_add(now(),interval?second), date_add(now(),interval?second)) ::: [10001001, 616eff9eadc84b72a7be0b59855a7c8a, 1, 8abe71763a6b4fc7b28bd7970751b14c]
geyi commented 4 years ago

@SteNicholas eg1: Actual SQL: ds0 ::: INSERT INTO t_quiz_order_ref0 (START_TIME, ID, ORDER_ID, QUESTION_ID) VALUES (date_add(now(),interval?second), ?, ?, ?) ::: [3, 10000008, 616eff9eadc84b72a7be0b59855a7c8a] exception: No value specified for parameter 4

eg2: Actual SQL: ds0 ::: INSERT INTO t_quiz_order_ref0 (ID, ORDER_ID, QUESTION_ID, START_TIME) VALUES (?, ?, ?, date_add(now(),interval?second)) ::: [10000008, 616eff9eadc84b72a7be0b59855a7c8a, 1] exception: No value specified for parameter 4