MySQL Bugs: #82805: Having clause condition fails on join table with TEXT field

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:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.10, 5.7.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
I have tested 5.7.10 AWS RDS instance.

I created two tables, Test Script follows

CREATE TABLE `TEST_DOCUMENT` (
  `ID` int(14) NOT NULL AUTO_INCREMENT,
  `TEXT` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `TEST_DOCUMENT` (`ID`, `TEXT`) VALUES ('1', 'Test data');
INSERT INTO `TEST_DOCUMENT` (`ID`, `TEXT`) VALUES ('2', 'New Data came');

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;

INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('1', 'This is another', '0.250', '1');
INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('2', 'Testing 2', '0.500', '1');
INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('3', 'This is new data', '-0.250', '2');
INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('4', 'Testing new data 2', '1.500', '2');

SQL
===
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

My expected Result

2	New Data came	0.6250000

But the result is 

1	Test data	0.3750000
2	New Data came	0.6250000

How to repeat:
Drop table TEST_DOCUMENT;
Drop table TEST_SENTIMENT;

CREATE TABLE `TEST_DOCUMENT` (
  `ID` int(14) NOT NULL AUTO_INCREMENT,
  `TEXT` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `TEST_DOCUMENT` (`ID`, `TEXT`) VALUES ('1', 'Test data');
INSERT INTO `TEST_DOCUMENT` (`ID`, `TEXT`) VALUES ('2', 'New Data came');

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;

INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('1', 'This is another', '0.250', '1');
INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('2', 'Testing 2', '0.500', '1');
INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('3', 'This is new data', '-0.250', '2');
INSERT INTO `TEST_SENTIMENT` (`ID`, `SUB_TXT`, `SCORE`, `DOCUMENT_ID`) VALUES ('4', 'Testing new data 2', '1.500', '2');

SQL
===
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

Hello Sabeer,

Thank you for the report and test case.
Observed that 5.7.14 build is affected.

Thanks,
Umesh

-- 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>

-- 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>

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)

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;