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