My bad, DISTINCT... ORDER BY UPPER works indeed as one can test using
set mode regular;
with a as (select 'a', 'b')
select distinct 1 from a order by upper(2)
As to the mode, I understand it's confusing. I am at the mercy of that
legacy app which sends non-standard SQL except if it the driver
So you use either Oracle or H2 in MySQL compatibility mode? It's a pretty
strange choice, you're just looking for trouble.
If your query looks like
SELECT DISTINCT A FROM someTable ORDER BY someFunction(A)
you don't need a compatibility mode, such query is accepted by H2 since
1.4.198. (But
I am using MySQL mode indeed because the app sends statements like in
https://github.com/h2database/h2database/pull/1287, which was only
fixed/allowed in MySQL mode.
Granted the latter is not supported in Oracle but "the app" looks up the
DatabaseMetadata to use the right syntax.
The errors on
Yes in memory only for now.
I can move it to embedded files and tcp as well.
What I saw is a sudden drop of performance between 196 and 197. That drop was
halved and partially recovered in 198 and 199.
But it's still, for that particular use case (CTE), too slow compared to older
versions.
