Bug #31281 Group by and subquery issue in 5.0.41-community-nt-log

• Submitted: 28 Sep 2007 15:09
• Modified: 28 Oct 2007 15:24
• Reporter: HECTOR JUAREZ
• Status: No Feedback
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.0.41-community-nt-log
• OS: Windows
• CPU Architecture: Any
• Tags: 5.0.41-community-nt-log, GROUP BY, subquery

[28 Sep 2007 15:09] HECTOR JUAREZ

Description:
I have a problem with a query that has a subquery in the where clause and a group by in the main query. The subquery depends on main query data and is used for filter some data of the main query.
The problem is that in version 5.0.41-community-nt-log the query does not return any data, while in 5.0.26-community-nt is working fine.
In 'How to repeat' section there's a complete script for duplicate the problem, please test it in both versions above mentioned and you'll see the differences.

Thanks.

How to repeat:
drop table if exists t1;
create table t1(
id bigint(20) PRIMARY KEY NOT NULL,
id1 bigint(20) not null,
id2 bigint(20) not null,
value1 varchar(255) null,
value2 int null
);

drop table if exists t2;
create table t2(
id bigint(20) PRIMARY KEY NOT NULL,
idt1 bigint(20) not null,
id3 bigint(20) not null,
value1 varchar(255) null,
value2 int null
);

drop table if exists t2_relleno_id3;
create table t2_relleno_id3(
id bigint(20) PRIMARY KEY NOT NULL,
value_col varchar(255) not null unique
);

drop table if exists t3;
create table t3(
id bigint(20) PRIMARY KEY NOT NULL,
id1 bigint(20) not null,
id2 bigint(20) not null,
id3 bigint(20) not null,
value1 varchar(255) null,
value2 int null
);

insert into t1 (id,id1,id2,value1,value2) values
(1,1,1,"head id1=1 - id2=1 rep!",2),
(2,1,2,"head id1=1 - id2=2 rep!",2),
(3,2,1,"head id1=2 - id2=1",2),
(4,3,1,"head id1=3 - id2=1",2);

insert into t2 (id,idt1,id3,value1,value2) values
(1,1,1,"detail id1=1 - id2=1 - id3=1",2),
(2,1,2,"detail id1=1 - id2=1 - id3=2",2),
(3,2,1,"detail id1=1 - id2=2 - id3=1",9),
(4,2,2,"detail id1=1 - id2=2 - id3=2",9),
(5,3,1,"detail id1=2 - id2=1 - id3=1",3),
(6,3,2,"detail id1=2 - id2=1 - id3=2",3),
(7,4,1,"detail id1=3 - id2=1 - id3=1",3),
(8,4,2,"detail id1=3 - id2=1 - id3=2",3);

insert into t2_relleno_id3 (id,value_col) values
(1,"value 1"),(2,"value 2");

select tr.value_col ,sum(t2.value2),
(select min(t2a.value2) from t1 t1a,t2 t2a
where t1a.id = t2a.idt1 and t1a.id1=t1.id1 and t2a.id3=t2.id3
) as test
from t1,t2,t2_relleno_id3 tr
where t1.id = t2.idt1
and t2.value2 =
(select min(t2a.value2) from t1 t1a,t2 t2a
where t1a.id = t2a.idt1 and t1a.id1=t1.id1 and t2a.id3=t2.id3
)
and t2.id3 = tr.id
group by tr.value_col;

[28 Sep 2007 15:24] MySQL Verification Team

Thank you for the bug report. Could you please try the latest release?.
Thanks in advance.

mysql> select tr.value_col ,sum(t2.value2),
    -> (select min(t2a.value2) from t1 t1a,t2 t2a
    -> where t1a.id = t2a.idt1 and t1a.id1=t1.id1 and t2a.id3=t2.id3
    -> ) as test
    -> from t1,t2,t2_relleno_id3 tr
    -> where t1.id = t2.idt1
    -> and t2.value2 =
    -> (select min(t2a.value2) from t1 t1a,t2 t2a
    -> where t1a.id = t2a.idt1 and t1a.id1=t1.id1 and t2a.id3=t2.id3
    -> )
    -> and t2.id3 = tr.id
    -> group by tr.value_col;
+-----------+----------------+------+
| value_col | sum(t2.value2) | test |
+-----------+----------------+------+
| value 1   |              8 |    2 |
| value 2   |              8 |    2 |
+-----------+----------------+------+
2 rows in set (0.16 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.45-community-nt |
+---------------------+
1 row in set (0.01 sec)

mysql>

[29 Oct 2007 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".