[SOLVED] Upgrading from 4.x to 5.x - Join processing changes problem

[muzhik]

I'm trying to migrate a DB from mysql 4.x to 5.0.54, and have run into an error apparently due to the join processing changes implemented in 5.0.12, as described in        http://dev.mysql.com/doc/refman/5.0/en/join.html

 

My query is:

 

SELECT customer.email,
        customer.password,
        customer.username,
        customer.customer_id,
        customer.partner_id,
        customer.bstor,
        customer.zip,
        customer.country,
        customer.state,
        customer.address,
        customer.city,
        account.startdate,
        account.account_id,
        account.trial_end_date,
        account.creditcard_id,
        account.albumcounter,
        account.referer,
        account.account_expires,
        account.autorenewal,
        account.account_status,
        account.securitylevel,
        customer.count,
        customer.globalpassword,
        quota,
        membership_history.membership_level_id,
        creditcard.number,
        creditcard.type,
        creditcard.expiration_date,
        creditcard.cardholder,
        creditcard.zip,
        creditcard.country,
        emailsent2.emailreminder_pro,
        emailsent2.email_frozen,
        emailsent2.emailreminder_trial,
        Member2.membership_level_id AS DecreaseLevelId

FROM customer, account, membership_history

left outer join membership_history Member2 on account.account_id = Member2.account_id AND Member2.comments = "decrease"
left outer join creditcard on account.creditcard_id = creditcard.creditcard_id
left outer join emailsent2 on emailsent2.account_id = account.account_id

WHERE customer.customer_id = account.customer_id

AND customer.username = 'dev2test'
AND account.account_id = membership_history.account_id
AND username <> ""
AND account.account_status <> "I"
AND account.startdate <> "NULL"
AND membership_history.comments = "current";

 

This returns the erro:

 

ERROR 1054 (42S22) at line 1: Unknown column 'account.account_id' in 'on clause'

 

 

I am far from an SQL guru, so if anyone has a suggestion as to how to reconfigure this query properly in order to work with 5.x, any help would be most appreciated.

[fenway]

Convert your comma to proper INNER JOINs... the precedence of the comma changed in 5.0.

 

Alternatively, wrap the first three tables in parens.

[muzhik]

Convert your comma to proper INNER JOINs... the precedence of the comma changed in 5.0.

 

Thanks - I understand that, but I have no clue how to go about converting any joins...  Could you provide an example perhaps??

 

When I said "this is my query" I should have I suppose said "this is the query" - as generated by this application. I didn't write this!

 

Alternatively, wrap the first three tables in parens.

 

I've tried various combinations of parens in/around the tables, but have so far only been able to generate a long sequence of syntax errors......

 

Again, any more detailed help appreciated.

[fenway]

Replace

 

FROM customer, account, membership_history

 

with

 

FROM ( customer, account, membership_history )

 

As for proper join syntax (drop the on conditions from your where clause):

 

FROM customer
inner join account on ( customer.customer_id = account.customer_id )
inner join membership_history on ( account.account_id = membership_history.account_id )
left outer join membership_history Member2 on ( account.account_id = Member2.account_id AND Member2.comments = "decrease" )
left outer join creditcard on ( account.creditcard_id = creditcard.creditcard_id )
left outer join emailsent2 on ( emailsent2.account_id = account.account_id )
WHERE 
....

 

 

[muzhik]

Thanks, that did it.