search

mvysny / vok-orm

Mapping rows from a SQL database to POJOs in its simplest form
MIT License
21 stars 4 forks source link

Aliased columns do not work with SQLDataLoader #5

Closed mvysny closed 6 years ago

mvysny commented 6 years ago

MySQL nor PostgreSQL does not support aliases in WHERE clauses: https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error Therefore we can't use the java bean property names when constructing the WHERE clause, but we must use the actual column names.

mvysny commented 6 years ago

The workaround would be to use HAVING instead of WHERE with MySQL, but it's slower and not actually supported by the PostgreSQL (actually it looks like it's supported by newer PostgreSQLs 9.x - evaluate).

mvysny commented 6 years ago

All of the following databases fail to execute this statement:

SELECT count(*) FROM (select p.name as personName from Test p where 1=1 and personName = ? order by 1=1 ) AS Foo

The error message is 'unknown column personName'.

mvysny commented 6 years ago

Ok, it's explicitly mentioned in PostgreSQL SELECT documentation:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

mvysny commented 6 years ago

Fixed in vok-orm 0.9