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]

Reply via email to