Bug #88590 max(id) from multiple tables returns unexpected result
Submitted: 21 Nov 2017 12:06 Modified: 22 Nov 2017 11:20
Reporter: Darren Shukri Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.20 OS:Ubuntu (17.10)
Assigned to: CPU Architecture:Any
Tags: aggregation, functions, MAX, SQL

[21 Nov 2017 12:06] Darren Shukri
Description:
The max function gives unexpected results when retrieving the max() value for a column from multiple tables.

Given the simplest of tables foo, bar and baz created as per the instructions under "How to repeat":

select max(id) from foo; /* returns 3 */
select max(id) from bar; /* returns 5 */
select max(id) from baz; /* returns null */
select max(foo.id), max(bar.id) from foo, bar; /* returns 3, 5 */
select max(bar.id), max(foo.id) from foo, bar; /* returns 5, 3 */

select max(foo.id), max(bar.id), max(baz.id) from foo, bar, baz; /* expected 3, 5, null */

The above actually returns null, null, null which is unexpected as only max(baz.id) returns null.  It seems that retrieving any combination of max(baz.id) and max(X.id) (where X is foo or bar) results in ALL values returned by max() becoming null

How to repeat:
/* BEGIN CREATE AND POPULATE TEST CASE TABLES*/
create table foo (id smallint unsigned);

create table bar like foo;

create table baz like foo;

insert into foo values (1), (2), (3);

insert into bar select * from foo;

insert into bar values (4), (5);
/* END CREATE AND POPULATE TEST CASE TABLES */

-- UNEXPECTED/INCORRECT RESULTS FROM THE FOLLOWING
select max(foo.id), max(baz.id) from foo, baz; /* returns null, null */
select max(bar.id), max(baz.id) from bar, baz; /* returns null, null */
select max(foo.id), max(bar.id), max(baz.id) from foo, bar, baz; /* returns null, null, null */

-- ORDER DOES NOT SEEM TO MATTER AS THE FOLLOWING PRODUCE THE SAME RESULTS
select max(baz.id), max(foo.id) from baz, foo; /* returns null, null */
select max(baz.id), max(bar.id) from baz, bar; /* returns null, null */
select max(foo.id), max(baz.id), max(bar.id) from foo, bar, baz; /* returns null, null, null */
select max(bar.id), max(foo.id), max(baz.id) from foo, bar, baz; /* returns null, null, null */
select max(baz.id), max(bar.id), max(foo.id) from foo, bar, baz; /* returns null, null, null */
[21 Nov 2017 20:06] MySQL Verification Team
Thank you for the bug report. It's expected result that comma separated table query it behaves like an inner join, you could check that Oracle 12c and Microsoft SQL Server give the same result tan MySQL:

mysql 5.7 > create table a (a int);
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 > create table b (b int);
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 > insert into a values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.7 > select max(a), max(b)
    -> from a inner join b
    -> on a= b;
+--------+--------+
| max(a) | max(b) |
+--------+--------+
|   NULL |   NULL |
+--------+--------+
1 row in set (0.00 sec)

mysql 5.7 > create table foo (id smallint unsigned);
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 >
mysql 5.7 > create table bar like foo;
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 >
mysql 5.7 > create table baz like foo;
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 >
mysql 5.7 > insert into foo values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > insert into bar select * from foo;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.7 > select max(foo.id), max(bar.id), max(baz.id) from foo, bar, baz;
+-------------+-------------+-------------+
| max(foo.id) | max(bar.id) | max(baz.id) |
+-------------+-------------+-------------+
|        NULL |        NULL |        NULL |
+-------------+-------------+-------------+
1 row in set (0.00 sec)

C:\dbs>sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 21 18:00:16 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: system
Enter password:
Last Successful login time: Tue Nov 21 2017 17:58:14 -02:00

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> select * from foo;

        ID
----------
         1
         2
         3
         4
         5

SQL> select * from bar;

        ID
----------
         1
         2
         3

SQL> select * from baz;

no rows selected

SQL> select max(foo.id), max(bar.id), max(baz.id) from foo, bar, baz;

MAX(FOO.ID) MAX(BAR.ID) MAX(BAZ.ID)
----------- ----------- -----------

SQL>
[22 Nov 2017 11:20] Darren Shukri
Thanks for the quick response Miguel.

I admit that my expectation of what should be returned by "select max(baz.id), max(X.id) from baz, X" was incorrect.

In your response it is stated that the expected result is "that comma separated table query it behaves like an inner join".  From the MySQL 5.7 docs page on join syntax:

"INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table)."

This is confirmed by the result of the following simple query:

select foo.id, bar.id from foo, bar; /* returns 15 rows count(foo.id)*count(bar.id) */

It also explains why "select max(foo.id), max(bar.id) from foo, bar" returns the correct values (3, 5) for max(id) from each table rather than 3 for both as it would for an inner join on id (the only column each table possesses).

Following that same logic the expectation would then be that the number of rows returned by a Cartesian product of the tables foo, bar and baz would be 0 (count(foo.*)*count(bar.*)*count(baz.*)) and this is confirmed as...

select foo.id, bar.id, baz.id from foo, bar, baz;

...returns no rows.

And yet...

select max(foo.id), max(bar.id), max(baz.id) from foo, bar, baz;

...returns 1 row of three nulls.  I see, now, that Mysql is determining the Cartesian product (0 rows) and then applying the max function to each column of those 0 rows (returning null in each case).

I hope the above explanation helps others that may be surprised by MySQL's behaviour in this instance.

However your response also states that Oracle 12c does the same thing and then seems to show that it returns no rows.  This is not the same thing as returning a row with null for each column value.  Is this then a bug in Oracle 12c?
[22 Nov 2017 11:26] MySQL Verification Team
Thank you for the feedback. The Oracle's result is the same as MySQL just happens that Oracles uses an empty result instead of NULL, on another hand I commented SQL Server behaves as MySQL on this case you will get the NULL value printed.
[22 Nov 2017 11:45] MySQL Verification Team
## 

-bash-4.2$ psql
psql (9.4.0)
Type "help" for help.
postgres=# create table foo (id smallint);
CREATE TABLE
postgres=# create table bar (id smallint);
CREATE TABLE
postgres=# create table baz (id smallint);
CREATE TABLE
postgres=# insert into foo values (1), (2), (3);
INSERT 0 3
postgres=# insert into bar select * from foo;
INSERT 0 3
postgres=# insert into bar values (4), (5);
INSERT 0 2
postgres=# select * from foo;
 id 
----
  1
  2
  3
(3 rows)

postgres=# select * from bar;
 id 
----
  1
  2
  3
  4
  5
(5 rows)

                                                               
postgres=# select max(foo.id), max(bar.id), max(baz.id) from foo, bar, baz;
 max | max | max 
-----+-----+-----
     |     |    
(1 row)