Bug #29918 Assigning a variable in some SELECT queries yields invalid resuls

• Submitted: 19 Jul 2007 22:24
• Modified: 19 Jul 2007 23:37
• Reporter: Paweł Stradomski
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.44
• OS: Linux (Gentoo)
• CPU Architecture: Any
• Tags: COUNT DISTINCT, GROUP BY, join, user variables

[19 Jul 2007 22:24] Paweł Stradomski

Description:
If a variable is assigned in select query with join, group by and count distinct then results are spoiled.

This is related to bug 28494

How to repeat:
CREATE TABLE `products` (
`name` VARCHAR( 10 ) NOT NULL ,
`price` INT NOT NULL ,
PRIMARY KEY ( `name` )
);

INSERT INTO products VALUES ('tea', 7), ('coffie', 14), ('sugar', 3);

CREATE TABLE `p2` (
`name` VARCHAR( 10 ) NOT NULL ,
`price` INT NOT NULL ,
PRIMARY KEY ( `name` )
);

INSERT INTO p2 VALUES ('tea', 7), ('coffie', 14), ('sugar', 3);

SELECT
    @a := CASE
        WHEN products.price BETWEEN 0 AND  9 THEN  '0-9'
        WHEN products.price BETWEEN 10 AND  19 THEN  '10-19'
    END
        AS priceInterval,
   COUNT(DISTINCT products.name) AS productCount,
   CAST(SUBSTRING_INDEX(@a, '-', 1) AS SIGNED) AS priceIntervalStart
FROM
    products LEFT JOIN p2 ON products.name = p2.name
GROUP BY priceInterval
;

Expected result is:
+---------------+--------------+--------------------+
| priceInterval | productCount | priceIntervalStart |
+---------------+--------------+--------------------+
| 0-9           |            2 |                  0 |
| 10-19         |            1 |                 10 |
+---------------+--------------+--------------------+

Actual result is:
+---------------+--------------+--------------------+
| priceInterval | productCount | priceIntervalStart |
+---------------+--------------+--------------------+
| 0-9           |            3 |                  0 |
+---------------+--------------+--------------------+

Suggested fix:
Mysql should handle this kind of queries properly.

[19 Jul 2007 22:44] Paul DuBois

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html says:

"The order of evaluation for user variables is undefined and may change based on the elements contained within a given query. In SELECT @a, @a := @a+1 ..., you might think that MySQL will evaluate @a first and then do an assignment second, but changing the query (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may change the order of evaluation."

[19 Jul 2007 23:37] MySQL Verification Team

Thank you for the bug report. Please Paul's comment.

[miguel@light 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.48-debug Source distribution

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

mysql> use test
Database changed
mysql> CREATE TABLE `products` (
    -> `name` VARCHAR( 10 ) NOT NULL ,
    -> `price` INT NOT NULL ,
    -> PRIMARY KEY ( `name` )
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> INSERT INTO products VALUES ('tea', 7), ('coffie', 14), ('sugar', 3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> CREATE TABLE `p2` (
    -> `name` VARCHAR( 10 ) NOT NULL ,
    -> `price` INT NOT NULL ,
    -> PRIMARY KEY ( `name` )
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> INSERT INTO p2 VALUES ('tea', 7), ('coffie', 14), ('sugar', 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> 
mysql> SELECT
    ->     @a := CASE
    ->         WHEN products.price BETWEEN 0 AND  9 THEN  '0-9'
    ->         WHEN products.price BETWEEN 10 AND  19 THEN  '10-19'
    ->     END
    ->         AS priceInterval,
    ->    COUNT(DISTINCT products.name) AS productCount,
    ->    CAST(SUBSTRING_INDEX(@a, '-', 1) AS SIGNED) AS priceIntervalStart
    -> FROM
    ->     products LEFT JOIN p2 ON products.name = p2.name
    -> GROUP BY priceInterval
    -> ;
+---------------+--------------+--------------------+
| priceInterval | productCount | priceIntervalStart |
+---------------+--------------+--------------------+
| 0-9           |            2 |                  0 | 
| 10-19         |            1 |                 10 | 
+---------------+--------------+--------------------+
2 rows in set (0.04 sec)

mysql>

[27 Jul 2007 13:10] Piotr Czachur

It is a bug, I have already filled it (http://bugs.mysql.com/bug.php?id=28494), and it was fixed, so i guess it appeared again. Manual says that values returned by variable can be unpredictable, but result rows count shouldn't change because some value was assigned to a variable.