MySQL Bugs: #90382: Select Insert in a procedure, the aggregate value can't be divided

Bug #90382	Select Insert in a procedure, the aggregate value can't be divided
Submitted:	11 Apr 2018 9:25	Modified:	13 Apr 2018 5:05
Reporter:	man old	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	8.0.4	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Within a procedure, using INSERT ... SELECT..., when an aggregate value is divided, it returns ERROR 1046.

How to repeat:
create table t1(
  a int not null, 
  num1 int, 
  num2 int
);

create table t2(
  b int not null primary key, 
  sum1 int, 
  sum2 int, 
  div1 decimal(10,2), 
  div2 decimal(10,2)
);

insert into t1 values (1, 10, 15);
insert into t1 values (2, 20, 15);

CREATE PROCEDURE proc1()
BEGIN
  insert into t2 
    select a, sum(num1), sum(num2), sum(num1) /10.0, sum(num2) /10.0 from t1 group by a;
    -- select a, sum(num1), sum(num2), sum(num1) / sum(num2), sum(num2) /sum(1) from t1 group by a;
  commit;
END;

It returns Error 1046.

Works as expected with older MySQL 5.6.x for me:

mysql> create table t1(
    ->   a int not null,
    ->   num1 int,
    ->   num2 int
    -> );
Query OK, 0 rows affected (0.91 sec)

mysql> create table t2(
    ->   b int not null primary key,
    ->   sum1 int,
    ->   sum2 int,
    ->   div1 decimal(10,2),
    ->   div2 decimal(10,2)
    -> );
Query OK, 0 rows affected (0.84 sec)

mysql> insert into t1 values (1, 10, 15);
Query OK, 1 row affected (0.13 sec)

mysql> insert into t1 values (2, 20, 15);
Query OK, 1 row affected (0.10 sec)

mysql> delimiter //
mysql> select a, sum(num1), sum(num2), sum(num1) / sum(num2), sum(num2) /sum(1)
from t1 group by a//
+---+-----------+-----------+-----------------------+-------------------+
| a | sum(num1) | sum(num2) | sum(num1) / sum(num2) | sum(num2) /sum(1) |
+---+-----------+-----------+-----------------------+-------------------+
| 1 |        10 |        15 |                0.6667 |           15.0000 |
| 2 |        20 |        15 |                1.3333 |           15.0000 |
+---+-----------+-----------+-----------------------+-------------------+
2 rows in set (0.08 sec)

mysql> CREATE PROCEDURE proc1()
    -> BEGIN
    ->   insert into t2
    ->   select a, sum(num1), sum(num2), sum(num1) / sum(num2), sum(num2) /sum(1
) from t1 group by a;
    -> END;
    -> //
Query OK, 0 rows affected (0.10 sec)

mysql> call proc1()//
Query OK, 2 rows affected, 2 warnings (0.13 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1265
Message: Data truncated for column 'div1' at row 3
*************************** 2. row ***************************
  Level: Note
   Code: 1265
Message: Data truncated for column 'div1' at row 4
2 rows in set (0.00 sec)

mysql> select * from t2//
+---+------+------+------+-------+
| b | sum1 | sum2 | div1 | div2  |
+---+------+------+------+-------+
| 1 |   10 |   15 | 0.67 | 15.00 |
| 2 |   20 |   15 | 1.33 | 15.00 |
+---+------+------+------+-------+
2 rows in set (0.00 sec)

mysql> select version()//
+-----------+
| version() |
+-----------+
| 5.6.29    |
+-----------+
1 row in set (0.02 sec)

Please try it on Version 8.0.4!
My scripts WORKS FINE on old versions(5,6,x. 5.7.x), but CAN'T WORK on 8.0.4, I took whole afteroon to find out it.
There are several additional infors:
1) Try it within a procedure, it works fine in mysql shell.
2) Aggregate functions, sum(), avg(), count(), etc, have same ERROR.
3) Other operators( +, -, *, div, mod) EXCEPT "/" works fine.
4) My platform is Windows only, but I think it is same in Linux.

I tried (this time on Linux):

[openxs@fc23 8.0]$ bin/mysql -uroot --socket=/tmp/mysql.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.4-rc MySQL Community Server (GPL)

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> drop table t1, t2;
ERROR 1051 (42S02): Unknown table 'test.t1,test.t2'
mysql> drop procedure proc1;
ERROR 1305 (42000): PROCEDURE test.proc1 does not exist
mysql> create table t1(

create table t2(
  b int not null primary key,
    ->   a int not null,
  sum1 int,
  sum2 int,
  div1 decimal(10,2),
  div2 decimal(10,2)
);
    ->   num1 int,
insert into t1 values (2, 20, 15);
    ->   num2 int
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql>
mysql> create table t2(
    ->   b int not null primary key,
    ->   sum1 int,
    ->   sum2 int,
    ->   div1 decimal(10,2),
    ->   div2 decimal(10,2)
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql>
mysql> insert into t1 values (1, 10, 15);
Query OK, 1 row affected (0.06 sec)

mysql> insert into t1 values (2, 20, 15);
Query OK, 1 row affected (0.04 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE proc1()
    -> BEGIN
    ->   insert into t2
    ->   select a, sum(num1), sum(num2), sum(num1) / sum(num2), sum(num2) /sum(1) from t1 group by a;
    -> END;
    -> //
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter ;
mysql> select a, sum(num1), sum(num2), sum(num1) / sum(num2), sum(num2) /sum(1) from t1 group by a;
+---+-----------+-----------+-----------------------+-------------------+
| a | sum(num1) | sum(num2) | sum(num1) / sum(num2) | sum(num2) /sum(1) |
+---+-----------+-----------+-----------------------+-------------------+
| 1 |        10 |        15 |                0.6667 |           15.0000 |
| 2 |        20 |        15 |                1.3333 |           15.0000 |
+---+-----------+-----------+-----------------------+-------------------+
2 rows in set (0.00 sec)

mysql> call proc1();
Query OK, 2 rows affected, 2 warnings (0.04 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1265
Message: Data truncated for column 'div1' at row 3
*************************** 2. row ***************************
  Level: Note
   Code: 1265
Message: Data truncated for column 'div1' at row 4
2 rows in set (0.00 sec)

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

What am I doing wrong in this test?

Sorry, we have found the reason. This is NOT a BUG.
I apolodize.