Bug #31825 GROUP BY problem

• Submitted: 24 Oct 2007 20:35
• Modified: 25 Oct 2007 17:13
• Reporter: Ken DV
• Status: Can't repeat
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.0.41
• OS: Windows (Not tested on other OS'es)
• CPU Architecture: Any
• Tags: GROUP, SELECT

[24 Oct 2007 20:35] Ken DV

Description:
When executing a query such as this:

    SELECT * FROM keywords AS kw 
    WHERE keyword = 'test' 
    AND keyword NOT IN ( 
        SELECT keyword FROM keywords 
        WHERE keyword = kw.keyword 
    ) 
    GROUP BY kw.keyword

There is a result contrary to MySQL v5.0.22 (tested on FreeBSD) which does not return a result. 

When omitting the GROUP BY statement, there are no results returned (as is supposed to).

How to repeat:
Execute this query on MySQL v5.0.41:

    SELECT * FROM keywords AS kw 
    WHERE keyword = 'test' 
    AND keyword NOT IN ( 
        SELECT keyword FROM keywords 
        WHERE keyword = kw.keyword 
    ) 
    GROUP BY kw.keyword

[24 Oct 2007 22:43] MySQL Verification Team

Thank you for the bug report. Could you please provide the complete test
case: create table; insert data; offended query; expected result and
wrong result presented. Thanks in advance.

[25 Oct 2007 8:51] Ken DV

CREATE DATABASE `test` ;
CREATE TABLE `keywords` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`keyword` VARCHAR( 20 ) NOT NULL 
) ENGINE = MYISAM ;
INSERT INTO `test`.`keywords` (`id`, `keyword`) VALUES (NULL, 'test');
INSERT INTO `test`.`keywords` (`id`, `keyword`) VALUES (NULL, 'test2');

Offending query:

SELECT * 
FROM keywords AS kw
WHERE keyword = 'test'
AND keyword NOT 
IN (

SELECT keyword
FROM keywords
WHERE keyword = kw.keyword
)
GROUP BY kw.keyword
LIMIT 0 , 30 

Expected results: 0 rows
Actual results: 1 row

[25 Oct 2007 17:13] MySQL Verification Team

Thank you for the bug report. I could not repeat with latest release,
please upgrade:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> CREATE TABLE `keywords` (
    -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    -> `keyword` VARCHAR( 20 ) NOT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO `test`.`keywords` (`id`, `keyword`) VALUES (NULL, 'test');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO `test`.`keywords` (`id`, `keyword`) VALUES (NULL, 'test2');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *
    -> FROM keywords AS kw
    -> WHERE keyword = 'test'
    -> AND keyword NOT
    -> IN (
    ->
    -> SELECT keyword
    -> FROM keywords
    -> WHERE keyword = kw.keyword
    -> )
    -> GROUP BY kw.keyword
    -> LIMIT 0 , 30;
Empty set (0.13 sec)

mysql>