Bug #54056 Incorrect number format in output from SELECT

• Submitted: 28 May 2010 6:43
• Modified: 28 May 2010 8:34
• Reporter: Kristian Nielsen
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.1.47, 5.1.48, 5.6.99
• OS: Any
• CPU Architecture: Any

[28 May 2010 6:43] Kristian Nielsen

Description:
I commented on this in bug#47904, but it seems to have been missed so I though
I better make a proper bug report.

In MySQL 5.1.47, in the testcase main.subselect, the result file contains
(among other things) this:

SELECT
t1.t1_id,
IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount
FROM
t1
LEFT JOIN t2 ON t2.t1_id=t1.t1_id
GROUP BY
t1.t1_id
;
t1_id	total_amount
1	100.00
2	200.00
3	0.00
4	400.00
5	0.00

SELECT
t1.t1_id,
IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount
FROM
t1
LEFT JOIN t2 ON t2.t1_id=t1.t1_id
GROUP BY
t1.t1_id;
t1_id	total_amount
1	100.00
2	200.00
3	0
4	400.00
5	0

Note that this is the exact same query executed twice, but the output is different! In the second case, the zeros are output as "0", not "0.00".
The second case is a bug, it seems to be setting the wrong result type
for IFNULL(..., 0), int instead of decimal or something.

It seems the only difference between the two cases is the presence or absense of a primary key on the table, this should not influence the output of course.

How to repeat:
Run the test case main.subselect.

It passes, but observe the above incorrect data in the result file
mysql-test/r/subselect.result.

[28 May 2010 8:34] Sveta Smirnova

Thank you for the report.

Verified as described.

Problem causes ALTER TABLE t2 ADD PRIMARY KEY(t1_id); statement

Reduced test:

CREATE TABLE t1 (
        t1_id INT UNSIGNED,

        PRIMARY KEY(t1_id)
) Engine=MyISAM;

INSERT INTO t1 (t1_id) VALUES (1), (2), (3), (4), (5);

CREATE TABLE t2 SELECT * FROM t1;

CREATE TABLE t3 (
        t3_id INT UNSIGNED AUTO_INCREMENT,
        t1_id INT UNSIGNED,
        amount DECIMAL(16,2),

        PRIMARY KEY(t3_id),
        KEY(t1_id)
) Engine=MyISAM;

INSERT INTO t3 (t1_id, t3_id, amount)
        VALUES (1, 1, 100.00), (2, 2, 200.00), (4, 4, 400.00);

SELECT
        t1.t1_id,
        IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount
FROM
        t1
        LEFT JOIN t2 ON t2.t1_id=t1.t1_id
GROUP BY
        t1.t1_id
;

ALTER TABLE t2 ADD PRIMARY KEY(t1_id);
SELECT
        t1.t1_id,
        IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount
FROM
        t1
        LEFT JOIN t2 ON t2.t1_id=t1.t1_id
GROUP BY
        t1.t1_id
;