Re: MySQL 4.0 => 5.0 - strange...

[Jay Pipes]

Tim Rozmajzl wrote:
> I have a similar problem (migrating from 4.0 to
> 5.0), but the query looks like it already uses an
> explicit INNER JOIN between ENUMERATEDTYPE and
> TYPE. The error message was:
>
> Unknown column 'this_.TYPE_ID' in 'on clause'
>
>
> Where "this_" aliases ENUMERATEDTYPE. The query
> below was generated using Hibernate 3.0:
>
>
> select this_.TYPE_ID as TYPE1_0_, this_1_.version
> as version5_0_, this_1_.NAME as NAME5_0_,
> this_1_.description as descript4_5_0_, ( select
> count(*) from COMPLEXTYPE ctype where
> ctype.TYPE_ID = this_1_.TYPE_ID ) as formula0_0_,
> ( select count(*) from SIMPLETYPE stype where
> stype.TYPE_ID = this_1_.TYPE_ID ) as formula1_0_,
> ( select count(*) from ENUMERATEDTYPE etype where
> etype.TYPE_ID = this_1_.TYPE_ID ) as formula2_0_,
> ( select min(ENUMERATION.REPRESENTATION) from
> ENUMERATEDTYPE left join ENUMERATION on
> ENUMERATION.CONTAINING_ENUMERATION_ID =
> this_.TYPE_ID ) as formula3_0_ from ENUMERATEDTYPE
> this_ inner join TYPE this_1_ on
> this_.TYPE_ID=this_1_.TYPE_ID
>
>
> Both ENUMERATEDTYPE and TYPE have TYPE_ID
> columns.

Not sure about this query; it definitely never would have worked on a 4.0 server because it uses subqueries (which were introduced in 4.1).

The query itself is not particularly efficient, either, and gives me some concern considering that Hibernate generated it... I'll have to call the Hibernate devs! ;)

There are two major problems with the query:
a) It uses correlated subqueries, which are executed once for each row of the primary resultset, versus a derived table, which is executed only once, and then joined to the primary resultset
b) It needlessly does a correlated subquery from the primary table (ENUMERATEDTYPE) back *to itself* just to get a count of the records in the table. There's no need; simply do a GROUP BY on the primary table.

A more efficient version, using derived tables instead of correlated subqueries, would be:

SELECT
this_.TYPE_ID as TYPE1_0_
, this_1_.version as version5_0_
, this_1_.NAME as NAME5_0_
, this_1_.description as descript4_5_0_
, IFNULL(ctype.counter, 0) as formula0_0_
, IFNULL(stype.counter, 0) as formula1_0_
, COUNT(*) as formula2_0_
, IFNULL(enu.minner, 0) as formula3_0_
FROM ENUMERATEDTYPE this_
INNER JOIN TYPE this_1_
ON this_.TYPE_ID=this_1_.TYPE_ID
LEFT JOIN (
SELECT TYPE_ID, COUNT(*) as counter
FROM COMPLEXTYPE
GROUP BY TYPE_ID
) as ctype
ON this_.TYPE_ID = ctype.TYPE_ID
LEFT JOIN (
SELECT TYPE_ID, COUNT(*) as counter
FROM SIMPLETYPE
GROUP BY TYPE_ID
) as stype
ON this_.TYPE_ID = stype.TYPE_ID
LEFT JOIN ENUMERATION enu
ON enu.CONTAINING_ENUMERATION_ID =this_.TYPE_ID
GROUP BY TYPE_ID;

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com