Bug #25209 Select Into with order by

• Submitted: 20 Dec 2006 11:34
• Modified: 20 Dec 2006 11:52
• Reporter: Anca Dogaru
• Status: Closed
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.27-standard
• OS: unix
• CPU Architecture: Any

[20 Dec 2006 11:34] Anca Dogaru

Description:
SELECT INTO combined with COUNT and ORDER BY will not return corect result.

How to repeat:
scenario:
we have the following table:

CREATE TABLE `test` (
`id` INT NOT NULL ,
`name` VARCHAR( 21 ) NOT NULL
) TYPE = innodb;

with following records:

INSERT INTO `test` VALUES (1, 'first_rescord');
INSERT INTO `test` VALUES (1, 'second_record');
INSERT INTO `test` VALUES (3, 'third_record');

we create a view based on the table:

drop view if exists vw_test;
create view vw_test AS
SELECT id, name FROM test;

if i issue

mysql> select count(id) into @tst from vw_test group by id order by id limit 0,1;
Query OK, 1 row affected (0.00 sec)

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

mysql> select count(id)  from vw_test group by id order by id limit 0,1;
+-----------+
| count(id) |
+-----------+
|         2 |
+-----------+
1 row in set (0.01 sec)

mysql>

[20 Dec 2006 11:52] 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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db3
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db3
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.34-debug Source distribution

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

mysql> CREATE TABLE `test` (
    -> `id` INT NOT NULL ,
    -> `name` VARCHAR( 21 ) NOT NULL
    -> ) TYPE = innodb;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO `test` VALUES (1, 'first_rescord');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (1, 'second_record');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (3, 'third_record');
Query OK, 1 row affected (0.00 sec)

mysql> create view vw_test AS
    -> SELECT id, name FROM test;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(id) into @tst from vw_test group by id order by id limit
    -> 0,1;
Query OK, 1 row affected (0.00 sec)

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

mysql>