Description:
Scientific-notation strings produce different numeric interpretations depending
on the conversion entry point.
On MySQL 8.4.11, '1e5' + 0 and CAST('1e5' AS DECIMAL(10,0)) return 100000 without
warnings. CAST('1e5' AS SIGNED), CAST('1e5' AS UNSIGNED), BIT_COUNT('1e5'), and
'1e5' | 0 each return 1 with warning 1292:
Truncated incorrect INTEGER value: '1e5'
This suggests that these integer-conversion entry points consume only the
integer prefix of the string, while arithmetic and DECIMAL conversion accept
the exponent. The same core result differences were previously observed on
MySQL 8.4.0.
I am reporting this to request clarification of the intended conversion rules.
I have not found an explicit statement requiring all these entry points to
accept the same string syntax, so I am not claiming that the observed behavior
definitively violates the manual.
Relevant MySQL 8.4 documentation:
Section 14.3, Type Conversion in Expression Evaluation:
https://dev.mysql.com/doc/refman/8.4/en/type-conversion.html
Section 14.10, Cast Functions and Operators:
https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html
Section 14.12, Bit Functions and Operators:
https://dev.mysql.com/doc/refman/8.4/en/bit-functions.html
These sections describe string-to-number conversion, the BIGINT result types
of SIGNED/UNSIGNED casts, and integer conversion for numeric bit operations.
However, I could not find an explicit explanation of whether exponent notation
in string input is accepted by these integer-conversion entry points.
Related historical reports:
https://bugs.mysql.com/bug.php?id=6147
This report discusses string-to-integer conversion, including scientific
notation, primarily in assignment scenarios. It also contains CAST examples.
Please advise whether the current CAST/bit-operation behavior is related to
that report or intentionally follows different conversion rules.
https://bugs.mysql.com/bug.php?id=27483
This report concerns large floating-point values converted to BIGINT UNSIGNED
on Windows. The present reproduction uses string input with a small numeric
value on Linux.
Tested environment:
Server: MySQL 8.4.11, MySQL Community Server - GPL
Client: mysql command-line client 8.4.11 for Linux on x86_64
Container image: mysql:8.4.11
Container OS: Oracle Linux Server 9.8
Architecture: amd64
Docker Engine: 29.1.3
Host setup: Docker Desktop on Windows, accessed through WSL2 Ubuntu-22.04
SQL mode:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
The reproduction requires no tables or stored routines and was executed
directly with the mysql command-line client.
How to repeat:
Run the following statements in the mysql command-line client. Run
SHOW WARNINGS immediately after each tested SELECT.
SELECT VERSION(), @@version_comment, @@sql_mode;
SELECT '1e5' + 0;
SHOW WARNINGS;
SELECT CAST('1e5' AS DECIMAL(10,0));
SHOW WARNINGS;
SELECT CAST('1e5' AS SIGNED);
SHOW WARNINGS;
SELECT CAST('1e5' AS UNSIGNED);
SHOW WARNINGS;
SELECT BIT_COUNT('1e5');
SHOW WARNINGS;
SELECT '1e5' | 0;
SHOW WARNINGS;
Observed on MySQL 8.4.11:
Expression Result Warnings
'1e5' + 0 100000 None
CAST('1e5' AS DECIMAL(10,0)) 100000 None
CAST('1e5' AS SIGNED) 1 One warning, code 1292
CAST('1e5' AS UNSIGNED) 1 One warning, code 1292
BIT_COUNT('1e5') 1 One warning, code 1292
'1e5' | 0 1 One warning, code 1292
For each of the last four statements, SHOW WARNINGS returns:
Level: Warning
Code: 1292
Message: Truncated incorrect INTEGER value: '1e5'
Requested clarification:
Is rejecting the exponent in string input intentional for SIGNED/UNSIGNED
casts and numeric bit-operation conversions?
If so, where is the accepted string syntax for those conversions documented?
The arithmetic and DECIMAL results above are comparison cases, not by
themselves proof that integer conversion must return 100000.
Suggested fix:
Please clarify the intended string-to-integer conversion syntax.
If the current behavior is intentional, document that scientific-notation
strings such as '1e5' are truncated at the exponent marker by SIGNED/UNSIGNED
casts and the numeric bit-operation entry points demonstrated above, producing
warning 1292. Include a comparison with arithmetic and DECIMAL conversion.
If these entry points are intended to accept scientific notation in string
input, update the implementation and add regression tests for the cases above.
Description: Scientific-notation strings produce different numeric interpretations depending on the conversion entry point. On MySQL 8.4.11, '1e5' + 0 and CAST('1e5' AS DECIMAL(10,0)) return 100000 without warnings. CAST('1e5' AS SIGNED), CAST('1e5' AS UNSIGNED), BIT_COUNT('1e5'), and '1e5' | 0 each return 1 with warning 1292: Truncated incorrect INTEGER value: '1e5' This suggests that these integer-conversion entry points consume only the integer prefix of the string, while arithmetic and DECIMAL conversion accept the exponent. The same core result differences were previously observed on MySQL 8.4.0. I am reporting this to request clarification of the intended conversion rules. I have not found an explicit statement requiring all these entry points to accept the same string syntax, so I am not claiming that the observed behavior definitively violates the manual. Relevant MySQL 8.4 documentation: Section 14.3, Type Conversion in Expression Evaluation: https://dev.mysql.com/doc/refman/8.4/en/type-conversion.html Section 14.10, Cast Functions and Operators: https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html Section 14.12, Bit Functions and Operators: https://dev.mysql.com/doc/refman/8.4/en/bit-functions.html These sections describe string-to-number conversion, the BIGINT result types of SIGNED/UNSIGNED casts, and integer conversion for numeric bit operations. However, I could not find an explicit explanation of whether exponent notation in string input is accepted by these integer-conversion entry points. Related historical reports: https://bugs.mysql.com/bug.php?id=6147 This report discusses string-to-integer conversion, including scientific notation, primarily in assignment scenarios. It also contains CAST examples. Please advise whether the current CAST/bit-operation behavior is related to that report or intentionally follows different conversion rules. https://bugs.mysql.com/bug.php?id=27483 This report concerns large floating-point values converted to BIGINT UNSIGNED on Windows. The present reproduction uses string input with a small numeric value on Linux. Tested environment: Server: MySQL 8.4.11, MySQL Community Server - GPL Client: mysql command-line client 8.4.11 for Linux on x86_64 Container image: mysql:8.4.11 Container OS: Oracle Linux Server 9.8 Architecture: amd64 Docker Engine: 29.1.3 Host setup: Docker Desktop on Windows, accessed through WSL2 Ubuntu-22.04 SQL mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION The reproduction requires no tables or stored routines and was executed directly with the mysql command-line client. How to repeat: Run the following statements in the mysql command-line client. Run SHOW WARNINGS immediately after each tested SELECT. SELECT VERSION(), @@version_comment, @@sql_mode; SELECT '1e5' + 0; SHOW WARNINGS; SELECT CAST('1e5' AS DECIMAL(10,0)); SHOW WARNINGS; SELECT CAST('1e5' AS SIGNED); SHOW WARNINGS; SELECT CAST('1e5' AS UNSIGNED); SHOW WARNINGS; SELECT BIT_COUNT('1e5'); SHOW WARNINGS; SELECT '1e5' | 0; SHOW WARNINGS; Observed on MySQL 8.4.11: Expression Result Warnings '1e5' + 0 100000 None CAST('1e5' AS DECIMAL(10,0)) 100000 None CAST('1e5' AS SIGNED) 1 One warning, code 1292 CAST('1e5' AS UNSIGNED) 1 One warning, code 1292 BIT_COUNT('1e5') 1 One warning, code 1292 '1e5' | 0 1 One warning, code 1292 For each of the last four statements, SHOW WARNINGS returns: Level: Warning Code: 1292 Message: Truncated incorrect INTEGER value: '1e5' Requested clarification: Is rejecting the exponent in string input intentional for SIGNED/UNSIGNED casts and numeric bit-operation conversions? If so, where is the accepted string syntax for those conversions documented? The arithmetic and DECIMAL results above are comparison cases, not by themselves proof that integer conversion must return 100000. Suggested fix: Please clarify the intended string-to-integer conversion syntax. If the current behavior is intentional, document that scientific-notation strings such as '1e5' are truncated at the exponent marker by SIGNED/UNSIGNED casts and the numeric bit-operation entry points demonstrated above, producing warning 1292. Include a comparison with arithmetic and DECIMAL conversion. If these entry points are intended to accept scientific notation in string input, update the implementation and add regression tests for the cases above.