Description:
Related SQL statements are displayed abnormally in open-source 5.7.42
How to repeat:
drop table if exists t1_2;
create table t1_2 (
`id_col` int not null AUTO_INCREMENT,
`bigint_col` bigint DEFAULT NULL,
`binary_col` binary(11) DEFAULT NULL,
`bit_col` BIT(1) DEFAULT NULL,
`boolean_col` BOOLEAN,
`char_col` char(20),
`date_col` date DEFAULT NULL,
`datetime_col` datetime DEFAULT NULL,
`decimal_col` decimal (25,5) DEFAULT NULL,
`float_col` float DEFAULT NULL,
`int_col` int DEFAULT NULL,
`mediumint_col` mediumint DEFAULT NULL,
`smallint_col` smallint DEFAULT NULL,
`varchar_col` varchar(50) DEFAULT NULL,
`year_col` YEAR(4),
PRIMARY KEY (`id_col`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range(`id_col`)(
partition p0 values less than (6),
partition p1 values less than (11),
partition p2 values less than (16),
partition p3 values less than (21)
);
insert into t1_2 values(1,9000000000,200000,0,TRUE,'aaaaaaaaaa','2023-12-01','2023-12-01 00:00:00',9.99999,-100000.00001,2000000000,8000000,20000,'kongfeiyue@csdn.com',2011),(2,4000000000,180000,0,FALSE,'bbbbbbbbbb','2023-12-02','2023-12-02 00:10:00',99.99999,-900000.00001,-2000000000,1000000,-30000,'qqwqdqdqwdqd@csdn.com',2001),(3,18000000000,60000,0,TRUE,'cccccccccc','2023-12-03','2023-12-03 00:11:00',999.99999,-500000.00001,-1800000000,2000000,-20000,'kongfeiyue@yp889.com',2003),(4,2000000000,80000,0,FALSE,'dddddddddd','2023-12-04','2023-12-04 00:12:00',9999.99999,-700000.00001,-1600000000,-8000000,100,'mengkesbs@cx008.com',1990),(5,6000000000,150000,0,TRUE,'eeeeeeeeee','2023-12-05','2023-12-05 00:13:00',99999.99999,-800000.00001,-1400000000,-2000000,2000,'qfewfqfqwe@shangmen.com',2002),(6,5000000000,30000,0,FALSE,'ffffffffff','2023-12-06','2023-12-06 00:14:00',999999.99999,-600000.00001,-1200000000,-1000000,4000,'kongf342eiyue@wwqsdn.com',2006),(7,11000000000,90000,0,TRUE,'gggggggggg','2023-12-07','2023-12-07 00:15:00',9999999.99999,-200000.00001,-1000000000,5000000,-6000,'kodwqdqe@csd32sn.com',2008),(8,10000000000,40000,0,FALSE,'hhhhhhhhhh','2023-12-08','2023-12-08 00:16:00',99999999.99999,-300000.00001,-800000000,6000000,20000,'ko213eiyue@c3233.com',2012),(9,1000000000,70000,0,TRUE,'iiiiiiiiii','2023-12-09','2023-12-09 00:17:00',999999999.99999,-400000.00001,-600000000,-200000,10000,'9999gfeiyue@c333.com',2016),(10,8000000000,110000,0,FALSE,'jjjjjjjjjj','2023-12-10','2023-12-10 00:18:00',9999999999.99999,-1000000.00001,-400000000,30000,-10000,'ewfqfeiyue@csdn.com',2019),(11,7000000000,100000,1,TRUE,'kkkkkkkkkk','2023-12-11','2023-12-11 00:19:00',99999999999.99999,1000000.00001,-200000000,5600000,-15000,'wumoufanb@csdn.com',2018),(12,17000000000,140000,1,FALSE,'llllllllll','2023-12-12','2023-12-12 00:20:00',999999999999.99999,300000.00001,200000000,-900000,-11000,'qewfqfqqfue@csdn.com',2017),(13,14000000000,130000,1,TRUE,'mmmmmmmmmm','2023-12-13','2023-12-13 00:21:00',9999999999999.99999,200000.00001,400000000,-400000,23000,'kwefqfque@cfqsdn.com',2005),(14,13000000000,120000,1,FALSE,'nnnnnnnnnn','2023-12-14','2023-12-14 00:22:00',99999999999999.99999,800000.00001,600000000,-1200000,-2000,'weqwvqqvv.com',2024),(15,15000000000,50000,1,TRUE,'oooooooooo','2023-12-15','2023-12-15 04:00:00',999999999999999.99999,500000.00001,800000000,300000,-16000,'ewqeqwqcweqdn.com',2021),(16,16000000000,190000,1,FALSE,'pppppppppp','2023-12-16','2023-12-16 05:00:00',9999999999999999.99999,700000.00001,1000000000,600000,-7000,'kwqeqyue@csdn.com',2022),(17,12000000000,170000,1,TRUE,'qqqqqqqqqq','2023-12-17','2023-12-17 06:00:00',99999999999999999.99999,600000.00001,1200000000,7000000,-5000,'kowqeqeqwfyueecsdweqcom',2010),(18,3000000000,20000,1,FALSE,'rrrrrrrrrr','2023-12-18','2023-12-18 07:00:00',999999999999999999.99999,400000.00001,1400000000,-7000000,-25000,'kongwqetr42313csdn323dcom',1996),(19,19000000000,160000,1,TRUE,'ssssssssss','2023-12-19','2023-12-19 08:00:00',9999999999999999999.99999,900000.00001,1600000000,-6000000,13000,'kon123421ewweecsdnfcom',2009),(20,20000000000,10000,1,FALSE,'tttttttttt','2023-12-20','2023-12-20 09:00:00',99999999999999999999.99999,100000.00001,1800000000,60000,30000,'ewqfqwefqefwq',2023);
then,execute the query:
select CEILING(decimal_col) from t1_2 partition(p0,p3) group by year_col;
select FLOOR(decimal_col) from t1_2 partition(p0,p1,p3) group by varchar_col;
Suggested fix:
The excepted result:
mysql> select CEILING(decimal_col) from t1_2 partition(p0,p3) group by year_col;
+-----------------------+
| CEILING(decimal_col) |
+-----------------------+
| 10 |
| 100 |
| 1000 |
| 10000 |
| 100000 |
| 10000000000000000 |
| 100000000000000000 |
| 1000000000000000000 |
| 10000000000000000000 |
| 100000000000000000000 |
+-----------------------+
10 rows in set (0.00 sec)
mysql> select FLOOR(decimal_col) from t1_2 partition(p0,p1,p3) group by varchar_col;
+----------------------+
| FLOOR(decimal_col) |
+----------------------+
| 9 |
| 99 |
| 999 |
| 9999 |
| 99999 |
| 999999 |
| 9999999 |
| 99999999 |
| 999999999 |
| 9999999999 |
| 9999999999999999 |
| 99999999999999999 |
| 999999999999999999 |
| 9999999999999999999 |
| 99999999999999999999 |
+----------------------+
15 rows in set (0.00 sec)
actual result :
mysql> select CEILING(decimal_col) from t1_2 partition(p0,p3) group by year_col;
+----------------------+
| CEILING(decimal_col) |
+----------------------+
| 10000 |
| 9999999999999999 |
| 100 |
| 100000 |
| 1000 |
| 9999999999999999 |
| 9999999999999999 |
| 10 |
| 9999999999999999 |
| 9999999999999999 |
+----------------------+
10 rows in set (0.00 sec)
mysql> select FLOOR(decimal_col) from t1_2 partition(p0,p1,p3) group by varchar_col;
+--------------------+
| FLOOR(decimal_col) |
+--------------------+
| 999999999 |
| 9999999999 |
| 9999999999999999 |
| 99999999 |
| 9999999 |
| 9999999999999999 |
| 999999 |
| 9 |
| 999 |
| 9999999999999999 |
| 9999999999999999 |
| 9999999999999999 |
| 9999 |
| 99999 |
| 99 |
+--------------------+
15 rows in set (0.00 sec)
