| Bug #33032 | Incorrect result with UNION and user-defined variables | ||
|---|---|---|---|
| Submitted: | 6 Dec 2007 10:06 | Modified: | 7 Dec 2007 15:35 |
| Reporter: | Rinat Nasibullin | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0.45-community-nt | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[6 Dec 2007 10:10]
Rinat Nasibullin
Related bug http://bugs.mysql.com/31255
[6 Dec 2007 11:08]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php You will get same result if you issue instead of first SELECT: set @father_id=1; SELECT p.id, p.from_marriage_id, name, @father_id := m.man_id AS father_id FROM person p LEFT JOIN marriage m ON (p.from_marriage_id = m.id) WHERE p.id = @father_id;
[7 Dec 2007 15:35]
Rinat Nasibullin
Ok, it isn't UNION problem, just user-defined variables.
mysql> SET @father_id := 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @father_id;
+------------+
| @father_id |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT p.id, p.from_marriage_id, name,
-> @father_id2 := m.man_id AS father_id
-> FROM person p LEFT JOIN marriage m ON (p.from_marriage_id = m.id)
-> WHERE p.id = @father_id;
+----+------------------+------+-----------+
| id | from_marriage_id | name | father_id |
+----+------------------+------+-----------+
| 1 | 3 | J | 6 |
+----+------------------+------+-----------+
1 row in set (0.00 sec)
mysql> SELECT p.id, p.from_marriage_id, name,
-> @father_id := m.man_id AS father_id
-> FROM person p LEFT JOIN marriage m ON (p.from_marriage_id = m.id)
-> WHERE p.id = @father_id;
+----+------------------+------+-----------+
| id | from_marriage_id | name | father_id |
+----+------------------+------+-----------+
| 1 | 3 | J | 6 |
| 6 | 1 | F | 11 |
| 11 | NULL | A | NULL |
+----+------------------+------+-----------+
3 rows in set (0.00 sec)
Is last query correct?
Description: Incorrect result with UNION and user-defined variables. This is true only if use joined tables. How to repeat: -- MySQL Administrator dump 1.4 -- -- ------------------------------------------------------ -- Server version 5.0.45-community-nt /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- -- Create schema test -- CREATE DATABASE IF NOT EXISTS test; USE test; -- -- Definition of table `marriage` -- DROP TABLE IF EXISTS `marriage`; CREATE TABLE `marriage` ( `id` int(10) unsigned NOT NULL auto_increment, `man_id` int(10) unsigned default NULL, `woman_id` int(10) unsigned default NULL, PRIMARY KEY (`id`), KEY `man_id` (`man_id`), KEY `woman_id` (`woman_id`), CONSTRAINT `woman_id` FOREIGN KEY (`woman_id`) REFERENCES `person` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `man_id` FOREIGN KEY (`man_id`) REFERENCES `person` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- -- Dumping data for table `marriage` -- /*!40000 ALTER TABLE `marriage` DISABLE KEYS */; INSERT INTO `marriage` (`id`,`man_id`,`woman_id`) VALUES (1,11,12), (2,13,14), (3,6,8); /*!40000 ALTER TABLE `marriage` ENABLE KEYS */; -- -- Definition of table `person` -- DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `id` int(10) unsigned NOT NULL auto_increment, `from_marriage_id` int(10) unsigned default NULL, `name` varchar(45) default NULL, PRIMARY KEY (`id`), KEY `parent_id` USING BTREE (`from_marriage_id`), CONSTRAINT `from_marriage_id` FOREIGN KEY (`from_marriage_id`) REFERENCES `marriage` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; -- -- Dumping data for table `person` -- /*!40000 ALTER TABLE `person` DISABLE KEYS */; INSERT INTO `person` (`id`,`from_marriage_id`,`name`) VALUES (1,3,'J'), (5,1,'E'), (6,1,'F'), (7,2,'G'), (8,2,'H'), (9,2,'I'), (11,NULL,'A'), (12,NULL,'B'), (13,NULL,'C'), (14,NULL,'D'); /*!40000 ALTER TABLE `person` ENABLE KEYS */; -------------------------------------------------------------------------- mysql> use test; Database changed mysql> SELECT p.id, p.from_marriage_id, name, -> @father_id := m.man_id AS father_id -> FROM person p LEFT JOIN marriage m ON (p.from_marriage_id = m.id) -> WHERE p.id = 1 -> UNION -> SELECT p.id, p.from_marriage_id, name, -> @father_id := m.man_id AS father_id -> FROM person p LEFT JOIN marriage m ON (p.from_marriage_id = m.id) -> WHERE p.id = @father_id; +----+------------------+------+-----------+ | id | from_marriage_id | name | father_id | +----+------------------+------+-----------+ | 1 | 3 | J | 6 | | 6 | 1 | F | 11 | | 11 | NULL | A | NULL | +----+------------------+------+-----------+ 3 rows in set (0.00 sec) Suggested fix: If I will rename first @father_id to @father_id2 in second query then result will be correct.