mysql dialect in hsqldb

0
votes

I'm using hibernate to manage DB operations and MySQL in my application - use org.hibernate.dialect.MySQLInnoDBDialect as the hibernate dialect. But for testing purposes I'm using HSQLDB 1.8.0.10.

I have problem with query like this (working good on mysql not on hsql):

SELECT DISTINCT(id) FROM table ORDER BY name;

I know that the problem is with distinct and order by (http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx ) and solution of this could be for example:

SELECT DISTINCT(id) FROM table GROUP BY id ORDER BY MAX(name);

But my question is, if there is any possibility to using MySQL dialect in HSQLDB and not have to using this solution?

1
javamysqlhibernatehsqldb

1 Answers

2
votes

HSQLDB implements the SQL Standard correctly and does not allow the ambiguous query. It is not possible to change its behaviour.

It is better to modify your MySQL queries to be standard compliant. This allows you to port your application to another database more easily.