Hello, Thanks for reporting this. The problem is really that the dateAdd() function changes the DATE type into a TIMESTAMP type. When subtracting a TIMESTAMP from a DATE, PostgreSQL returns an INTERVAL type, rather than when subtracting a DATE from another DATE.
The solution would be to cast the outcome of the dateAdd() function back to DATE to prevent this erroneous type widening. I have registered issue #3824 for this: https://github.com/jOOQ/jOOQ/issues/3824 Cheers, Lukas 2014-11-27 15:20 GMT+01:00 Gabriel Forro <[email protected]>: > Hello, > > the following code snippet does fail against Postgres: > > max(greatest(dateDiff(table.DATE1, dateAdd(table.DATE2, inline(10), > DatePart.DAY)), inline(0))) > > > The generated SQL is the following: > > max(greatest(table.date1 - (table.date2 + (10 || ' day')::interval),0)) > > The reported error is: > ERROR: GREATEST types interval and integer cannot be matched > > The problem is the fact, that Postgres returns Timestamp when an interval > is added to the DATE type, so during the SQL execution in postgres the date > operation returns interval instead of integer (as a result of a DATE - > TIMESTAMP operation). > Unfortunately any attempt to cast the Timestamp to Date is ignored by JOOQ > during SQL generation, as the dateAdd function returned value is > Field<Date> so the cast is removed/ignored when SQL is generated. > > Probably fix: In case of Postgres the generated SQL should add just the > integer to the date (do not use intervals). > > Workaround: > You can use a simple custom field when You do use only Postgres and cross > DB compatibility is not required: > > public class AddDaysToDateCustomField extends CustomField<Date> { > private final Field<Date> field; > private int days; > > public AddDaysToDateCustomField(Field<Date> field, int days) { > super(field.getName(), DATE); > this.field = field; > this.days = days; > } > > @Override > public void accept(Context<?> ctx) { > ctx.visit(delegate(ctx.configuration())); > } > > private QueryPart delegate(Configuration configuration) { > if (days < 0) { > return DSL.field("({0} - {1})", DATE, field, -1 * days); > } else { > return DSL.field("({0} + {1})", DATE, field, days); > } > } > } > > > > > -- > 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.
