Column name not quoted in SELECT for PostgreSQL

[sergey...@themidnightcoders.com]

Hi,

I have a table in PostgreSQL 9.3 named coffee_beverages with one of the columns COFFEE_DESCRIPTION (in uppercase). 

Here is the code similar to what I am using (I simplified it for you to be easy to reproduce):

Connection connection = getConnection();
Field<String> field = DSL.field( "COFFEE_DESCRIPTION", SQLDataType.VARCHAR );
List<SelectField<?>> fields = Collections.<SelectField<?>>singletonList( field );
Table<Record> table = DSL.table( "coffee_beverages" );
DSLContext dslContext = DSL.using( connection, SQLDialect.POSTGRES );
Result<Record> coffee_beverages = dslContext.select( fields ).from( table ).fetch();

The problem is that the last line (.fetch()) produces the following exception:

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [select COFFEE_DESCRIPTION from coffee_beverages]; ERROR: column "coffee_description" does not exist
  Position: 8
at org.jooq.impl.Utils.translate(Utils.java:1645)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:661)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356)
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290)
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2316)
at Main.main(Main.java:28)
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:497)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)
Caused by: org.postgresql.util.PSQLException: ERROR: column "coffee_description" does not exist
  Position: 8
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:645)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:495)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:488)
at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:247)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:342)
... 8 more 

I've done some debugging, searched the Internet and here is what I found:

• PostgreSQL lowercases the column names, if they are not quoted with double-quotes, like "COLUMN_NAME"
• By default, jOOQ should quote each column name, especially for PostgreSQL, but apparently in this case it doesn't
• Note that if I change DSL.field( name, dataType) to DSL.fieldByName( dataType, name ) then it works fine, but I'm not likely to do it since DSL.fieldByName(...) is deprecated.
• Also I provide a screenshot during debug which shows that renderName property is QUOTED - as it should be. Though, no effect.

Hope all this helps you to reproduce and fix the problem. Anyway I'm ready to provide you any further information you would need.

[Lukas Eder]

Hi Sergey,

You've already answered your own question in a way. Yes, fieldByName is deprecated, but the Javadoc also points to the replacement, which is DSL.field(DSL.name()).

I've just recently explained the difference between "plain SQL" (which is what you have been doing) and using "naming" for identifiers (which is what you should be using) in the following Stack Overflow question:

http://stackoverflow.com/a/33672728/521799

Hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sergey...@themidnightcoders.com]

Thanks for the hint!
We use org.jooq:jooq:3.6.2-private from Maven and it seems to have no javadoc attached since my IntelliJ Idea failed to download them. Or maybe I'm missing something.
By the way your javadoc at http://www.jooq.org/javadoc/3.6.2/ seems to point to wrong method, since they even have different return values.

static <T> Field<T>

fieldByName(DataType<T> type, String... qualifiedName) Deprecated.  - [#3843] - 3.6.0 - use sequence(Name, DataType) instead

[Lukas Eder]

Hi Sergey,

Thanks for the update. Some more comments inline:

2015-11-13 13:34 GMT+01:00 <sergey...@themidnightcoders.com>:

Thanks for the hint!
We use org.jooq:jooq:3.6.2-private from Maven and it seems to have no javadoc attached since my IntelliJ Idea failed to download them. Or maybe I'm missing something.

Interesting, it is right there in Maven Central, and I can download it:

http://search.maven.org/#artifactdetails%7Corg.jooq%7Cjooq%7C3.6.2%7Cjar

Perhaps some connectivity issue at your side? Or a configuraiton issue as you've added a "-private" suffix to your version of jOOQ 3.6.2...

 

By the way your javadoc at http://www.jooq.org/javadoc/3.6.2/ seems to point to wrong method, since they even have different return values.

static <T> Field<T>

fieldByName(DataType<T> type, String... qualifiedName) Deprecated.

- [#3843] - 3.6.0 - use sequence(Name, DataType) instead$

Indeed, that's a documentation bug. Thanks for letting us know. We'll fix that immediately. I've registered an issue to track this:

https://github.com/jOOQ/jOOQ/issues/4737

Cheers,

Lukas

[sergey...@themidnightcoders.com]

Yes, I also think that's because of -private modifier. I will download it and link manually.

Thank you for all the answers, Lukas! 

Wish you every success!

[Lukas Eder]

Wish you every success!

Thanks! :) We cannot complain