MySQL Bugs: #19077: Wrong results from VIEWS with BIGINTs and extrema functions

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

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:
-

Verified, preparing a test case for upload now ...

mysqltest test case

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

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?

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 |
+--------+

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

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.

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

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

Fixed in 5.0.22, cset 1.2122.24.1

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.

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)

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)

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)