| Bug #81513 | Incorrect result of ROUND with subquery | ||
|---|---|---|---|
| Submitted: | 19 May 2016 19:39 | Modified: | 23 May 2016 7:15 |
| Reporter: | Power Gamer | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7.11, 5.7.12, 5.6.30 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[19 May 2016 19:39]
Power Gamer
[20 May 2016 5:23]
MySQL Verification Team
Hello!! Thank you for the report and test case. Observed that 5.6.30/5.7.12 release builds are affected. Thanks, Umesh
[20 May 2016 5:24]
MySQL Verification Team
-- 4.1.25->5.5.49 not affected -- 4.1.25 [umshastr@hod03]/export/umesh/server/binaries/mysql-4.1.25: bin/mysql -uroot -S/tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.25-classic Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database if not exists test; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> use test; Database changed mysql> create table t1(id int); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec) mysql> SELECT ROUND(1.23456, 2); -- OK: 1.23 +-------------------+ | ROUND(1.23456, 2) | +-------------------+ | 1.23 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2')); -- OK: 1.23 +------------------------------+ | ROUND(1.23456, (SELECT '2')) | +------------------------------+ | 1.23 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)); -- WRONG: 1.23000 +----------------------------------------------+ | ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)) | +----------------------------------------------+ | 1.23 | +----------------------------------------------+ 1 row in set (0.01 sec) -- 5.0.96 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.0.96: bin/mysql -uroot -S/tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.96 MySQL Community Server (GPL) Copyright (c) 2000, 2011, 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> create database if not exists test; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> use test; Database changed mysql> create table t1(id int); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec) mysql> SELECT ROUND(1.23456, 2); -- OK: 1.23 +-------------------+ | ROUND(1.23456, 2) | +-------------------+ | 1.23 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2')); -- OK: 1.23 +------------------------------+ | ROUND(1.23456, (SELECT '2')) | +------------------------------+ | 1.23 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)); -- WRONG: 1.23000 +----------------------------------------------+ | ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)) | +----------------------------------------------+ | 1.23 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> -- 5.1.77 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.1.77: bin/mysql -uroot -S/tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.77 Source distribution Copyright (c) 2000, 2013, 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> create database if not exists test; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> use test; Database changed mysql> create table t1(id int); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec) mysql> SELECT ROUND(1.23456, 2); -- OK: 1.23 +-------------------+ | ROUND(1.23456, 2) | +-------------------+ | 1.23 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2')); -- OK: 1.23 +------------------------------+ | ROUND(1.23456, (SELECT '2')) | +------------------------------+ | 1.23 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)); -- WRONG: 1.23000 +----------------------------------------------+ | ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)) | +----------------------------------------------+ | 1.23 | +----------------------------------------------+ 1 row in set (0.01 sec) mysql> -- 5.5.49 [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.5.49: bin/mysql -uroot -S/tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.49-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, 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> create database if not exists test; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> use test; Database changed mysql> drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table t1(id int); insert into t1 values(1); SELECT ROUND(1.23456, 2); -- OK: 1.23 SELECT ROUND(1.23456, (SELECT '2')); -- OK: 1.23 SELECT ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)); -- WRONG: 1.23000Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec) mysql> SELECT ROUND(1.23456, 2); -- OK: 1.23 +-------------------+ | ROUND(1.23456, 2) | +-------------------+ | 1.23 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2')); -- OK: 1.23 +------------------------------+ | ROUND(1.23456, (SELECT '2')) | +------------------------------+ | 1.23 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)); -- WRONG: 1.23000 +----------------------------------------------+ | ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)) | +----------------------------------------------+ | 1.23 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql>
[20 May 2016 5:25]
MySQL Verification Team
-- 5.6.30/5.7.12 affected [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.30: bin/mysql -uroot -S/tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.30-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, 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> mysql> create database if not exists test; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> use test; Database changed mysql> drop table if exists t1; insert into t1 values(1); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table t1(id int); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec) mysql> SELECT ROUND(1.23456, 2); -- OK: 1.23 +-------------------+ | ROUND(1.23456, 2) | +-------------------+ | 1.23 | +-------------------+ 1 row in set (0.01 sec) mysql> SELECT ROUND(1.23456, (SELECT '2')); -- OK: 1.23 +------------------------------+ | ROUND(1.23456, (SELECT '2')) | +------------------------------+ | 1.23 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)); -- WRONG: 1.23000 +----------------------------------------------+ | ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)) | +----------------------------------------------+ | 1.23000 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> -- 5.7.12 [root@cluster-repo ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 59 Server version: 5.7.12 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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> create database if not exists test; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> drop table if exists t1; Query OK, 0 rows affected (0.00 sec) mysql> create table t1(id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec) mysql> SELECT ROUND(1.23456, 2); -- OK: 1.23 +-------------------+ | ROUND(1.23456, 2) | +-------------------+ | 1.23 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2')); -- OK: 1.23 +------------------------------+ | ROUND(1.23456, (SELECT '2')) | +------------------------------+ | 1.23 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)); -- WRONG: 1.23000 +----------------------------------------------+ | ROUND(1.23456, (SELECT '2' FROM t1 LIMIT 1)) | +----------------------------------------------+ | 1.23000 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql>
[23 May 2016 7:15]
Roy Lyseng
This is not a bug: MySQL determines the data type of the result column during resolving of the query, which is a process that takes place before reading data from tables. In the first two examples, the round argument is constant, so it can be used to determine the data type of the result of the ROUND operation. However, in the third example, we must read data from 'sometable' to determine the argument to ROUND, so MySQL chooses some arbitrary value for the number of decimals (in this case 5), since this value is not available when resolving the query.
