MySQL Bugs: #79923: Aggr function, UPPER() and REPEAT() give incorrect output

Bug #79923	Aggr function, UPPER() and REPEAT() give incorrect output
Submitted:	12 Jan 2016 3:22	Modified:	12 Jan 2016 7:20
Reporter:	Su Dylan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7.8, 5.6.28, 5.7.10	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Output:
=====
mysql> SELECT min(1) , SUBSTR( REPEAT( SUBSTR( DATE_FORMAT( '2023-07-13 14:50:00', '%j' ) , 2 ) , 8 ) FROM 3 ) without_upper, UPPER( SUBSTR( REPEAT( SUBSTR( DATE_FORMAT( '2023-07-13 14:50:00', '%j' ) , 2 ) , 8 ) FROM 3 )  ) with_upper;
+--------+----------------+----------------+
| min(1) | without_upper  | with_upper     |
+--------+----------------+----------------+
|      1 | 94949494949494 | 94949494949 94 |
+--------+----------------+----------------+
1 row in set (0.00 sec)

mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
=====
"UPPER( SUBSTR( REPEAT( SUBSTR( DATE_FORMAT( '2023-07-13 14:50:00', '%j' ) , 2 ) , 8 ) FROM 3 )  )" is expected to return '94949494949494 '.
The ' '(blank) in the output should not appear.

How to repeat:
SELECT min(1) , SUBSTR( REPEAT( SUBSTR( DATE_FORMAT( '2023-07-13 14:50:00', '%j' ) , 2 ) , 8 ) FROM 3 ) without_upper, UPPER( SUBSTR( REPEAT( SUBSTR( DATE_FORMAT( '2023-07-13 14:50:00', '%j' ) , 2 ) , 8 ) FROM 3 )  ) with_upper;

Suggested fix:
Correct result is returned.

Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.6.28/5.7.10 are affected.

Thanks,
Umesh

-- 5.6.28

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: 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.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> SELECT min(1) , SUBSTR( REPEAT( SUBSTR( DATE_FORMAT( '2023-07-13 14:50:00', '%j' ) , 2 ) , 8 ) FROM 3 ) without_upper, UPPER( SUBSTR( REPEAT( SUBSTR( DATE_FORMAT( '2023-07-13 14:50:00', '%j' ) , 2 ) , 8 ) FROM 3 )  ) with_upper;
+--------+----------------+----------------+
| min(1) | without_upper  | with_upper     |
+--------+----------------+----------------+
|      1 | 94949494949494 | 94949494949 94 |
+--------+----------------+----------------+
1 row in set (0.00 sec)

mysql>

-- 5.7.10

[root@cluster-repo ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> SELECT min(1) , SUBSTR( REPEAT( SUBSTR( DATE_FORMAT( '2023-07-13 14:50:00', '%j' ) , 2 ) , 8 ) FROM 3 ) without_upper, UPPER( SUBSTR( REPEAT( SUBSTR( DATE_FORMAT( '2023-07-13 14:50:00', '%j' ) , 2 ) , 8 ) FROM 3 )  ) with_upper;
+--------+----------------+----------------+
| min(1) | without_upper  | with_upper     |
+--------+----------------+----------------+
|      1 | 94949494949494 | 94949494949 94 |
+--------+----------------+----------------+
1 row in set (0.00 sec)

mysql>

==18786== Source and destination overlap in memcpy(0x158334b0, 0x158334b3, 14)
==18786==    at 0x4C2B403: memcpy@@GLIBC_2.14 (in /usr/lib64/valgrind/vgpreload_memcheck-amd64-linux.so)
==18786==    by 0x134B78B: copy_if_not_alloced(String*, String*, unsigned long) (sql_string.cc:934)
==18786==    by 0x18C04E3: Item_str_conv::val_str(String*) (item_strfunc.cc:1617)
==18786==    by 0x1458E45: Item_copy_string::copy(THD const*) (item.cc:4655)
==18786==    by 0x114E17D: copy_fields(Temp_table_param*, THD const*) (sql_executor.cc:4318)
==18786==    by 0x114AD60: end_send_group(JOIN*, QEP_TAB*, bool) (sql_executor.cc:3081)
==18786==    by 0x1145DA5: do_select(JOIN*) (sql_executor.cc:896)
==18786==    by 0x1144048: JOIN::exec() (sql_executor.cc:214)
==18786==    by 0x11B1E82: handle_query(THD*, LEX*, Query_result*, unsigned long long, unsigned long long) (sql_select.cc:194)
==18786==    by 0x1174D0A: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5104)
==18786==    by 0x116E837: mysql_execute_command(THD*, bool) (sql_parse.cc:2771)
==18786==    by 0x1175B3F: mysql_parse(THD*, Parser_state*) (sql_parse.cc:5511)
==18786==    by 0x116B7CA: dispatch_command(THD*, COM_DATA const*, enum_server_command) (sql_parse.cc:1433)
==18786==    by 0x116A777: do_command(THD*) (sql_parse.cc:1001)
==18786==    by 0x134D24A: handle_connection (connection_handler_per_thread.cc:3

This is a duplicate of
Bug #79695 True where clause doesn't return expected row, but empty set