Bug #18457 wrong, non-empty recordset returned

• Submitted: 23 Mar 2006 14:46
• Modified: 8 May 2006 2:17
• Reporter: André Krug
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 4.1 BK
• OS: Linux (Linux Suse 10)
• Assigned to: Assigned Account
• CPU Architecture: Any

[23 Mar 2006 14:46] André Krug

Description:
Query:

select 42 as sample1, count(t2.id) as sample2
from tab1 t1 
left join tab2 t2 on (t2.id = 0)
where t1.id in (1,2)
group by t1.id 
having sample1=23 and sample2=0
order by sample2;

(description of case in how to repeat)

returns following wrong recordset:

 sample1   	  sample2
    42 	                 0
    42 	                 0

In correct case the recordset should be empty.
Tested on Debian Sarge, MYSQL Server 4.1.15 and 5.0.18

How to repeat:
create table tab1 (id int(11));
create table tab2 (id int(11));
insert into tab1 values (1),(2);
insert into tab2 values (2);
select 42 as sample1, count(t2.id) as sample2 
from tab1 t1 
left join tab2 t2 on (t2.id = 0)
where t1.id in (1,2)
group by t1.id 
having sample1=23 and sample2=0
order by sample2;

[23 Mar 2006 15:16] MySQL Verification Team

Thank you for the bug report. I was able to repeat only with the
current source server 4.1:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.19-debug-log

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

mysql> create table tab1 (id int(11));
Query OK, 0 rows affected (0.01 sec)

mysql> create table tab2 (id int(11));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tab1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tab2 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select 42 as sample1, count(t2.id) as sample2 
    -> from tab1 t1 
    -> left join tab2 t2 on (t2.id = 0)
    -> where t1.id in (1,2)
    -> group by t1.id 
    -> having sample1=23 and sample2=0
    -> order by sample2;
+---------+---------+
| sample1 | sample2 |
+---------+---------+
|      42 |       0 |
|      42 |       0 |
+---------+---------+
2 rows in set (0.02 sec)

mysql> 
------------------------------------------------------------------

mysql> create table tab1 (id int(11));
Query OK, 0 rows affected (0.01 sec)

mysql> create table tab2 (id int(11));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tab1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tab2 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select 42 as sample1, count(t2.id) as sample2 
    -> from tab1 t1 
    -> left join tab2 t2 on (t2.id = 0)
    -> where t1.id in (1,2)
    -> group by t1.id 
    -> having sample1=23 and sample2=0
    -> order by sample2;
Empty set (0.01 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.20-debug |
+--------------+
1 row in set (0.00 sec)

[23 Mar 2006 15:50] Hartmut Holzgraefe

fails on 5.0.18, returns an empty set as expected on 5.0.19

[23 Mar 2006 16:13] MySQL Verification Team

Ok. 4.1 still has the bug with the latest source I got 1 hour ago:

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.19-debug-log

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

mysql> create table tab1 (id int(11));
Query OK, 0 rows affected (0.01 sec)

mysql> create table tab2 (id int(11));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tab1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tab2 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select 42 as sample1, count(t2.id) as sample2 
    -> from tab1 t1 
    -> left join tab2 t2 on (t2.id = 0)
    -> where t1.id in (1,2)
    -> group by t1.id 
    -> having sample1=23 and sample2=0
    -> order by sample2;
+---------+---------+
| sample1 | sample2 |
+---------+---------+
|      42 |       0 |
|      42 |       0 |
+---------+---------+
2 rows in set (0.00 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/4.1> bk changes /home/miguel/dbs/mysql-4.1/ | head
ChangeSet@1.2474, 2006-03-23 14:29:43+04:00, [e-mail]
  Merge [e-mail]:/home/bk/mysql-4.1
  into  mysql.com:/usr/home/bar/mysql-4.1.b15376

ChangeSet@1.2471.1.1, 2006-03-20 16:28:25+04:00, [e-mail]
  Bug#17374: select ... like 'A%' operator fails to find value on columuns with key
  Fixed that LIKE worked case insensitively for latin2_czech_cs,
  which was wrong for a case sensitive collation.

ChangeSet@1.2472, 2006-03-20 14:43:02+04:00, [e-mail]
miguel@hegel:~/dbs/4.1>

[8 May 2006 2:17] Igor Babaev

This bug is a duplicate of bug #14927.
The fact is that patch for bug #14927 was applied 4.1 only today, though it was appliied to 5.0 three. months ago.

After that patch had been applied I got:
ysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.20-debug |
+--------------+
1 row in set (0.00 sec)

mysql> create table tab1 (id int(11));
Query OK, 0 rows affected (0.43 sec)

mysql> create table tab2 (id int(11));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tab1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tab2 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select 42 as sample1, count(t2.id) as sample2
    -> from tab1 t1
    -> left join tab2 t2 on (t2.id = 0)
    -> where t1.id in (1,2)
    -> group by t1.id
    -> having sample1=23 and sample2=0
    -> order by sample2;
Empty set (0.00 sec)