| Bug #17014 | table can not be used after "using ... as " | ||
|---|---|---|---|
| Submitted: | 1 Feb 2006 16:15 | Modified: | 9 Feb 2006 13:47 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.18 | OS: | Linux (LINUX 2.4.32) |
| Assigned to: | CPU Architecture: | Any | |
[9 Feb 2006 13:47]
Valeriy Kravchuk
MUPLTI DELETE should mean "multiple-table DELETE" - the operation you are trying to perform. Read the manual (http://dev.mysql.com/doc/refman/4.1/en/delete.html) about it: "From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.7.1, “JOIN Syntax”. The first multiple-table DELETE syntax is supported starting from MySQL 4.0.0. The second is supported starting from MySQL 4.0.2. For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching: DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id; Or: DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id; These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2. The preceding examples show inner joins that use the comma operator, but multiple-table DELETE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN. The syntax allows .* after the table names for compatibility with Access. If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly. Note: In MySQL 4.0, you should refer to the table names to be deleted with the true table name. In MySQL 4.1, you must use the alias (if one was given) when referring to a table name: In MySQL 4.0: DELETE test FROM test AS t1, test2 WHERE ... In MySQL 4.1: DELETE t1 FROM test AS t1, test2 WHERE ... The reason we did not make this change in 4.0 was to avoid breaking any old 4.0 applications that were using the old syntax." The Note in this long quote from the manual also explains, why the behaviour you described is not a bug in MySQL >=4.1. It is intended and documented behaviour in newer versions.
Description: This Delete : delete from `ftp_user` using ftp_hauptuser as fh,ftp_user as fu WHERE fh.username = '<-username->' AND fh.uid = fu.uid; where the content between "<" and ">" is replaced, works under 4.0.24 as it should. Under 5.0 for the same statement we get a Unknown table 'ftp_user' in MULTI DELETE error. ( mysqld Ver 5.0.18-standard-log for pc-linux-gnu on i686 (MySQL Community Edition - Standard (GPL)) ) our temporary FIX is: delete from fu using ftp_hauptuser as fh,ftp_user as fu WHERE fh.username = '<-username->' AND fh.uid = fu.uid; this means, you can not use the tablename after the from, but the later defined ALIAS works pretty well. We think this is a bug in 5.0 as it works well under 4.0.24 and below. 4.1 not tested. ( mysqld Ver 4.0.24-standard for pc-linux-gnu on i686 (Official MySQL RPM) ) How to repeat: These are our table descriptions , just fill something in which will fullfill the Whereclause: WHERE fh.username = 'BEN' AND fh.uid = fu.uid; -- -- Table structure for table `ftp_hauptuser` -- DROP TABLE IF EXISTS `ftp_hauptuser`; CREATE TABLE IF NOT EXISTS `ftp_hauptuser` ( `id` int(11) NOT NULL auto_increment, `domain` char(255) NOT NULL default '', `username` char(255) NOT NULL default '', `maxuser` int(11) NOT NULL default '0', `uid` int(11) NOT NULL default '0', `gid` int(11) NOT NULL default '0', `nextid` int(11) NOT NULL default '1', `home` char(255) NOT NULL default '', `eigeneip` enum('nein','ja') NOT NULL default 'nein', `aktiv` enum('ja','nein') NOT NULL default 'ja', `ip` char(20) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Beschreibt den User des HauptAccounts' AUTO_INCREMENT=30 ; -- -------------------------------------------------------- -- -- Table structure for table `ftp_user` -- DROP TABLE IF EXISTS `ftp_user`; CREATE TABLE IF NOT EXISTS `ftp_user` ( `host` char(128) NOT NULL default '', `userid` char(128) NOT NULL default '', `passwd` char(30) NOT NULL default '', `uid` int(11) default NULL, `gid` int(11) default '65000', `homedir` char(255) default NULL, `shell` char(255) default '/bin/bash', `count` int(11) NOT NULL default '0', `rechte` enum('MIXED','DOWNLOAD','UPLOAD') NOT NULL default 'MIXED', `eigeneip` enum('nein','ja') NOT NULL default 'nein', UNIQUE KEY `userid` (`userid`,`host`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ################################### my.cnf ################################### # cat /etc/my.cnf [mysqld] port = 3306 socket = /opt/root/tmp/mysql.sock skip-show-database old-passwords set-variable = wait_timeout=300 set-variable = max_connections=40 set-variable = thread_cache_size=5 set-variable = max_heap_table_size=100M set-variable = max_join_size=40000000 set-variable = max_allowed_packet=10M set-variable = max_binlog_cache_size=100M set-variable = sort_buffer=64000 set-variable = query_cache_type=1 set-variable = query_cache_size=1000000 log = /tmp/mysql.log [mysql] socket = /opt/root/tmp/mysql.sock Suggested fix: just don't kill the tablename throu an alias definition :)