Bug #82805 | Having clause condition fails on join table with TEXT field | ||
---|---|---|---|
Submitted: | 31 Aug 2016 2:20 | Modified: | 31 Aug 2016 9:47 |
Reporter: | Sabeer K | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.7.10, 5.7.14 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | regression |
[31 Aug 2016 2:20]
Sabeer K
[31 Aug 2016 5:30]
MySQL Verification Team
Hello Sabeer, Thank you for the report and test case. Observed that 5.7.14 build is affected. Thanks, Umesh
[31 Aug 2016 5:31]
MySQL Verification Team
-- 5.7.14 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.14: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test Database changed mysql> CREATE TABLE `TEST_DOCUMENT` ( -> `ID` int(14) NOT NULL AUTO_INCREMENT, -> `TEXT` text, -> PRIMARY KEY (`ID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO `TEST_DOCUMENT` (`ID`, `TEXT`) VALUES ('1', 'Test data'); INSERT INTO `TEST_DOCUMENT` (`ID`, `TEXT`) VALUES ('2', 'New Data came'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `TEST_DOCUMENT` (`ID`, `TEXT`) VALUES ('2', 'New Data came'); Query OK, 1 row affected (0.00 sec) mysql> mysql> CREATE TABLE `TEST_SENTIMENT` ( -> `ID` int(11) NOT NULL AUTO_INCREMENT, -> `SUB_TXT` text, -> `SCORE` decimal(7,3) DEFAULT NULL, -> `DOCUMENT_ID` int(11) DEFAULT NULL, -> PRIMARY KEY (`ID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('1', 'This is another', '0.250', '1'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('2', 'Testing 2', '0.500', '1'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('3', 'This is new data', '-0.250', '2'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('4', 'Testing new data 2', '1.500', '2'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT -> TEST_DOCUMENT.ID , TEST_DOCUMENT.TEXT, AVG(SCORE) SCORE -> FROM -> TEST_DOCUMENT, -> TEST_SENTIMENT -> WHERE -> TEST_DOCUMENT.ID = TEST_SENTIMENT.DOCUMENT_ID -> GROUP BY TEST_DOCUMENT.ID having SCORE > 0.6; +----+---------------+-----------+ | ID | TEXT | SCORE | +----+---------------+-----------+ | 1 | Test data | 0.3750000 | | 2 | New Data came | 0.6250000 | +----+---------------+-----------+ 2 rows in set (0.00 sec) mysql> explain SELECT TEST_DOCUMENT.ID , TEST_DOCUMENT.TEXT, AVG(SCORE) SCORE FROM TEST_DOCUMENT, TEST_SENTIMENT WHERE TEST_DOCUMENT.ID = TEST_SENTIMENT.DOCUMENT_ID GROUP BY TEST_DOCUMENT.ID having SCORE > 0.6; +----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | TEST_DOCUMENT | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | TEST_SENTIMENT | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql>
[31 Aug 2016 5:32]
MySQL Verification Team
-- Looks like regression to me -- 5.6.32, 5.5.51, 5.1.77, 5.0.96, 4.1.25 mysql> SELECT -> TEST_DOCUMENT.ID , TEST_DOCUMENT.TEXT, AVG(SCORE) SCORE -> FROM -> TEST_DOCUMENT, -> TEST_SENTIMENT -> WHERE -> TEST_DOCUMENT.ID = TEST_SENTIMENT.DOCUMENT_ID -> GROUP BY TEST_DOCUMENT.ID having SCORE > 0.6; +----+---------------+-----------+ | ID | TEXT | SCORE | +----+---------------+-----------+ | 2 | New Data came | 0.6250000 | +----+---------------+-----------+ 1 row in set (0.00 sec) mysql>
[31 Aug 2016 7:43]
Øystein Grøvlen
Posted by developer: A work-around seems to be to add "ORDER BY NULL": mysql> SELECT -> TEST_DOCUMENT.ID , TEST_DOCUMENT.TEXT, AVG(SCORE) SCORE -> FROM -> TEST_DOCUMENT, -> TEST_SENTIMENT -> WHERE -> TEST_DOCUMENT.ID = TEST_SENTIMENT.DOCUMENT_ID -> GROUP BY TEST_DOCUMENT.ID having SCORE > 0.6 -> ORDER BY NULL; +----+---------------+-----------+ | ID | TEXT | SCORE | +----+---------------+-----------+ | 2 | New Data came | 0.6250000 | +----+---------------+-----------+ 1 row in set (0,00 sec)
[31 Aug 2016 9:47]
Sabeer K
I have same issue when i added more order statement. Case 1: SELECT TEST_DOCUMENT.ID , TEST_DOCUMENT.TEXT, AVG(SCORE) SCORE FROM TEST_DOCUMENT, TEST_SENTIMENT WHERE TEST_DOCUMENT.ID = TEST_SENTIMENT.DOCUMENT_ID GROUP BY TEST_DOCUMENT.ID having SCORE > 0.6 ORDER BY TEST_DOCUMENT.ID desc; Case 2: SELECT TEST_DOCUMENT.ID , TEST_DOCUMENT.TEXT, AVG(SCORE) SCORE FROM TEST_DOCUMENT, TEST_SENTIMENT WHERE TEST_DOCUMENT.ID = TEST_SENTIMENT.DOCUMENT_ID GROUP BY TEST_DOCUMENT.ID having SCORE > 0.6 ORDER BY NULL,TEST_DOCUMENT.ID desc; Case 3: SELECT TEST_DOCUMENT.ID , TEST_DOCUMENT.TEXT, AVG(SCORE) SCORE FROM TEST_DOCUMENT, TEST_SENTIMENT WHERE TEST_DOCUMENT.ID = TEST_SENTIMENT.DOCUMENT_ID GROUP BY TEST_DOCUMENT.ID having SCORE > 0.6 ORDER BY TEST_DOCUMENT.ID desc,NULL;