Bug #34008 Server crashes with simple query involving DISTINCT, DIV and VARCHAR
Submitted: 23 Jan 2008 14:22 Modified: 28 Jan 2008 19:20
Reporter: Mikel Astiz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.41 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: crash, distinct, div, varchar

[23 Jan 2008 14:22] Mikel Astiz
Description:
MySQL server crashes in Windows (but apparently not in Linux) with the following query:

SELECT DISTINCT id, str DIV 2 FROM test

If the field 'str' is a VARCHAR, the server crashes.

There is no problem if DISTINCT is not used, or if the field is a DOUBLE.

How to repeat:
1. Create the table: 

CREATE TABLE  `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `str` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Add some information. For example:

INSERT INTO `test` (`str`) VALUES ('100.95334');
INSERT INTO `test` (`str`) VALUES ('100.95334');
INSERT INTO `test` (`str`) VALUES ('200.48334');
INSERT INTO `test` (`str`) VALUES ('200.48334');
INSERT INTO `test` (`str`) VALUES ('200.48334');
INSERT INTO `test` (`str`) VALUES ('210.48334');
INSERT INTO `test` (`str`) VALUES ('1210.148334');

3. Execute the query:

SELECT DISTINCT id, str DIV 2 FROM test;
[23 Jan 2008 15:05] Valeriy Kravchuk
Please, tyr to repeat with a newer version, 5.0.45 or 5.0.51. I can not repeat the behaviour described:

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.54-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.02 sec)

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test'
mysql> CREATE TABLE  `test` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `str` varchar(20) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO `test` (`str`) VALUES ('100.95334');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `test` (`str`) VALUES ('100.95334');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `test` (`str`) VALUES ('200.48334');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `test` (`str`) VALUES ('200.48334');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `test` (`str`) VALUES ('200.48334');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `test` (`str`) VALUES ('210.48334');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO `test` (`str`) VALUES ('1210.148334');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT DISTINCT id, str DIV 2 FROM test;
+----+-----------+
| id | str DIV 2 |
+----+-----------+
|  1 |        50 |
|  2 |        50 |
|  3 |       100 |
|  4 |       100 |
|  5 |       100 |
|  6 |       105 |
|  7 |       605 |
+----+-----------+
7 rows in set (0.03 sec)
[23 Jan 2008 15:16] Mikel Astiz
The bug seems to be fixed in 5.0.51.
[28 Jan 2008 19:20] Susanne Ebrecht
Many thanks for the feedback.

I'll close this bug now.
If you still get problems, please feel free to open it again.