Postgres String / Array function bug

[Samir Faci]

So I wanted to relay this on behalf of a coworker... figured i'd at least push it up to upstream and see if someone else has a fix or ran into this use case already.  Failing that opening up a bug to track this is an issue.

I'm trying to execute the following SQL in Jooq.  It works fine in postgres but fails using Jooq.

Environment:

• Jooq: 3.10.6
• Postgres: 10.3

ui.advertiser_default_setting.value is a text field using one of the worst anti-pattern you can use in DB design which is a list of comma delimited fields. ie 123,456,789,111 etc.

Sample Data:

select * from ui.advertiser_default_setting where name = 'taxonomyIds' limit 5;
   id   | provider_id |   type   |    name     |                                                                                                                                                                                                                             v
alue                                                                                                                                                                                                                             | is_deleted | is_new | is_updated | active |
 created_by_id |       creation_date        | modified_by_id |     modification_date
--------+-------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------+------------+--------+
---------------+----------------------------+----------------+----------------------------
 105799 |       13971 | CAMPAIGN | taxonomyIds |
                                                                                                                                                                                                                                 | t          | f      | f          | f      |          9864 | 2018-08-10 12:15:35.177    |           9864 | 2018-08-10 12:15:35.177
   3606 |        1321 | CAMPAIGN | taxonomyIds | 36,44,45,46,52,127,1766,1768,7326,7577,9539,12160,12894,14007,14008,16627,16628,19631,20423,20424,20425,20427,20428,22430,22454,22690,22969,22976,23138,23224,23225,23386,23387,23388,23440,23535,23552,23579,23615,23632,236
33,23634,23762,23809,23817,23818,23858,23861,23985,24065,24066,24110,24191,24263,24666,24667,25700,25752,25809,25853,25873,25875,25881,25904,25905,26045,26087,26122,26174,26303,26423,26442,26581,26790,27481,27721,27727,27728 | f          | t      | t          | t      |               |                            |           8914 | 2018-08-10 13:54:39.241007
 102956 |        1374 | CAMPAIGN | taxonomyIds | 36,44,45,46,52,127,1766,1768,7326,7577,9539,12160,12894,14007,14008,16627,16628,19631,20423,20424,20425,20427,20428,22430,22454,22690,22969,22976,23138,23224,23225,23386,23387,23388,23440,23535,23552,23579,23615,23632,236
33,23634,23762,23809,23817,23818,23858,23861,23985,24065,24066,24110,24191,24263,24666,24667,25700,25752,25809,25853,25873,25875,25881,25904,25905,26045,26087,26122,26174,26303,26423,26442,26581,26790,27481,27721,27727,27728 | f          | t      | f          | t      |
           444 | 2018-08-09 11:56:43.516841 |           8914 | 2018-08-10 13:54:39.241007
  66900 |        1385 | CAMPAIGN | taxonomyIds | 36,44,45,46,52,127,1766,1768,7326,7577,9539,12160,12894,14007,14008,16627,16628,19631,20423,20424,20425,20427,20428,22430,22454,22690,22969,22976,23138,23224,23225,23386,23387,23388,23440,23535,23552,23579,23615,23632,236
33,23634,23762,23809,23817,23818,23858,23861,23985,24065,24066,24110,24191,24263,24666,24667,25700,25752,25809,25853,25873,25875,25881,25904,25905,26045,26087,26122,26174,26303,26423,26442,26581,26790,27481,27721,27727,27728 | f          | t      | t          | t      |          7968 | 2015-06-26 09:30:20.853291 |           8914 | 2018-08-10 13:54:39.241007
   3632 |        1393 | CAMPAIGN | taxonomyIds | 36,44,45,46,52,127,1766,1768,7326,7577,9539,12160,12894,14007,14008,16627,16628,19631,20423,20424,20425,20427,20428,22430,22454,22690,22969,22976,23138,23224,23225,23386,23387,23388,23440,23535,23552,23579,23615,23632,236
33,23634,23762,23809,23817,23818,23858,23861,23985,24065,24066,24110,24191,24263,24666,24667,25700,25752,25809,25853,25873,25875,25881,25904,25905,26045,26087,26122,26174,26303,26423,26442,26581,26790,27481,27721,27727,27728 | f          | t      | t          | t      |
               |                            |           8914 | 2018-08-10 13:54:39.241007
(5 rows)

Query:

select ui.advertiser_default_setting.provider_id
from ui.advertiser_default_setting
where (ui.advertiser_default_setting.name = 'taxonomyIds' and ui.advertiser_default_setting.type = 'CAMPAIGN' and
       ui.advertiser_default_setting.provider_id in (6377, 10720) and ui.advertiser_default_setting.active = true and
       ui.advertiser_default_setting.is_deleted = false and ui.advertiser_default_setting.value is not null and
       array_position(string_to_array(ui.advertiser_default_setting.value, ','), '15261') is null);

If I run this in a db console that works fine, but if I try to run it using Jooq I get the following error.

Java code:

 final List<Long> activeAdvDefaultSettingAdvertisersInsertTaxId =
            dslContext.select(ADVERTISER_DEFAULT_SETTING.PROVIDER_ID)
                .from(ADVERTISER_DEFAULT_SETTING)
                .where(ADVERTISER_DEFAULT_SETTING.NAME.eq("taxonomyIds"))
                .and(ADVERTISER_DEFAULT_SETTING.TYPE.eq("CAMPAIGN"))
                .and(ADVERTISER_DEFAULT_SETTING.PROVIDER_ID.in(advertiserIds))
                .and(JooqHelperUtils.activeRecordCheck(ADVERTISER_DEFAULT_SETTING))
                .and(ADVERTISER_DEFAULT_SETTING.VALUE.isNotNull())
                .and(field("array_position(string_to_array(ui.advertiser_default_setting.value, ','), {0})",
                    SQLDataType.INTEGER,
                    Long.toString(taxonomyId)
                ).isNull())
                .fetchInto(Long.class);

Util Code:

JooqHelperUtils.activeRecordCheck

public static Condition activeEdgeRecordCheck(Table table) {
         Field<String> active = table.field("active", String.class);
         return active.eq("Y");     
}

Console Log:

2018-07-17 15:06:26,489 [https-jsse-nio-9443-exec-4] DEBUG %% org.jooq.tools.LoggerListener - -> with bind values      : select ui.advertiser_default_setting.provider_id from ui.advertiser_default_setting 
where (ui.advertiser_default_setting.name = 'taxonomyIds' and ui.advertiser_default_setting.type = 'CAMPAIGN' and ui.advertiser_default_setting.provider_id in (6377, 10720) and 
ui.advertiser_default_setting.active = true and ui.advertiser_default_setting.is_deleted = false and ui.advertiser_default_setting.value is not null and 
array_position(string_to_array(ui.advertiser_default_setting.value, ','), '15261') is null)
2018-07-17 15:06:26,672 [https-jsse-nio-9443-exec-4] DEBUG %% org.jooq.tools.LoggerListener - Exception
org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar [select ui.advertiser_default_setting.provider_id from ui.advertiser_default_setting where (ui.advertiser_default_setting.name = ? 
and ui.advertiser_default_setting.type = ? and ui.advertiser_default_setting.provider_id in (?, ?) and ui.advertiser_default_setting.active = true and ui.advertiser_default_setting.is_deleted = false 
and ui.advertiser_default_setting.value is not null and array_position(string_to_array(ui.advertiser_default_setting.value, ','), ?) is null)]; nested exception is org.postgresql.util.PSQLException: 
ERROR: function array_position(text[], character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Database table structure:

\d+ ui.advertiser_default_setting
                                                       Table "ui.advertiser_default_setting"
      Column       |            Type             | Collation | Nullable |             Default              | Storage  | Stats target | Description
-------------------+-----------------------------+-----------+----------+----------------------------------+----------+--------------+-------------
 id                | bigint                      |           | not null | generated by default as identity | plain    |              |
 provider_id       | bigint                      |           | not null |                                  | plain    |              |
 type              | character varying           |           | not null | 'CAMPAIGN'::character varying    | extended |              |
 name              | character varying(255)      |           | not null |                                  | extended |              |
 value             | character varying(1024)     |           |          |                                  | extended |              |
 is_deleted        | boolean                     |           |          | false                            | plain    |              |
 is_new            | boolean                     |           |          | true                             | plain    |              |
 is_updated        | boolean                     |           |          | true                             | plain    |              |
 active            | boolean                     |           |          | false                            | plain    |              |
 created_by_id     | bigint                      |           |          |                                  | plain    |              |
 creation_date     | timestamp without time zone |           |          |                                  | plain    |              |
 modified_by_id    | bigint                      |           |          |                                  | plain    |              |
 modification_date | timestamp without time zone |           |          |                                  | plain    |              |
Indexes:
    "advertiser_default_setting_pkey" PRIMARY KEY, btree (id)
    "adv_def_setng_prov_id_type_name_idx" UNIQUE, btree (provider_id, type, name)
    "advertiser_default_setting_provider_id_type_name_value_idx" UNIQUE, btree (provider_id, type, name, value)
    "advertiser_default_setting_unique_index" UNIQUE, btree (provider_id, type, name, value) WHERE is_deleted = false
    "advertiser_default_setting_provider_idx" btree (provider_id)
Check constraints:
    "advertiser_default_setting_type_check" CHECK (type::text = 'CAMPAIGN'::text)
Foreign-key constraints:
    "advertiser_default_setting_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES ui.principal(id) ON DELETE SET NULL
    "advertiser_default_setting_modified_by_id_fkey" FOREIGN KEY (modified_by_id) REFERENCES ui.principal(id) ON DELETE SET NULL
    "advertiser_default_setting_provider_id_fkey" FOREIGN KEY (provider_id) REFERENCES ui.provider(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
Triggers:
    advertiser_default_setting_trg_audit AFTER INSERT OR DELETE OR UPDATE ON ui.advertiser_default_setting FOR EACH ROW EXECUTE PROCEDURE audit.pg_audit_trigger()

--

Thank you
Samir Faci

https://keybase.io/csgeek

[Samir Faci]

I thought I'd poke this again to see if there's any thoughts or anything else I can provide.

[Rob Sargent]

Have you tried 

field(String.format("array_position(string_to_array(ui.advertiser_default_setting.value, ','), %d)”, taxonomyId))

--
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.

[Lukas Eder]

Hi Samir,

The problem is probably here:

field("array_position(string_to_array(ui.advertiser_default_setting.value, ','), {0})",

SQLDataType.INTEGER,

Long.toString(taxonomyId)

)

Your SQL version uses an inline literal for your taxonomyId string, so PostgreSQL is able to derive the type from the literal itself. In your jOOQ version, a bind variable is used, and jOOQ doesn't always cast those in PostgreSQL, only when integration tests have shown that PostgreSQL really cannot handle the variable otherwise. In this case, jOOQ doesn't have any such information and doesn't cast the bind variable. You could fix this e.g. by casting the value yourself:

field("array_position(string_to_array(ui.advertiser_default_setting.value, ','), {0}::varchar(1024))",

SQLDataType.INTEGER,

Long.toString(taxonomyId)

)

Or by passing an inline value to get the same SQL as before:

field("array_position(string_to_array(ui.advertiser_default_setting.value, ','), {0})",

SQLDataType.INTEGER,

inline(Long.toString(taxonomyId))

)

Hope this helps,

Lukas

--

[Lukas Eder]

My bad, the error message is actually quite clear about what was the problem:

ERROR: function array_position(text[], character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

So, the cast would have to be to text, not varchar:

field("array_position(string_to_array(ui.advertiser_default_setting.value, ','), {0}::text)",

SQLDataType.INTEGER,

Long.toString(taxonomyId)

)

[Samir Faci]

Thanks Lukas, I'll give that a go.  

--

Samir Faci

[Samir Faci]

I just wanted to confirm that the fix above worked if anyone else runs into a similar use case.