Bug #19077 Wrong results from VIEWS with BIGINTs and extrema functions
Submitted: 13 Apr 2006 16:32 Modified: 5 Sep 2012 16:33
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.20 OS:Any (any)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[13 Apr 2006 16:32] Beat Vontobel
Description:
Many VIEWs with BIGINTs and extrema functions (MIN/MAX) return very strange and wrong results in different situations.

This is especially bad as MySQL often seems to use BIGINT implicitly in result columns for any integer types in VIEWs. The consequence is that you're very likely to hit this bug if you just build any nested construct of VIEWs (as integer types and extrema functions are pretty common).

How to repeat:
CREATE TABLE t (i INT, j BIGINT);

INSERT INTO t VALUES (1, 2), (2, 2), (3, 2);

CREATE VIEW min_tj AS SELECT MIN(j) AS j FROM t;

CREATE OR REPLACE VIEW v AS
    SELECT MIN(i)
    FROM t
    WHERE j = ( SELECT * FROM min_tj );

Now execute the following query that's expected to return 1:

SELECT * FROM v;
+--------+
| MIN(i) |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

min_tj gives the right result:

SELECT * FROM min_tj;
+------+
| j    |
+------+
| 2    |
+------+
1 row in set (0.00 sec)

Now change the column type for j from BIGINT to INT:

ALTER TABLE t CHANGE COLUMN j j INT;

SELECT * FROM v;
+--------+
| MIN(i) |
+--------+
| 1      |
+--------+
1 row in set (0.00 sec)

That's what we would expect for t.j with BIGINT as well.

Suggested fix:
-
[13 Apr 2006 17:32] Hartmut Holzgraefe
Verified, preparing a test case for upload now ...
[13 Apr 2006 17:51] Hartmut Holzgraefe
mysqltest test case

Attachment: bug19077.tar.gz (application/x-gunzip, text), 729 bytes.

[13 Apr 2006 18:12] Beat Vontobel
Hi Hartmut, my problem is, I've maybe just hit a whole bunch of VIEW bugs at once.

I tried to code a little application with some nested VIEWs. From about the fourth VIEW level it was complete mayhem. Not one single query returned correct results. This one was the first bug I could isolate somehow. But there seem to be many more.

The tough thing is, that they usually don't show up at the level they originate: This one for example originally went through four levels, but all intermediate levels returned correct results. The bugs could be related or not (difficult to tell from my point of view) - should I still try to isolate them as good as possible (it's at the edge of my time capacity) and maybe even risk some duplicates (even if they show very different behaviour, they could originate at a similar place - it seems like there might be some data type issues in VIEWs for example), or would it be okay, if I just posted all my code at once?
[14 May 2006 15:52] Igor Babaev
The same problem can be easily demonstrated without any views:

mysql> select * from (select min(i) from t where j=(select * from (select min(j) from t) t1)) t2;
+--------+
| min(i) |
+--------+
|   NULL |
+--------+
[17 May 2006 20:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6527
[17 May 2006 20:54] Evgeny Potemkin
The convert_constant_item() function converts constant items to ints on
prepare phase to optimize execution speed. In this case it tries to evaluate
subselect which contains a derived table and is contained in a derived table. 
All derived tables are filled only after all derived tables are prepared.
So evaluation of subselect with derived table at the prepare phase will
return a wrong result.
[17 May 2006 20:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6530
[17 May 2006 21:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6534
[18 May 2006 13:31] Evgeny Potemkin
Fixed in 5.0.22, cset 1.2122.24.1
[26 May 2006 17:06] Paul DuBois
Noted in 5.0.23, 5.1.11 changelogs.

Premature optimization of nested subqueries in the FROM clause that
refer to aggregate functions could lead to incorrect results.
[17 Dec 2010 12:48] Bugs System
Pushed into mysql-5.1 5.1.55 (revid:georgi.kodinov@oracle.com-20101217124435-9imm43geck5u55qw) (version source revid:sergey.glukhov@oracle.com-20101214093303-wmo9mqcb8rz0wv9f) (merge vers: 5.1.55) (pib:24)
[17 Dec 2010 12:51] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:sergey.glukhov@oracle.com-20101214104600-v0ndu721rf61nbml) (merge vers: 5.5.8) (pib:24)
[17 Dec 2010 12:55] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:sergey.glukhov@oracle.com-20101214111513-9j68fg7s48a986ng) (merge vers: 5.6.1) (pib:24)