Bug #12916 SELECT DON'T WORKING IN 5.0 AND WORKING IN 4.1

• Submitted: 31 Aug 2005 17:01
• Modified: 31 Aug 2005 17:53
• Reporter: Roberto Spadim (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.11
• OS: Linux (LINUX)
• CPU Architecture: Any

[31 Aug 2005 17:01] Roberto Spadim

Description:
this works:

SELECT MAX(lote_spa) FROM spamov_digitacao WHERE unidade_id=4 AND lote_tipo='v'

this don't work:

SELECT MAX(lote_spa) FROM spamov_digitacao WHERE unidade_id=4 AND lote_tipo='v' GROUP BY unidade_id,lote_tipo

How to repeat:
create table spamov_digitacao (unidade_id double,lote_tipo enum ('v'), lote_spa double);
insert into spamov_digitacao(4,'v',1);
insert into spamov_digitacao(4,'v',2);
insert into spamov_digitacao(4,'v',3);

Suggested fix:
?????? maybe optimizer is wrong???

[31 Aug 2005 17:53] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.12-beta-debug

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

mysql> create database br2;
Query OK, 1 row affected (0.03 sec)

mysql> use br2;
Database changed
mysql> create table spamov_digitacao (unidade_id double,lote_tipo enum ('v'), lote_spa
    -> double);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into spamov_digitacao values (4,'v',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into spamov_digitacao values (4,'v',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into spamov_digitacao values (4,'v',3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT MAX(lote_spa) FROM spamov_digitacao WHERE unidade_id=4 AND lote_tipo='v'
    -> 
    -> ;
+---------------+
| MAX(lote_spa) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(lote_spa) FROM spamov_digitacao WHERE unidade_id=4 AND lote_tipo='v'
    -> GROUP BY unidade_id,lote_tipo;
+---------------+
| MAX(lote_spa) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

mysql>