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?
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...
Many thanx in advance for any suggestions! Sebastian
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
