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: | |
Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
Version: | 8.0.4 | OS: | Windows |
Assigned to: | CPU Architecture: | Any |
[11 Apr 2018 9:25]
man old
[11 Apr 2018 16:40]
Valeriy Kravchuk
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)
[12 Apr 2018 2:23]
man old
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.
[12 Apr 2018 14:27]
Valeriy Kravchuk
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?
[13 Apr 2018 1:36]
man old
Sorry, we have found the reason. This is NOT a BUG. I apolodize.