To any MySQL users, I am using an open-source object relational mapping layer from Java called Hibernate to interface with a MySQL database on my machine. I am running version 4.0.13 of the server, and SQL/J Connector version 3.0.7. The query that is generated from Hibernate looks like this:
select skill0_.SKILL_GUID as x0_0_, skill0_.SKILL_NAME as x1_0_, resource1_.COMPETENCE_LEVEL as x2_0_, resource1_.RESOURCE_SKILL_ID as x3_0_ from SKILL skill0_, RESOURCE_SKILL resource1_ where skill0_.SKILL_GUID=resource1_.SKILL_GUID and ((resource1_.RESOURCE_GUID=? )and(skill0_.ACTIVE=1 )and(resource1_.ACTIVE=1 )) group by skill0_.SKILL_GUID , skill0_.SKILL_NAME , resource1_.COMPETENCE_LEVEL having (resource1_.COMPETENCE_LEVEL>? ) order by resource1_.COMPETENCE_LEVEL desc , skill0_.SKILL_NAME asc This query does not work, giving me the following error: Unknown column 'resource1_.COMPETENCE_LEVEL' in 'having clause' Now, if I remove the having clause, the query runs fine. BUT, if I change the query so that the having clause is the following: having (x2_0_>? ) It runs fine! "x2_0_" is the SQL label given to the field in the select clause. I have asked the Hibernate developers to assist me on this issue, but they replied that this was most certainly a problem with MySQL alone, and that the SQL works on all the other DBs they have tested on. They suggested I contact this mailing list to find a workaround. Why is MySQL tossing an error back at me? The column is used in other places in the same query without a problem. Is a HAVING clause special in some way? Does it require an alias to operate? Why? This same query works on Oracle and on MS SQL, as is. I need the having clause because if I put this particular criteria in the where clause, I'd get erroneous data. There are multiple matching rows for the criteria, and I only want the most recent, which has the max(resourceSkillID). Otherwise, I'd just do away with it. I'd appreciate any advice or insight on this issue. We are running benchmarks between different Java database abstraction layers against multiple databases to see which we should use for upcoming projects. If we select MySQL, we'd be getting a support contract, and would be shipping many servers with MySQL on them, at the appropriate licensing fee. Since this is one of the queries that we need to support in the benchmark, we may need to drop MySQL from the running in order to finish the work. Tom Harris ~--------------------------------------------------------~ ~| Tom Harris - Cisco Systems - VTG - CCBU - CCIS - CEM |~ ~| http://home.nyc.rr.com/tharrisx/ |~ ~--------------------------------------------------------~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
