MySQL Bugs: #91720: Different results when using a subquery based on a view compared to a table

Bug #91720	Different results when using a subquery based on a view compared to a table
Submitted:	19 Jul 2018 16:16	Modified:	20 Aug 2018 8:09
Reporter:	Steve Hanselman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.7.22	OS:	Ubuntu (16.04 LTS)
Assigned to:		CPU Architecture:	Any

Description:
We have a query which performs a subselect/subquery.  When the data source is a table it works correctly, but when the data source is a view (even when the view is "select * from") it fails to produce the correct result.

How to repeat:
CREATE TABLE IF NOT EXISTS `test_table` (
  `a` varchar(50) DEFAULT NULL,
  `b` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

REPLACE INTO `test_table` (`a`, `b`) VALUES
	('123', 50),
	('123', 25),
	('def', 14);

CREATE VIEW `v_test_table` AS select * from `test_table`;

/*** works ***/
select tt.a,

sum(tt.b)/
(select sum(b) from test_table tt_sub where tt_sub.a = tt.a)
as mySum

from test_table tt
where tt.a = '123'
group by tt.a
order by tt.a;

result mysum = 1

/*** does not work ***/
select tt.a,

sum(tt.b)/
(select sum(b) from v_test_table tt_sub where tt_sub.a = tt.a)
as mySum

from v_test_table tt
where tt.a = '123'
group by tt.a
order by tt.a;

Sorry forgot to put that the result from the view is 

mysum = 5.3571

Please provide the server version. Thanks.

Have tried it on various 5.7 variants

Thank you for the feedback. Verified on 5.7, on 8.0 same result for table and view:
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log Source distribution BUILD: 2018-JUL-20

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > USE test
Database changed
mysql 5.7 > CREATE TABLE IF NOT EXISTS `test_table` (
    ->   `a` varchar(50) DEFAULT NULL,
    ->   `b` decimal(10,0) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql 5.7 >
mysql 5.7 > REPLACE INTO `test_table` (`a`, `b`) VALUES
    -> ('123', 50),
    -> ('123', 25),
    -> ('def', 14);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.7 > CREATE VIEW `v_test_table` AS select * from `test_table`;
Query OK, 0 rows affected (0.01 sec)

mysql 5.7 > select tt.a,
    ->
    -> sum(tt.b)/
    -> (select sum(b) from test_table tt_sub where tt_sub.a = tt.a)
    -> as mySum
    ->
    -> from test_table tt
    -> where tt.a = '123'
    -> group by tt.a
    -> order by tt.a;
+------+--------+
| a    | mySum  |
+------+--------+
| 123  | 1.0000 |
+------+--------+
1 row in set (0.00 sec)

mysql 5.7 > select tt.a,
    ->
    -> sum(tt.b)/
    -> (select sum(b) from v_test_table tt_sub where tt_sub.a = tt.a)
    -> as mySum
    ->
    -> from v_test_table tt
    -> where tt.a = '123'
    -> group by tt.a
    -> order by tt.a;
+------+--------+
| a    | mySum  |
+------+--------+
| 123  | 5.3571 |
+------+--------+
1 row in set (0.00 sec)

mysql 5.7 >

*******************************************************************************************************************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.13 Source distribution 2018-JUL-20

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > USE test
Database changed
mysql 8.0 > CREATE TABLE IF NOT EXISTS `test_table` (
    ->   `a` varchar(50) DEFAULT NULL,
    ->   `b` decimal(10,0) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)

mysql 8.0 >
mysql 8.0 > REPLACE INTO `test_table` (`a`, `b`) VALUES
    -> ('123', 50),
    -> ('123', 25),
    -> ('def', 14);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 8.0 > CREATE VIEW `v_test_table` AS select * from `test_table`;
Query OK, 0 rows affected (0.01 sec)

mysql 8.0 > select tt.a,
    ->
    -> sum(tt.b)/
    -> (select sum(b) from test_table tt_sub where tt_sub.a = tt.a)
    -> as mySum
    ->
    -> from test_table tt
    -> where tt.a = '123'
    -> group by tt.a
    -> order by tt.a;
+------+--------+
| a    | mySum  |
+------+--------+
| 123  | 1.0000 |
+------+--------+
1 row in set (0.00 sec)

mysql 8.0 > select tt.a,
    ->
    -> sum(tt.b)/
    -> (select sum(b) from v_test_table tt_sub where tt_sub.a = tt.a)
    -> as mySum
    ->
    -> from v_test_table tt
    -> where tt.a = '123'
    -> group by tt.a
    -> order by tt.a;
+------+--------+
| a    | mySum  |
+------+--------+
| 123  | 1.0000 |
+------+--------+
1 row in set (0.00 sec)

mysql 8.0 >

I'm changing this to S1 as there is no apparent workaround.

Confirmed on version 5.7.21 (windows 7 x64)

Same result, if subquery is multiplied by 1 it is expected result (note group by not needed):

select tt.a,

    (sum(tt.b)) / ((select sum(b) from v_test_table tt_sub where tt_sub.a = tt.a)) as mySum
    ,(sum(tt.b)) / (1*(select sum(b) from v_test_table tt_sub where tt_sub.a = tt.a)) as mySum_2
    ,sum(tt.b) as sum_ttb
    ,(select sum(b) from v_test_table tt_sub where tt_sub.a = tt.a) as subqry
from v_test_table tt
where tt.a = '123'

a	mySum	mySum_2	sum_ttb	subqry
123	5.3571	1.0000	75	75

The group by is needed in the bigger picture.

I'll try some additional maths on the result to see if it can correct the result, also I wonder if there are any optimiser hints that can turn off whatever is causing the issue?

Posted by developer:
 
Workarounds:

1. CREATE ALGORITHM=TEMPTABLE VIEW ...

2. SET optimizer_switch='derived_merge=off';

3. (Only 8.0) SELECT /*+ NO_MERGE(v_test_table) */ ...

Another workaround seems to be to upgrade to 8.0, since the problem appears to be fixed there.