Value select cast() or extract() + JPQL macro does not work

Not sure if it is normal, documentation seems not preventing this case.

I’m using ValueLoadContext with different queries, and combining JPQL macro @between and cast() or extract() yields an error

Errors found for input jpql:[select extract(monthfrome.date), e.customer, SUM(e.amount) from testcuba4$Order e where (e.date >= :e_date_1_1 and e.date < :e_date_1_2) group by e.date, e.customer order by 1, 2]

This does not work:

select extract(month from e.date), e.customer, SUM(e.amount) from testcuba4$Order e where @between(e.date, now, now+1, year) group by extract(month from e.date), e.customer order by extract(month from e.date), e.customer

But this works:

select e.date, e.customer, SUM(e.amount) from testcuba4$Order e where @between(e.date, now, now+1, year) group by e.date, e.customer order by e.date, e.customer

And also this:

select cast(extract(month from e.date) integer), e.customer, SUM(e.amount) from testcuba4$Order e group by extract(month from e.date), e.customer order by extract(month from e.date), e.customer

Sample project attached, lookt at integration tests.testcuba4.zip (87.2 KB)

Regards,
Michael

Hi Michael,

Thank you for reporting the problem. We have an issue: Query with Extract is transformed incorrectly when security constraint is applied or query contains macro · Issue #767 · cuba-platform/cuba · GitHub

We’ll fix the problem during this month.

Thank you,
Andrey

Great, thanks Andrey. For the record there is a workaround: expand macros in a service before they reach data manager.

Hello!
extract(month from e.date) works fine, but
cast(e.date as text) or
cast(e.date as date)

still doesnt’ work(

Try to use cast without as keyword, e.g: CAST(e.salary integer)