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