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.
