[Postgres] bad SQL grammar exception when inserting empty arrays

[tkaiser]

Expected behavior and actual behavior:

I expect to be able to use at least empty arrays when inserting into a Postgres table that has columns of eg. TEXT [] NOT NULL type.

(Almost can't believe I'm the first to run into this, but a search for "postgres array" in issues came up with nothing related afaict)

Actual behavior:

org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: cannot determine type of empty array
  Hint: Explicitly cast to the desired type, for example ARRAY[]::integer[].
  Position: 523
	at org.jooq_3.9.6.DEFAULT.debug(Unknown Source)
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.translate(JooqExceptionTranslator.java:92)
	at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.handle(JooqExceptionTranslator.java:81)
	at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.exception(JooqExceptionTranslator.java:54)
	at org.jooq.impl.ExecuteListeners.exception(ExecuteListeners.java:245)
	at org.jooq.impl.BatchMultiple.execute(BatchMultiple.java:126)
	at org.jooq.impl.BatchMultiple.execute(BatchMultiple.java:78)
	at com.dynatrace.devops.dashboards.supportalerts.service.sql.SqlAlertRepositoryImpl.insertImportedAlerts(SqlAlertRepositoryImpl.java:86)
	at com.dynatrace.devops.dashboards.supportalerts.service.sql.SqlAlertRepositoryImplTest.insertImportedAlertWithEmptyArrays(SqlAlertRepositoryImplTest.java:98)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.postgresql.util.PSQLException: ERROR: cannot determine type of empty array
  Hint: Explicitly cast to the desired type, for example ARRAY[]::integer[].
  Position: 523
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
	at com.sun.proxy.$Proxy100.executeBatch(Unknown Source)
	at org.jooq.tools.jdbc.DefaultStatement.executeBatch(DefaultStatement.java:93)
	at org.jooq.tools.jdbc.DefaultStatement.executeBatch(DefaultStatement.java:93)
	at org.jooq.impl.BatchMultiple.execute(BatchMultiple.java:106)
	... 31 common frames omitted

Steps to reproduce the problem:

CREATE TABLE person (
  id   BIGSERIAL PRIMARY KEY,
  names TEXT [] NOT NULL  
);

Then generate code from that, then something like

List<String> emptyNames = new ArrayList<>();
dsl.insertInto(PERSON).columns(PERSON.NAMES).values(
  emptyNames.toArray(new String[emptyNames.size()]).execute()

Versions:

• jOOQ: 3.9.6 (pulled in because of generating code)
• Java: java version "1.8.0_91"
• Database (include vendor): jdbc:postgresql://localhost:32857/test (PostgreSQL 10.1)
• JDBC Driver (include name if inofficial driver): org.postgresql:postgresql:9.4-1200-jdbc41

[lukaseder]

Thanks for your report. This works in our integration test, but I suspect you've found an API usage where this breaks. Your stack trace hints at a batch execution, but your Java code doesn't use that. Also, is PERSON.NAMES a generated column with the correct data type?

[tkaiser]

Sorry for the delay, christmas vacation got in the way ;) anyway, while trying to set up a sandbox project to reproduce this I found that setting spring.jooq.sql-dialect=postgres in application.properties fixes this.

It appears to be the case that when setting no SQL dialect, you get
org.jooq.exception.SQLDialectNotSupportedException: Cannot bind ARRAY types in dialect DEFAULT
when trying to insert an array, as apparently the default dialect does not support arrays at all.

IF batch inserting though, you can get past that and then you get the
org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: cannot determine type of empty array Hint: Explicitly cast to the desired type, for example ARRAY[]::integer[].

When setting the SQL dialect properly, everything works as expected and the following statements are generated:

2018-01-02 10:25:02.785 DEBUG 32209 --- [           main] org.jooq.tools.LoggerListener            : Executing query          : insert into "public"."person" ("names") values (?::varchar[])
2018-01-02 10:25:02.787 DEBUG 32209 --- [           main] org.jooq.tools.LoggerListener            : -> with bind values      : insert into "public"."person" ("names") values (cast('{"foo","bar"}' as varchar[]))

[lukaseder]

OK, cool, thanks for following up. Yes indeed, the errors provided from the JDBC batch API work differently, which is why the SQLDialectNotSupportedException seems to be bypassed.

[lukaseder]

I have created a feature request in Spring Boot directly, to auto-configure that spring.jooq.sql-dialect flag: spring-projects/spring-boot#11464

I've seen this quite a few times now, that users forget this flag, which isn't mandatory, but shouldn't default to SQLDialect.DEFAULT if the actual dialect is known to Spring.