• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate-MySQL bug

 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am posting this here because I think this is more related to Hibernate than MySQL.
Yesterday we faced an issue in production and found this bug. Thought it is worth sharing.
We are using Hibernate 4 and MySQL 5.6

So the select query was something like this. Simple query, nothing special.
Query returns 1 row when run from MySQL prompt and result is
5,100,8,4000
But when executed the query using Hibernate SQLQuery.list(), result was
5,100,5,4000

It is because my both tables had a column with same name and both columns are used in select clause. Alias is ignored, so value is overwritten.
I saw the bug reported in MySQL site, but not getting now. I will post the link once I find it again.

Then with little modification in query, I got another bug. I added an alias for the 1st column.
This time Hibernate just refused to execute this query and threw exception

Interesting thing here is query was run successfully in MySQL prompt. Then I found this link.
https://bugs.mysql.com/bug.php?id=33966

So there are few solutions to this.
1.This I found the simplest one. Add this to the connection string.
Previously this was true, but since MySQL 5.0.5 this became false. So we have to manually set this to true.
2.Dirty solution: Modify query. Add an alias to the whole query and give column names in outer select query, e.g.,

3.Precaution while designing database: Never have the same column names for 2 potential join candidates.
 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Isn't that a cross-product join?

You didn't mention, incidentally, whether you're doing old-style Hibernate or Hibernate JPA. But your queries don't look JPA-like.


Precaution while designing database: Never have the same column names for 2 potential join candidates


Actually, for the last 5 years or so I've supported a system that not only used the same column names, it used compound keys!

What did me in wasn't issues with column names, it was that the database had originally been designed for a mainframe, where fixed-length strings are more efficient - so key fields were space-padded - except when they got trimmed by Hibernate, JPA, or the JDBC driver. Adding insult to injury, the DBMS itself tended to be agnostic about padding out values. So things routinely didn't match up.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry for delayed response.

Tim Holloway wrote:Isn't that a cross-product join?

Do you mean cartesian join? No this is not cartesian join.
This join condition does not always return true. It returns true only when value of SID column of MIXPRODUCT table is equal to value of PID column of PRODUCT table.


Tim Holloway wrote:You didn't mention, incidentally, whether you're doing old-style Hibernate or Hibernate JPA. But your queries don't look JPA-like.

I am not that much into Hibernate, but by this question of yours I guess you are asking whether we are using Object relational mapping for this query.
Yes you are right, in this particular case SQLQuery.list() returns a List<Obejct[]>.
We are iterating over the List<Obejct[]> and populating a List<POJO>
 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, I'm pretty sure that that IS a Cartesian join. You aren't seeing it because your "id" fields are presumably unique, but rename them to "city" and consider what would come back (assuming multiple entries per city on both tables).

What you intended I think was an inclusive-only (inner) join, which is coded thus:


I took it that you were using an ORM as a given, since you explictly said "Hibernate". But what I was asking was which Hibernate. Originally Hibernate used something very like straight SQL as its query language. These days, Hibernate implements the EJB3 Java Persistence Architecture (JPA), which has a more abstract query language (JPQL).

I never worked that much with old Hibernate, but in JPA, one wouldn't normally code a join like that. Instead one would define 2 Entity objects, one for table a and one for table b and link them. These days that can be done using the JPA @OneToOne annotation on the linkages. Thus the actual query logic would look something like this:
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First of all thank you so much for giving your inputs.
Tim Holloway wrote:No, I'm pretty sure that that IS a Cartesian join. You aren't seeing it because your "id" fields are presumably unique, but rename them to "city" and consider what would come back (assuming multiple entries per city on both tables).

This is the relevant part of the table. I really did not know that this is also called cartesian join. I thought cartesian join returns (table1_row_count*table2_row_count)


Now this query returns 5, 100, 8, 4000 when run from MySQL prompt and JDBC and returns 5, 100, 5, 4000 when run from Hibernate as I mentioned in my previous post.

And this query returns the same result in MySQL promt and JDBC and throws exception in Hibernate


Your advice of using JPQL is a good one. It really solves the problem.
Anyway I cannot change these in the current application at least.
So I went for the "useOldAliasMetadataBehavior=true" solution.
In future I will take care and follow the right path.

My only concern is I expected same result when executing above query from 1.database, 2.JDBC and 3.Hibernate, which I am not getting right now.

However this is very well explained in these 2 links.
https://forum.hibernate.org/viewtopic.php?f=1&t=1006399
http://bugs.mysql.com/bug.php?id=35150

So I guess it is always better to use the latest technologies as advised.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic