MySQL子查询外层SELECT中filepath列值丢失问题排查

原因分析 #

这是MySQL 8.0.x版本处理LATERAL结合VALUES子句并通过UNION ALL合并，再作为子查询或视图时的列类型推断缺陷：

• 单独执行查询时，MySQL能直接从hymn_maintune和hymn_alternatetune表的pdf_path/mp3_ins_path/mp3_voice_path列继承正确的字符串类型；
• 当查询被包裹为子查询或创建视图时，若原表对应列存在NULL值，MySQL会错误地将VALUES子句生成的column_2列类型推断为NULL，而非原表的字符串类型（如VARCHAR），导致所有行的filepath值被截断为NULL。

解决方法 #

方法1：显式指定VALUES子句中列的数据类型 #

通过CAST函数强制指定column_2的类型与原表列一致（示例假设原列为VARCHAR(255)），同时给LATERAL别名指定列名，明确列定义：

SELECT m.hymn_number hymn_id, ML.num_melody, ML.mediatype, ML.filepath 
FROM hymn_maintune m ,
    LATERAL ( VALUES 
              ROW( 1, 'Partitura', CAST(m.pdf_path AS VARCHAR(255)) ) , 
              ROW( 1, 'Instrumental', CAST(m.mp3_ins_path AS VARCHAR(255)) ) , 
              ROW( 1, 'Voz', CAST(m.mp3_voice_path AS VARCHAR(255)) ) )
       AS ML(num_melody, mediatype, filepath)
UNION ALL  
SELECT a.hymn_number , AL.num_melody , AL.mediatype , AL.filepath  
FROM hymn_alternatetune a ,
    LATERAL ( VALUES 
              ROW( 2, 'Partitura', CAST(a.pdf_path AS VARCHAR(255)) ) , 
              ROW( 2, 'Instrumental', CAST(a.mp3_ins_path AS VARCHAR(255)) ) , 
              ROW( 2, 'Voz', CAST(a.mp3_voice_path AS VARCHAR(255)) ) )
       AS AL(num_melody, mediatype, filepath)

方法2：替换LATERAL VALUES为拆分的UNION ALL语句 #

放弃LATERAL VALUES写法，改用多个单条SELECT通过UNION ALL拼接，让MySQL直接继承原表列类型：

SELECT hymn_number hymn_id, 1 num_melody, 'Partitura' mediatype, pdf_path filepath FROM hymn_maintune
UNION ALL
SELECT hymn_number, 1, 'Instrumental', mp3_ins_path FROM hymn_maintune
UNION ALL
SELECT hymn_number, 1, 'Voz', mp3_voice_path FROM hymn_maintune
UNION ALL
SELECT hymn_number, 2, 'Partitura', pdf_path FROM hymn_alternatetune
UNION ALL
SELECT hymn_number, 2, 'Instrumental', mp3_ins_path FROM hymn_alternatetune
UNION ALL
SELECT hymn_number, 2, 'Voz', mp3_voice_path FROM hymn_alternatetune

方法3：创建视图时显式约束列类型（仅视图场景） #

若需创建视图，可结合方法1的CAST写法，在视图定义中强制指定列类型，避免推断错误：

CREATE VIEW hymn_media AS
SELECT m.hymn_number hymn_id, ML.num_melody, ML.mediatype, ML.filepath 
FROM hymn_maintune m ,
    LATERAL ( VALUES 
              ROW( 1, 'Partitura', CAST(m.pdf_path AS VARCHAR(255)) ) , 
              ROW( 1, 'Instrumental', CAST(m.mp3_ins_path AS VARCHAR(255)) ) , 
              ROW( 1, 'Voz', CAST(m.mp3_voice_path AS VARCHAR(255)) ) )
       AS ML(num_melody, mediatype, filepath)
UNION ALL  
SELECT a.hymn_number , AL.num_melody , AL.mediatype , AL.filepath  
FROM hymn_alternatetune a ,
    LATERAL ( VALUES 
              ROW( 2, 'Partitura', CAST(a.pdf_path AS VARCHAR(255)) ) , 
              ROW( 2, 'Instrumental', CAST(a.mp3_ins_path AS VARCHAR(255)) ) , 
              ROW( 2, 'Voz', CAST(a.mp3_voice_path AS VARCHAR(255)) ) )
       AS AL(num_melody, mediatype, filepath);

内容的提问来源于stack exchange，提问作者Samuel Pizarro