Description:
REVERSE() returns different results for two correlated scalar subqueries that select the same FLOAT column value.
The first subquery uses WHERE 1, while the second uses WHERE LENGTH(CONCAT(COALESCE(t1.c0, ''), 'x')) > 0.
The second predicate is true for every row in the test case, as confirmed by the ref3 column, so I would expect both expressions to produce the same result.
For example, for the row displaying c1 as 0.614148, the first expression returns '841416.0', while the second returns '732344702084'. The discrepancy occurs for all four rows.
How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
c0 BIGINT,
c1 FLOAT
);
INSERT INTO t1 (c0, c1) VALUES
(NULL, 0.614148),
(NULL, 0.810261),
(0, 0.219862),
(95494781, 1083180000);
SELECT
t1.c1 AS ref0,
REVERSE((SELECT t1.c1 WHERE 1)) AS ref1,
REVERSE((
SELECT t1.c1
WHERE LENGTH(CONCAT(COALESCE(t1.c0, ''), 'x')) > 0
)) AS ref2,
LENGTH(CONCAT(COALESCE(t1.c0, ''), 'x')) > 0 AS ref3
FROM t1;
Actual result:
+------------------+------------------+----------------------+------+
| ref0 | ref1 | ref2 | ref3|
+------------------+------------------+----------------------+------+
| 0.614148 | 841416.0 | 732344702084 | 1 |
| 0.810261 | 162018.0 | 275632111016 | 1 |
| 0.219862 | 268912.0 | 880904519991 | 1 |
| 1083180000 | 0000813801 | 2300813801 | 1 |
+------------------+-------------------+---------------------+------+
Expected result:
+------------------+------------------+----------------------+------+
| ref0 | ref1 | ref2 | ref3|
+------------------+------------------+----------------------+------+
| 0.614148 | 841416.0 | 841416.0 | 1 |
| 0.810261 | 162018.0 | 162018.0 | 1 |
| 0.219862 | 268912.0 | 268912.0 | 1 |
| 1083180000 | 0000813801 | 0000813801 | 1 |
+------------------+-------------------+---------------------+------+
The ref3 column confirms that the second predicate is true for every row. Both subqueries therefore select the same outer-column value, and ref1 and ref2 are expected to match.
Description: REVERSE() returns different results for two correlated scalar subqueries that select the same FLOAT column value. The first subquery uses WHERE 1, while the second uses WHERE LENGTH(CONCAT(COALESCE(t1.c0, ''), 'x')) > 0. The second predicate is true for every row in the test case, as confirmed by the ref3 column, so I would expect both expressions to produce the same result. For example, for the row displaying c1 as 0.614148, the first expression returns '841416.0', while the second returns '732344702084'. The discrepancy occurs for all four rows. How to repeat: DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( c0 BIGINT, c1 FLOAT ); INSERT INTO t1 (c0, c1) VALUES (NULL, 0.614148), (NULL, 0.810261), (0, 0.219862), (95494781, 1083180000); SELECT t1.c1 AS ref0, REVERSE((SELECT t1.c1 WHERE 1)) AS ref1, REVERSE(( SELECT t1.c1 WHERE LENGTH(CONCAT(COALESCE(t1.c0, ''), 'x')) > 0 )) AS ref2, LENGTH(CONCAT(COALESCE(t1.c0, ''), 'x')) > 0 AS ref3 FROM t1; Actual result: +------------------+------------------+----------------------+------+ | ref0 | ref1 | ref2 | ref3| +------------------+------------------+----------------------+------+ | 0.614148 | 841416.0 | 732344702084 | 1 | | 0.810261 | 162018.0 | 275632111016 | 1 | | 0.219862 | 268912.0 | 880904519991 | 1 | | 1083180000 | 0000813801 | 2300813801 | 1 | +------------------+-------------------+---------------------+------+ Expected result: +------------------+------------------+----------------------+------+ | ref0 | ref1 | ref2 | ref3| +------------------+------------------+----------------------+------+ | 0.614148 | 841416.0 | 841416.0 | 1 | | 0.810261 | 162018.0 | 162018.0 | 1 | | 0.219862 | 268912.0 | 268912.0 | 1 | | 1083180000 | 0000813801 | 0000813801 | 1 | +------------------+-------------------+---------------------+------+ The ref3 column confirms that the second predicate is true for every row. Both subqueries therefore select the same outer-column value, and ref1 and ref2 are expected to match.