Sebastian Hoffmann <[EMAIL PROTECTED]> wrote: > > I have just updated from MySQL 3.23 to MySQL 4.0.14. My operating > system is Mac OS X (10.2.6). > > I have now encountered an odd problem with some "select" statements > which appear to work differently in the new version: > > I want to do a join on two tables which are in two different > databases. In 3.23.xx, the following worked fine: > > > mysql> SELECT count(bncUserData.1062255927_sebhoff_stat.fnum) FROM > bncUserData.1062255927_sebhoff_stat, bncData.headerInfo WHERE > bncUserData.1062255927_sebhoff_stat.fnum=bncData.headerInfo.fnum and > bncData.headerInfo.spowri=1; > +-------------------------------------------------+ > | count(bncUserData.1062255927_sebhoff_stat.fnum) | > +-------------------------------------------------+ > | 3 | > +-------------------------------------------------+ > 1 row in set (0.01 sec) > > > However, if I try the same thing (with slightly different table > names, but it's the same content and structure...) with 4.0.14, I get > the following: > > mysql> SELECT * FROM bncUserData.1062150666_sebhoff_stat, > bncData.headerInfo WHERE > bncUserData.1062150666_sebhoff_stat.fnum=bncData.headerInfo.fnum and > bncData.headerInfo.spowri=1; > ERROR 1109: Unknown table 'bncUserData.1062150666_sebhoff_stat' in where clause > > If I add aliases, however, everything works fine: > > mysql> SELECT count(t1.fnum) FROM bncUserData.1062150666_sebhoff_stat > as t1, bncData.headerInfo as t2 WHERE t1.fnum=t2.fnum and t2.spowri=1; > +----------------+ > | count(t1.fnum) | > +----------------+ > | 3 | > +----------------+ > 1 row in set (0.08 sec) > > Reading the manual didn't help - as far as I can tell, aliases are optional... > > What am I doing wrong? What have I missed?
Nothing. It's known bug, but it's not fixed yet. :( > > It would be pretty bad if I actually had to change the individual SQL > queries - they are all created on the fly by perl scripts and > rewriting this code would take a lot of time and debugging... > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]