Bug #121261 CRC32() returns different results for equivalent correlated scalar subqueries

• Submitted: 10 Sep 6:12
• Modified: 10 Sep 7:41
• Reporter: Hust DBTesting
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 26.7.0
• OS: Any
• CPU Architecture: ARM (Apple Silicon)

[10 Sep 6:12] Hust DBTesting

Description:
MySQL returns different CRC32() values for two correlated scalar subqueries that return the same value.

The same test case returns the expected result in TiDB, suggesting that the inconsistent behavior is MySQL-specific.

How to repeat:
DROP TABLE IF EXISTS t2;

CREATE TABLE t2 (
    c0 FLOAT NULL
);

INSERT INTO t2 (c0) VALUES (0.57586);

SELECT
    t2.c0,
    CRC32(t2.c0),
    CRC32((SELECT t2.c0 WHERE 1)),
    CRC32((
        SELECT t2.c0
        WHERE LOCATE('F', CONCAT(t2.c0, 'FF')) > 0
    ))
FROM t2;

Expected result

The two scalar subqueries return the same value:

(SELECT t2.c0 WHERE 1)

and

(
    SELECT t2.c0
    WHERE LOCATE('F', CONCAT(t2.c0, 'FF')) > 0
)

For the inserted value 0.57586, the predicate is true because CONCAT(t2.c0, 'FF') contains F.

Therefore, both scalar subqueries return 0.57586, and applying CRC32() to either result should produce the same value.

Expected:
0.57586 | 4128368541 | 4128368541 | 4128368541

Actual result
0.57586 | 4128368541 | 4128368541 | 11518377

The first two CRC32() calls agree, while the CRC32() call around the predicate-bearing correlated scalar subquery returns a different value.

[10 Sep 7:41] Roy Lyseng

Duplicate of bug#121255.

[10 Sep 19:18] Jean-François Gagné

As for Bug#121255, making repro and expanding on types.  There is an implicit conversions from FLOAT to string when calling CRC32, but when making it explicit, things break.  Unclear what type ends-up being passed to CRC32 in CRC32((SELECT t2.c0 WHERE LOCATE('F', CONCAT(t2.c0, 'FF')) > 0)).

./use <<< "DROP DATABASE IF EXISTS test_jfg ; CREATE DATABASE test_jfg;"
./use -N test_jfg <<< "
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2 (c0 FLOAT NULL);
  INSERT INTO t2 VALUES (0.57586);"

./use -N test_jfg <<< "
  SELECT
    t2.c0,
    CRC32(t2.c0),
    CRC32((SELECT t2.c0 WHERE 1)),
    CRC32((SELECT t2.c0 WHERE LOCATE('F', CONCAT(t2.c0, 'FF')) > 0))
  FROM t2;"
0.57586 4128368541      4128368541      11518377

./use -N test_jfg <<< "
  create temporary table foo
    SELECT
      t2.c0 as f1,
      CRC32(t2.c0) as f2,
      CRC32((SELECT t2.c0 WHERE 1)) as f3,
      CRC32((SELECT t2.c0 WHERE LOCATE('F', CONCAT(t2.c0, 'FF')) > 0)) as f4
    FROM t2;
  desc foo;
"
f1      float   YES             NULL    NULL
f2      int unsigned    YES             NULL    NULL
f3      int unsigned    YES             NULL    NULL
f4      int unsigned    YES             NULL    NULL

./use -N test_jfg <<< "
  create temporary table foo
    SELECT
      t2.c0 as f2,
      (SELECT t2.c0 WHERE 1) as f3,
      (SELECT t2.c0 WHERE LOCATE('F', CONCAT(t2.c0, 'FF')) > 0) as f4
    FROM t2;
  desc foo;
"
f2      float   YES             NULL    NULL
f3      float   YES             NULL    NULL
f4      float   YES             NULL    NULL

./use -N test_jfg <<< "
  create temporary table foo
    SELECT
      CAST(t2.c0 AS CHAR) as s2,
      CAST((SELECT t2.c0 WHERE 1) AS CHAR) as s3,
      CAST((SELECT t2.c0 WHERE LOCATE('F', CONCAT(t2.c0, 'FF')) > 0) AS CHAR) as s4
    FROM t2;
  desc foo;
"
ERROR 1406 (22001) at line 2: Data too long for column 's4' at row 2

./use -N test_jfg <<< "
  create temporary table foo
    SELECT
      CAST(t2.c0 AS CHAR) as s2,
      CAST((SELECT t2.c0 WHERE 1) AS CHAR) as s3
    FROM t2;
  desc foo;
"
s2      varchar(12)     YES             NULL    NULL
s3      varchar(12)     YES             NULL    NULL

./use -N test_jfg <<< "
  create temporary table foo
    SELECT
      CAST((SELECT t2.c0 WHERE LOCATE('F', CONCAT(t2.c0, 'FF')) > 0) AS CHAR) as s4
    FROM t2;
  desc foo;
"
ERROR 1406 (22001) at line 2: Data too long for column 's4' at row 2