Description:
We noticed two cases where ZEROFILL appears to affect the string representation of a FLOAT column inconsistently when used in expressions.
In the first case, UPPER(c0) returns a zero-padded string for a FLOAT ZEROFILL column, while UPPER(0.4645) returns an unpadded string.
In the second case, CHARACTER_LENGTH((SELECT c0)) returns 12 for a column containing 0, but wrapping the same column in COALESCE(c0, c0) changes the length to 1. Both scalar subqueries return the numeric value 0. Removing ZEROFILL makes both lengths equal to 1.
In these examples, zero padding affects the string-function results depending on how the argument is expressed, even though the numeric value is unchanged.
How to repeat:
--- Case 1: UPPER() with a FLOAT ZEROFILL column.
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 FLOAT ZEROFILL
);
INSERT INTO t0 (c0) VALUES (0.4645);
SELECT
t0.c0 AS r0,
UPPER(0.4645) AS r1,
UPPER(t0.c0) AS r2
FROM t0;
--- Actual result:
--- r0 r1 r2
--- 0.4645 0.4645 0000000.4645
--- Expected: r2 should return an unpadded representation, '0.4645', if ZEROFILL is ignored in this expression.
--- Case 2: CHARACTER_LENGTH() with direct and COALESCE-wrapped scalar-subquery results.
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 FLOAT ZEROFILL
);
INSERT INTO t0 (c0) VALUES (0);
SELECT
t0.c0 AS ref0,
(SELECT COALESCE(t0.c0, t0.c0)) AS ref1,
(SELECT t0.c0) AS ref2,
CHARACTER_LENGTH(
(SELECT COALESCE(t0.c0, t0.c0))
) AS ref3,
CHARACTER_LENGTH(
(SELECT t0.c0)
) AS ref4
FROM t0;
--- Actual result:
--- ref0 ref1 ref2 ref3 ref4
--- 0 0 0 1 12
--- Expected result if ZEROFILL is ignored in these expressions:
--- ref0 ref1 ref2 ref3 ref4
--- 0 0 0 1 1
Description: We noticed two cases where ZEROFILL appears to affect the string representation of a FLOAT column inconsistently when used in expressions. In the first case, UPPER(c0) returns a zero-padded string for a FLOAT ZEROFILL column, while UPPER(0.4645) returns an unpadded string. In the second case, CHARACTER_LENGTH((SELECT c0)) returns 12 for a column containing 0, but wrapping the same column in COALESCE(c0, c0) changes the length to 1. Both scalar subqueries return the numeric value 0. Removing ZEROFILL makes both lengths equal to 1. In these examples, zero padding affects the string-function results depending on how the argument is expressed, even though the numeric value is unchanged. How to repeat: --- Case 1: UPPER() with a FLOAT ZEROFILL column. DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 FLOAT ZEROFILL ); INSERT INTO t0 (c0) VALUES (0.4645); SELECT t0.c0 AS r0, UPPER(0.4645) AS r1, UPPER(t0.c0) AS r2 FROM t0; --- Actual result: --- r0 r1 r2 --- 0.4645 0.4645 0000000.4645 --- Expected: r2 should return an unpadded representation, '0.4645', if ZEROFILL is ignored in this expression. --- Case 2: CHARACTER_LENGTH() with direct and COALESCE-wrapped scalar-subquery results. DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 FLOAT ZEROFILL ); INSERT INTO t0 (c0) VALUES (0); SELECT t0.c0 AS ref0, (SELECT COALESCE(t0.c0, t0.c0)) AS ref1, (SELECT t0.c0) AS ref2, CHARACTER_LENGTH( (SELECT COALESCE(t0.c0, t0.c0)) ) AS ref3, CHARACTER_LENGTH( (SELECT t0.c0) ) AS ref4 FROM t0; --- Actual result: --- ref0 ref1 ref2 ref3 ref4 --- 0 0 0 1 12 --- Expected result if ZEROFILL is ignored in these expressions: --- ref0 ref1 ref2 ref3 ref4 --- 0 0 0 1 1