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
On Fri, Sep 7, 2018 at 8:38 PM Samir Faci <[email protected]> wrote:
> I thought I'd poke this again to see if there's any thoughts or anything
> else I can provide.
>
>
>
> On Mon, Sep 3, 2018 at 4:52 PM Samir Faci <[email protected]> wrote:
>
>> 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_idfrom
>> ui.advertiser_default_settingwhere (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
>>
>
>
> --
> Thank you
> Samir Faci
> https://keybase.io/csgeek
>
> --
> 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 [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
