Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

FOR JSON emulation must use RETURNING CLOB in Oracle #12399

Closed
lukaseder opened this issue Sep 6, 2021 · 2 comments
Closed

FOR JSON emulation must use RETURNING CLOB in Oracle #12399

lukaseder opened this issue Sep 6, 2021 · 2 comments
Labels
C: DB: Oracle C: Functionality E: Enterprise Edition E: Professional Edition P: Medium R: Fixed T: Defect
Projects
3.14 XML and JSON
Milestone
Version 3.16.0

Comments

@lukaseder
Copy link
Member

Just like the JSON based MULTISET emulation, the FOR JSON emulation must use RETURNING CLOB when emulating the SQL Server syntax in Oracle. This test currently fails:

public void testForJSONPathLarge() throws Exception {
    Table<B> ta = TBook().as("a");
    Table<B> tb = TBook().as("b");
    Table<B> tc = TBook().as("c");
    Table<B> td = TBook().as("d");

    assertJSONEquals(json(
        Seq.crossJoin(
                Seq.rangeClosed(1, 4),
                Seq.rangeClosed(1, 4),
                Seq.rangeClosed(1, 4),
                Seq.rangeClosed(1, 4))
            .map(t -> "{'a_id':" + t.v1 + ",'b_id':" + t.v2 + ",'c_id':" + t.v3 + ",'d_id':" + t.v4 + "}")
            .collect(joining(",", "[", "]"))
        ),
        create().select(
                    ta.field(TBook_ID()).as("a_id"),
                    tb.field(TBook_ID()).as("b_id"),
                    tc.field(TBook_ID()).as("c_id"),
                    td.field(TBook_ID()).as("d_id"))
                .from(ta, tb, tc, td)
                .orderBy(1, 2, 3, 4)
                .forJSON().path()
                .fetchSingle()
                .value1());
}

The resulting error is:

org.jooq.exception.DataAccessException: SQL [select json_arrayagg(json_object(key 'a_id' value "a_id", key 'b_id' value "b_id", key 'c_id' value "c_id", key 'd_id' value "d_id" absent on null) format json) from (select "a"."ID" "a_id", "b"."ID" "b_id", "c"."ID" "c_id", "d"."ID" "d_id" from "TEST"."T_BOOK" "a", "TEST"."T_BOOK" "b", "TEST"."T_BOOK" "c", "TEST"."T_BOOK" "d" order by 1, 2, 3, 4) "t" having count(*) = count(*) -- SQL rendered with a free trial version of jOOQ 3.16.0-SNAPSHOT]; ORA-40478: Ausgabewert zu groß (Höchstwert: 4000)

	at org.jooq_3.16.0-SNAPSHOT.ORACLE18C.debug(Unknown Source)
	at org.jooq.impl.Tools.translate(Tools.java:3057)
	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:639)
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)
	at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:295)
	at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:316)
	at org.jooq.impl.SelectImpl.fetchLazyNonAutoClosing(SelectImpl.java:2849)
	at org.jooq.impl.ResultQueryTrait.fetchSingle(ResultQueryTrait.java:601)
	at org.jooq.test.all.testcases.ForJSONTests.testForJSONPathLarge(ForJSONTests.java:282)
	at org.jooq.test.jOOQAbstractTest.testForJSONPathLarge(jOOQAbstractTest.java:7828)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:93)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
Caused by: java.sql.SQLException: ORA-40478: Ausgabewert zu groß (Höchstwert: 4000)

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1231)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:772)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:512)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:163)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1010)
	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1271)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1149)
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1661)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1470)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3761)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4136)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1014)
	at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219)
	at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219)
	at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4198)
	at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:230)
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:335)
	... 37 more
Caused by: Error : 40478, Position : 253, Sql = select json_arrayagg(json_object(key 'a_id' value "a_id", key 'b_id' value "b_id", key 'c_id' value "c_id", key 'd_id' value "d_id" absent on null) format json) from (select "a"."ID" "a_id", "b"."ID" "b_id", "c"."ID" "c_id", "d"."ID" "d_id" from "TEST"."T_BOOK" "a", "TEST"."T_BOOK" "b", "TEST"."T_BOOK" "c", "TEST"."T_BOOK" "d" order by 1, 2, 3, 4) "t" having count(*) = count(*) -- SQL rendered with a free trial version of jOOQ 3.16.0-SNAPSHOT, OriginalSql = select json_arrayagg(json_object(key 'a_id' value "a_id", key 'b_id' value "b_id", key 'c_id' value "c_id", key 'd_id' value "d_id" absent on null) format json) from (select "a"."ID" "a_id", "b"."ID" "b_id", "c"."ID" "c_id", "d"."ID" "d_id" from "TEST"."T_BOOK" "a", "TEST"."T_BOOK" "b", "TEST"."T_BOOK" "c", "TEST"."T_BOOK" "d" order by 1, 2, 3, 4) "t" having count(*) = count(*) -- SQL rendered with a free trial version of jOOQ 3.16.0-SNAPSHOT, Error Msg = ORA-40478: Ausgabewert zu groß (Höchstwert: 4000)

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)
	... 56 more
@lukaseder lukaseder added this to the Version 3.16.0 milestone Sep 6, 2021
@lukaseder lukaseder added this to To do in 3.14 XML and JSON via automation Sep 6, 2021
@lukaseder
Copy link
Member Author

While I'm at it, the FOR JSONB emulation should produce RETURNING BLOB

lukaseder added a commit that referenced this issue Sep 6, 2021
@lukaseder
[#12399] FOR JSON emulation must use RETURNING CLOB in Oracle
083fd06
lukaseder added a commit that referenced this issue Sep 6, 2021
@lukaseder
[#12399] FOR JSON emulation must use RETURNING CLOB in Oracle
0ac14cd
@lukaseder
Copy link
Member Author

Fixed in jOOQ 3.16.0 and 3.15.3 (#12400)

3.14 XML and JSON automation moved this from To do to Done Sep 6, 2021
lukaseder added a commit that referenced this issue Sep 7, 2021
@lukaseder
[#12399] RETURNING BLOB is also required in Db2
f964025
lukaseder added a commit that referenced this issue Sep 7, 2021
@lukaseder
[#12399] RETURNING BLOB is also required in Db2
8fee8eb
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
C: DB: Oracle C: Functionality E: Enterprise Edition E: Professional Edition P: Medium R: Fixed T: Defect
Projects
3.14 XML and JSON
  
Done
Milestone
Version 3.16.0
Development

No branches or pull requests
1 participant
@lukaseder