Splitting statements on semicolon leads to errors #252
Description
Bug Report
Versions
- Driver: io.r2dbc:r2dbc-h2:1.0.0.RELEASE
- Database: H2
- Java: 21
Current Behavior
When executing a SQL statement which contains a semicolon, the statement is split on semicolon. This results in multiple statements which are executed separately. This leads to errors.
io.r2dbc.spi.R2dbcBadGrammarException
io.r2dbc.spi.R2dbcBadGrammarException: [42000] [42000] Syntax error in SQL statement "insert into test (0, [*]'aa"; SQL statement:
insert into test (0, 'aa [42000-232]
at io.r2dbc.h2.H2DatabaseExceptionFactory.convert(H2DatabaseExceptionFactory.java:84)
at io.r2dbc.h2.client.SessionClient.createCommand(SessionClient.java:166)
at io.r2dbc.h2.client.SessionClient.access$000(SessionClient.java:39)
at io.r2dbc.h2.client.SessionClient$1.next(SessionClient.java:120)
at io.r2dbc.h2.client.SessionClient$1.next(SessionClient.java:108)
at java.base/java.util.Spliterators$IteratorSpliterator.tryAdvance(Spliterators.java:1950)
at reactor.core.publisher.FluxIterable$IterableSubscription.hasNext(FluxIterable.java:271)
at reactor.core.publisher.FluxIterable.subscribe(FluxIterable.java:187)
at reactor.core.publisher.FluxIterable.subscribe(FluxIterable.java:83)
at reactor.core.publisher.Flux.subscribe(Flux.java:8891)
at reactor.core.publisher.FluxFlatMap$FlatMapMain.onNext(FluxFlatMap.java:430)
at reactor.core.publisher.FluxArray$ArraySubscription.slowPath(FluxArray.java:126)
at reactor.core.publisher.FluxArray$ArraySubscription.request(FluxArray.java:99)
at reactor.core.publisher.FluxFlatMap$FlatMapMain.onSubscribe(FluxFlatMap.java:373)
at reactor.core.publisher.FluxArray.subscribe(FluxArray.java:53)
at reactor.core.publisher.FluxArray.subscribe(FluxArray.java:59)
at reactor.core.publisher.Flux.subscribe(Flux.java:8891)
at reactor.core.publisher.FluxFlatMap.trySubscribeScalarMap(FluxFlatMap.java:202)
at reactor.core.publisher.MonoFlatMapMany.subscribeOrReturn(MonoFlatMapMany.java:49)
at reactor.core.publisher.Flux.subscribe(Flux.java:8876)
at reactor.core.publisher.Flux.subscribeWith(Flux.java:9012)
at reactor.core.publisher.Flux.subscribe(Flux.java:8856)
at reactor.core.publisher.Flux.subscribe(Flux.java:8780)
at reactor.core.publisher.Flux.subscribe(Flux.java:8750)
at de.saschaufer.apps.rule_engine_kie.service_internal_dmn.Test.test(Test.java:25)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:767)
at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$8(TestMethodTestDescriptor.java:217)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:213)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:138)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:156)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:146)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:144)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:143)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:100)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:160)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:146)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:144)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:143)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:100)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:160)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:146)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:144)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:143)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:100)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:198)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:169)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:93)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:58)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:141)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:57)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:103)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:85)
at org.junit.platform.launcher.core.DelegatingLauncher.execute(DelegatingLauncher.java:47)
at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:63)
at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)
at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "insert into test (0, [*]'aa"; SQL statement:
insert into test (0, 'aa [42000-232]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:514)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.message.DbException.get(DbException.java:199)
at org.h2.message.DbException.getSyntaxError(DbException.java:247)
at org.h2.command.Tokenizer.readCharacterString(Tokenizer.java:1101)
at org.h2.command.Tokenizer.tokenize(Tokenizer.java:224)
at org.h2.command.ParserBase.initialize(ParserBase.java:292)
at org.h2.command.Parser.parse(Parser.java:552)
at org.h2.command.Parser.prepareCommand(Parser.java:484)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:645)
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:561)
at io.r2dbc.h2.client.SessionClient.createCommand(SessionClient.java:157)
... 91 more
Steps to reproduce
I created a minimal reproducible example in a unit test.
Input Code
import io.r2dbc.h2.CloseableConnectionFactory;
import io.r2dbc.h2.H2Connection;
import io.r2dbc.h2.H2ConnectionFactory;
import reactor.core.publisher.Mono;
class Test {
@org.junit.jupiter.api.Test
void test() {
final CloseableConnectionFactory connectionFactory = H2ConnectionFactory.inMemory("testdb");
final Mono<H2Connection> connection = connectionFactory.create();
connection
.flatMapMany(c -> c.createStatement("create table test (id int primary key, name varchar(255))").execute())
.subscribe(
ok -> System.out.println("ok"),
throwable -> throwable.printStackTrace()
);
connection
.flatMapMany(c -> c.createStatement("insert into test (0, 'aa;aa')").execute())
.subscribe(
ok -> System.out.println("ok"),
throwable -> throwable.printStackTrace()
);
}
}Expected behavior/code
I think, the expected behavior should be, that the statement is executed as it is. Furthermore, this leads to errors when the semicolon is not meant as a separator of statements but e.g. part of a value, like in the example.
As it is now, I can't use it, because the values I have, contain semicolons. Therefore, I can't see the split as a feature, which maybe comes in handy if a statement is actually more than one statement.
Possible Solution
One possible solution could be, that the statements are executed as they are, no splitting.
Another solution could be, that semicolons, which are surrounded by single quotes, are ignored. Problem with this one is, that, e.g. stored procedures, can have semicolons. So, I prefer solution one.
Additional context
The split happens here:
H2Statement#execute()
Flux.fromArray(this.sql.split(";"))In this issue the bug has already been mentioned: #169 (comment)
Unfortunately, the issue was closed.