Bug #1927 Subqueries give wrong results

• Submitted: 23 Nov 2003 14:49
• Modified: 23 Nov 2003 15:03
• Reporter: Isaac Salsberg
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: Ver 13.5 Distrib 4.1.0-alpha, for Win95/
• OS: Windows (W2K)
• CPU Architecture: Any

[23 Nov 2003 14:49] Isaac Salsberg

Description:
certain subqueries give wrong results:

By instance:

select artic,precio from precios main
where 3 > (select count(distinct artic) 
             from precios sub 
            where main.precio > sub.precio)
order by 1,2;

Gives the following output WHICH IS WRONG!!!:
+-------+--------+
| artic | precio |
+-------+--------+
| aaa   | 100.00 |
+-------+--------+
1 row in set (0.00 sec)

How to repeat:
create table precios(
artic char(5),precio decimal(7,2));
insert into precios values('aaa',100.00);
insert into precios values('bbb',500.00);
insert into precios values('ccc',300.00);
insert into precios values('ddd',900.00);
insert into precios values('eee',200.00);
insert into precios values('fff',170.00);
insert into precios values('ggg',080.00);
insert into precios values('hhh',800.00);
insert into precios values('iii',100.00);

select artic,precio from precios main
where 3 > (select count(distinct artic) 
             from precios sub 
            where main.precio > sub.precio)
order by 1,2;

+-------+--------+
| artic | precio |
+-------+--------+
| aaa   | 100.00 |
+-------+--------+
1 row in set (0.00 sec)

When The correct output should be:

artic    precio

aaa      100.00
ggg       80.00
iii      100.00

[23 Nov 2003 15:03] MySQL Verification Team

Thank you for the bug report however this issue was already fixed
in the current development tree and will be present in the incoming
release:

mysql> select artic,precio from precios main
    -> where 3 > (select count(distinct artic)
    ->              from precios sub
    ->             where main.precio > sub.precio)
    -> order by 1,2;
+-------+--------+
| artic | precio |
+-------+--------+
| aaa   | 100.00 |
| ggg   |  80.00 |
| iii   | 100.00 |
+-------+--------+
3 rows in set (0.11 sec)

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 4.1.1-alpha-max-nt |
+--------------------+
1 row in set (0.00 sec)