upgrade probs to 5.0.18 - queries fail

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:

http://www.braziloutsource.com/random/hwtf.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'
)OR(activity0_.TYPE='SERVICE'
))AND(activity0_.promise_date>='2006-2-10'
)AND(activity0_.promise_date<'2006-2-11'
)AND(periodsofd7_.code='FIRST_HOUR' and
activityve2_.period_of_day_id=periodsofd7_.period_of_day_id) AND(microarea4_.micro_area_id
IN(microarea6_.micro_area_id))AND(activitysc3_.scheduler_id= appointmen1_.appsch_id
)

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.

iksrazal

Re: upgrade probs to 5.0.18 - queries fail

wrote in message
news:1139656103.653323.263910@g47g2000cwa.googlegroups.com.. .
> The query failing is this:
>
> SELECT . . .
> FROM activity AS activity0_,
> appointment_scheduler AS appointmen1_
> INNER JOIN activity_velox_info AS activityve2_
> ON activity0_.act_velox_info_id = activityve2_.act_velox_info_id
.. . .

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 =
C.field2

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 =
C.field2

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".

Regards,
Bill K.

Re: upgrade probs to 5.0.18 - queries fail

Bill Karwin escreveu:

> wrote in message
> news:1139656103.653323.263910@g47g2000cwa.googlegroups.com.. .
> > The query failing is this:
> >
> > SELECT . . .
> > FROM activity AS activity0_,
> > appointment_scheduler AS appointmen1_
> > INNER JOIN activity_velox_info AS activityve2_
> > ON activity0_.act_velox_info_id = activityve2_.act_velox_info_id
> . . .
>
> 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.

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.

iksrazal

Re: upgrade probs to 5.0.18 - queries fail

Hi,

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

Thanks,
Saran