upgrade probs to 5.0.18 - queries fail

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi all,

I'm hoping someone on this forum can help me. I use hibernate with java
to access mysql. We are trying to upgrade from 4.1.15 non-inndb, to
5.0.18 with innodb. Hibernate makes a 'create tables' script. The
script installs, but the queries that hibernate creates now fails.
These same queries worked fine with 4.1 .

Since this is a hard problem to solve on a forum, if any kind fellow
would like to, you could install the db from this zip:


The query failing is this:

select count(activity0_.activity_id) as col_0_0_ from activity
activity0_, appointment_scheduler appointmen1_ inner join
activity_velox_info activityve2_ on
activity0_.act_velox_info_id=activityve2_.act_velox_info_id inner join
activity_schedule activitysc3_ on
activity0_.act_sch_id=activitysc3_.act_sch_id inner join micro_area
microarea4_ on activityve2_.micro_area_id=microarea4_.micro_area_id
inner join micro_area_scheduler microareas5_ on
appointmen1_.appsch_id=microareas5_.appsch_id inner join micro_area
microarea6_ on microareas5_.micro_area_id=microarea6_.micro_area_id,
periods_of_day periodsofd7_ where (appointmen1_.appsch_id=1
)AND(activity0_.state<>'TRIAGE' )AND((activity0_.type='INSTALL'
)AND(periodsofd7_.code='FIRST_HOUR'  and

 Unknown column 'activity0_.ACT_VELOX_INFO_ID' in 'on clause'

So its complaining on ACT_VELOX_INFO_ID, yet I can do a simple query:

 select act_velox_info_id from activity;
| act_velox_info_id |
|                 1 |
|                 2 |

I'm very stuck. Any help highly appreciated.


Re: upgrade probs to 5.0.18 - queries fail

Quoted text here. Click to load it
. . .

I ran into this recently.  It took me a while to figure it out.  It is
explained in the docs, though.

MySQL 5.0.12 and later enforces an evaluation order in JOINs that is more
compliant with SQL:2003.  So your alias "activity0_" hasn't been defined
yet, at the point when it's evaluating the join condition.

For example:

  FROM tableA AS A, tableB AS B INNER JOIN tableC AS C ON A.field1 =

The above fails, because the B JOIN C is evaluated before the A alias has
been created.

The solution is to use parentheses to force an order of evaluation:

  FROM (tableA AS A, tableB AS B) INNER JOIN tableC AS C ON A.field1 =

See the bulleted paragraph in
http://dev.mysql.com/doc/refman/5.0/en/join.html beginning:

"Previously, the comma operator (,) and JOIN both had the same precedence,
so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3).
Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2
JOIN t3)). "

Also see the bug http://bugs.mysql.com/bug.php?id=15779, in which the report
of joins failing in MySQL 5.0 was closed as "not a bug".

Bill K.

Re: upgrade probs to 5.0.18 - queries fail

Bill Karwin escreveu:

Quoted text here. Click to load it

Thanks Bill, that's the problem. I'm scambling for a solution since
hibernate is generating my queries, and there seems to be no way to
turn off this new feature, short of using pre 5.0.12. I'm hoping to get
a fix from hibernate.


Re: upgrade probs to 5.0.18 - queries fail


I'm having this exact same problem in my application.  Was there any
resolution from Hibernate?


Site Timeline