Bug #120856 COERCIBILITY() differs for expression-derived strings between VIEW/CTE and TEMPORARY TABLE

• Submitted: 3 Jul 6:15
• Modified: 8 Jul 8:17
• Reporter: Xiaoyuan Xie
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 9.7.0
• OS: Any
• CPU Architecture: Any

[3 Jul 6:15] Xiaoyuan Xie

Description:
The coercibility of expression-derived string values differs depending on whether the expression is used through a VIEW/CTE or materialized into a TEMPORARY TABLE using CREATE TEMPORARY TABLE ... SELECT.

When queried through a VIEW or CTE, COERCIBILITY(vc) returns 4.

After the same expression is materialized into a TEMPORARY TABLE, COERCIBILITY(vc) returns 2.

This happens for at least the following expression-derived strings:

BIN(c2)
CHARSET(c0)

How to repeat:
Test case 1: BIN() expression

DROP VIEW IF EXISTS v0;
DROP TEMPORARY TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (
  c2 BIGINT
);

INSERT INTO t0 VALUES (100);

CREATE VIEW v0 AS
SELECT BIN(c2) AS vc
FROM t0;

SELECT 'VIEW' AS src, COERCIBILITY(vc)
FROM v0;

WITH cte AS (
  SELECT BIN(c2) AS vc
  FROM t0
)
SELECT 'CTE' AS src, COERCIBILITY(vc)
FROM cte;

CREATE TEMPORARY TABLE tmp AS
SELECT BIN(c2) AS vc
FROM t0;

SELECT 'TEMP_TABLE' AS src, COERCIBILITY(vc)
FROM tmp;

SHOW CREATE TABLE tmp;
Actual result for test case 1:

VIEW:
+------+------------------+
| src  | COERCIBILITY(vc) |
+------+------------------+
| VIEW |                4 |
+------+------------------+

CTE:
+-----+------------------+
| src | COERCIBILITY(vc) |
+-----+------------------+
| CTE |                4 |
+-----+------------------+

TEMPORARY TABLE:
+------------+------------------+
| src        | COERCIBILITY(vc) |
+------------+------------------+
| TEMP_TABLE |                2 |
+------------+------------------+
Test case 2: CHARSET() over an ENUM column

DROP VIEW IF EXISTS v0;
DROP TEMPORARY TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (
  c0 ENUM('e0_a', 'e1_b')
);

INSERT INTO t0 VALUES ('e0_a');

CREATE VIEW v0 AS
SELECT CHARSET(c0) AS vc
FROM t0;

SELECT 'VIEW' AS src, COERCIBILITY(vc)
FROM v0;

WITH cte AS (
  SELECT CHARSET(c0) AS vc
  FROM t0
)
SELECT 'CTE' AS src, COERCIBILITY(vc)
FROM cte;

CREATE TEMPORARY TABLE tmp AS
SELECT CHARSET(c0) AS vc
FROM t0;

SELECT 'TEMP_TABLE' AS src, COERCIBILITY(vc)
FROM tmp;

SHOW CREATE TABLE tmp;
Actual result for test case 2:

VIEW:
+------+------------------+
| src  | COERCIBILITY(vc) |
+------+------------------+
| VIEW |                4 |
+------+------------------+

CTE:
+-----+------------------+
| src | COERCIBILITY(vc) |
+-----+------------------+
| CTE |                4 |
+-----+------------------+

TEMPORARY TABLE:
+------------+------------------+
| src        | COERCIBILITY(vc) |
+------------+------------------+
| TEMP_TABLE |                2 |
+------------+------------------+

Expected result / question:
It is unclear whether this difference is intended.

The same expression-derived string has coercibility 4 in the VIEW and CTE forms, but after CREATE TEMPORARY TABLE ... SELECT, the resulting column has coercibility 2.

If this behavior is expected because materialization creates a physical column with implicit collation, it may need clearer documentation.

Additional notes:
This report is not claiming a wrong query result. The issue is that the coercibility metadata differs between expression-derived strings and materialized columns created from the same expression. This may affect later comparisons if the materialized result is used in expressions involving different collations.

[8 Jul 8:17] Roy Lyseng

Thank you for the bug report.

In the case of the view and the CTE, the BIN() function is given the
coercibility of a character string function, which is COERCIBLE (4).

But in the case of the temporary table, the coercibility is that of a column,
which is IMPLICIT (2).

I see that the specification of coercibility for string functions is missing
from documentation, thus I verify this as a documentation issue.