Bug #39433 poor optimized query
Submitted: 13 Sep 2008 17:52 Modified: 21 Mar 2011 4:15
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.45, 5.0.70, 5.1.30 OS:Linux
Assigned to:
Tags: qc
Triage: Triaged: D3 (Medium) / R4 (High) / E4 (High)

[13 Sep 2008 17:52] Roberto Spadim
Description:
poor optimized plan

How to repeat:
query and table in files

Suggested fix:
check a better plan, maybe first 
select distinct field moeda and after check subquery
[15 Sep 2008 7:23] Valerii Kravchuk
Thank you for a problem report. Verified on your test case with latest 5.0.70 from bzr. Adding FORCE INDEX(moeda) makes query to run hundreds times faster...
[15 Sep 2008 20:16] Roberto Spadim
FORCE INDEX worked on my mysql server version
thanks guys
[24 Dec 2008 12:00] Evgeny Potemkin
An example showing a wrong result. Select list should be evaluated only once per group, but the first select with GROUP BY evaluates it for each row.
The second query evaluates it correctly:

mysql> delimiter |
mysql> drop function bug39433|
Query OK, 0 rows affected (0.00 sec)

mysql> create function bug39433() returns int  begin set @var39433=(@var39433 + 1); return 0; end|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> create table t1 (f1 int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t1 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select @var39433:=0;
+--------------+
| @var39433:=0 |
+--------------+
|            0 | 
+--------------+
1 row in set (0.01 sec)

mysql> select bug39433(),f1 from t1 group by f1;
+------------+------+
| bug39433() | f1   |
+------------+------+
|          0 |    1 | 
+------------+------+
1 row in set (0.00 sec)

mysql> select @var39433;
+-----------+
| @var39433 |
+-----------+
|        24 | 
+-----------+
1 row in set (0.00 sec)

mysql> select @var39433:=0;
+--------------+
| @var39433:=0 |
+--------------+
|            0 | 
+--------------+
1 row in set (0.00 sec)

mysql> select bug39433(),f1, count(*) from t1 group by f1;
+------------+------+----------+
| bug39433() | f1   | count(*) |
+------------+------+----------+
|          0 |    1 |       24 | 
+------------+------+----------+
1 row in set (0.00 sec)

mysql> select @var39433;
+-----------+
| @var39433 |
+-----------+
|         1 | 
+-----------+
1 row in set (0.00 sec)

mysql>
[21 Mar 2011 4:15] Roberto Spadim
closed it's something similar to bug 24770