-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 
  Previous topic | Next topic 
Author Message
giorgos
 Post subject: BUG when using formula (calculated property) and subselects
PostPosted: Wed Feb 09, 2005 6:54 am 
Newbie

Joined: Thu Mar 04, 2004 5:03 am
Posts: 18
Read the rules before posting!
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.1.8

Name and version of the database you are using: MySQL 4.1.9

When using a formula of the following variety:
Code:
formula="( SELECT t.attr1 FROM Table t ORDER BY t.attr2 DESC LIMIT 1 )"


Hibernate prefixes the tokens DESC and LIMIT with a table alias in the generated SQL statement. It does recognize correctly though that ORDER and BY (and SELECT, etc) are SQL syntax tokens.

The generated SQL looks like:
Code:
SELECT t1_.id, t1_.a1, t1_.a2, ..., (SELECT t2_.attr1 FROM Table t2_ ORDER BY t.attr2 t2_.DESC t2_.LIMIT 1), t1_.aN FROM Table t1_;


I am not sure where exaclty the bug lies but I though I'd point it out so that someone who knows better can fix it.

Regards,
Giorgos
Top
 Profile  
 
giorgos
 Post subject: possible resolution
PostPosted: Wed Feb 09, 2005 7:03 am 
Newbie

Joined: Thu Mar 04, 2004 5:03 am
Posts: 18
In class Template

add

Code:
KEYWORDS.add("desc");
KEYWORDS.add("limit");


(and probably others such as "asc")

inside the static initializer

Giorgos
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Wed Feb 09, 2005 7:10 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Fixed in HB3 CVS
Top
 Profile  
 
giorgos
 Post subject:
PostPosted: Wed Feb 09, 2005 7:55 am 
Newbie

Joined: Thu Mar 04, 2004 5:03 am
Posts: 18
what about 2.1.x? will there be a fix?
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Wed Feb 09, 2005 8:58 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
oh, alright, just for you...
Top
 Profile  
 
giorgos
 Post subject:
PostPosted: Wed Feb 09, 2005 9:19 am 
Newbie

Joined: Thu Mar 04, 2004 5:03 am
Posts: 18
thanks mate
Top
 Profile  
 
giorgos
 Post subject:
PostPosted: Wed Feb 09, 2005 9:21 am 
Newbie

Joined: Thu Mar 04, 2004 5:03 am
Posts: 18
btw, while you're at it could you have a look at this previos post that i made:
http://forum.hibernate.org/viewtopic.php?t=938443

i suspect there lies another bug.
Top
 Profile  
 
giorgos
 Post subject: And yet another bug for you
PostPosted: Wed Feb 09, 2005 9:28 am 
Newbie

Joined: Thu Mar 04, 2004 5:03 am
Posts: 18
When you order by a calculated column (ie, formula) the column is recalculated in the order by clause instead of using it alias. So you get:
Code:
select (subselect here) as p1 from Table t order by (subselect here)


instead of:
Code:
select (subselect here) as p1 from Table t order by p1


MySQL at least is not clever enough (as of version 4.1.9) to optimize this away.

Thanks for your help,
Giorgos
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Wed Feb 09, 2005 9:47 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Neither of these is a bug.

See my comment in the other thread.

As for this one. MySQL is *definitely* smart enough to optimize it. After calculating the result of the subselect once, all the data will be cached in memory, and a second calculation will be incredibly efficient.

With the new query parser, we *might* look into re-using aliases defined in the select clause in other clauses, but support for this is incredibly patchy in different databases (surprisingly).
Top
 Profile  
 
giorgos
 Post subject:
PostPosted: Wed Feb 09, 2005 11:16 am 
Newbie

Joined: Thu Mar 04, 2004 5:03 am
Posts: 18
Quote:
As for this one. MySQL is *definitely* smart enough to optimize it. After calculating the result of the subselect once, all the data will be cached in memory, and a second calculation will be incredibly efficient.


i was misguided by using EXPLAIN which indicated that the subquery was executed an extra time when using it in the order by clause (as opposed to replacing it with the alias)

thanks for yet another educated response,
giorgos[/quote]
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Wed Feb 09, 2005 11:22 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Well, from the p.o.v of the query optimizer, it is evaluating the subselect a second time. Its just going to be really fast the second time round.


Well, try out both queries on your database. See what the difference is.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.