| Bug #12728 | Very strange behaviour of ELT | ||
|---|---|---|---|
| Submitted: | 22 Aug 2005 17:18 | Modified: | 26 Jun 2006 15:15 |
| Reporter: | Tobias Wiersch | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 3.23 and 4.0.18 | OS: | Windows (Windows and Linux) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[22 Aug 2005 17:44]
MySQL Verification Team
Thank you for the bug report.
This bug not affects >= 4.1.
miguel@hegel:~/dbs/4.0> bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.26-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `test` (
-> `Word` varchar(8) NOT NULL default '',
-> PRIMARY KEY (`Word`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO `test` VALUES ('Bar');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test` VALUES ('Foo');
Query OK, 1 row affected (0.00 sec)
mysql> select Word from test t where ( t.Word=ELT(1,'Foo','Bar') or
-> t.Word=ELT(2,'Foo','Bar') )
->
-> ;
+------+
| Word |
+------+
| Foo |
+------+
1 row in set (0.01 sec)
mysql>
miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `test` (
-> `Word` varchar(8) NOT NULL default '',
-> PRIMARY KEY (`Word`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> INSERT INTO `test` VALUES ('Bar');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `test` VALUES ('Foo');
Query OK, 1 row affected (0.01 sec)
mysql> select Word from test t where ( t.Word=ELT(1,'Foo','Bar') or
-> t.Word=ELT(2,'Foo','Bar') )
-> ;
+------+
| Word |
+------+
| Bar |
| Foo |
+------+
2 rows in set (0.00 sec)
[13 Jun 2006 10:59]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/7563
[14 Jun 2006 10:46]
Alexander Barkov
The patch is ok to push.
[15 Jun 2006 11:24]
Ramil Kalimullin
fixed in 4.0.28
[26 Jun 2006 15:15]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Documented bugfix in 4.0.28 changelog. Closed.
Description: To reproduce the problem, create the test-table (look "how to repeat"). Now have a look at this query: select Word from test t where ( t.Word=ELT(1,'Foo','Bar') or t.Word=ELT(2,'Foo','Bar') ) This should give me both rows of the table. But I only get the first row. Maybe I missed something (a feature?) but I think this is a bug.. The strangest thing is: select Word from test t where ( t.Word=ELT(1,'Foo','BarX') or t.Word=ELT(2,'Foo','Bar') ) The first ELT gets Foo and not BarX but because BarX!=Bar, suddenly the second ELT works correctly and I will get both rows!? Whats going on here?? Very very strange. How to repeat: CREATE TABLE `test` ( `Word` varchar(8) NOT NULL default '', PRIMARY KEY (`Word`) ) TYPE=MyISAM; INSERT INTO `test` VALUES ('Bar'); INSERT INTO `test` VALUES ('Foo');