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 18:32	Modified:	26 May 2006 19:06
Reporter:	Beat Vontobel (Silver Quality Contributor)
Status:	Closed
Category:	Server: Views	Severity:	S2 (Serious)
Version:	5.0.20	OS:	Any (any)
Assigned to:	Evgeny Potemkin	Target Version:

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.