This is so odd, that I didn't know the best terms to place in Title. MySQL 8.0.41
Two queries with a UNION, works just fine, as expected.
SELECT m.hymn_number hymn_id, ML.column_0 num_melody, ML.column_1 mediatype, ML.column_2 filepath
FROM hymn_maintune m ,
LATERAL ( VALUES
ROW( 1, 'Partitura', m.pdf_path ) ,
ROW( 1, 'Instrumental', m.mp3_ins_path ) ,
ROW( 1, 'Voz', m.mp3_voice_path ) )
AS ML
UNION ALL
SELECT a.hymn_number , AL.column_0 , AL.column_1 , AL.column_2
FROM hymn_alternatetune a ,
LATERAL ( VALUES
ROW( 2, 'Partitura', a.pdf_path ) ,
ROW( 2, 'Instrumental', a.mp3_ins_path ) ,
ROW( 2, 'Voz', a.mp3_voice_path ) )
AS AL
produces the proper/expected values at last column filepath
# hymn_id | num_melody | mediatype | filepath
----------|------------|--------------|-----------------------------------
5 | 1 | Partitura | Uploaded-pdf-Files/0001.ciF.pdf
5 | 1 | Instrumental | Uploaded-mp3-Files/0001.piano.mp3
5 | 1 | Voz | Uploaded-mp3-Files/0001.voz_piano.mp3
8 | 1 | Partitura | Uploaded-pdf-Files/boleto.pdf
8 | 1 | Instrumental | Uploaded-mp3-Files/0004.pian0.mp3
8 | 1 | Voz | Uploaded-mp3-Files/0004.voz_pianO.mp3
... | ... | ... | ...
184 | 2 | Partitura | Uploaded-pdf-Files/0163.cif.pdf
184 | 2 | Instrumental | Uploaded-mp3-Files/0163.2.piano.mp3
184 | 2 | Voz | Uploaded-mp3-Files/0163.2.voz_piano.mp3
45 | 2 | Partitura | Uploaded-pdf-Files/0033.cif.pdf
... | ... | ... | ...
Now, I wish to select on top of it, to order by and apply filter where clauses, (create a sql view, based on this).
But, when a sql-view is created with the exactly same query, or If I select * from (it) as new_alias
SELECT hymn_medias.* FROM
( SELECT m.hymn_number hymn_id, ML.column_0 num_melody, ML.column_1 mediatype, ML.column_2 filepath
FROM hymn_maintune m ,
LATERAL ( VALUES
ROW( 1, 'Partitura', m.pdf_path ) ,
ROW( 1, 'Instrumental', m.mp3_ins_path ) ,
ROW( 1, 'Voz', m.mp3_voice_path ) )
AS ML
UNION ALL
SELECT a.hymn_number , AL.column_0 , AL.column_1 , AL.column_2
FROM hymn_alternatetune a ,
LATERAL ( VALUES
ROW( 2, 'Partitura', a.pdf_path ) ,
ROW( 2, 'Instrumental', a.mp3_ins_path ) ,
ROW( 2, 'Voz', a.mp3_voice_path ) )
AS AL
) AS hymn_medias
The last column looses its values for all rows !?!?!?!?
# hymn_id | num_melody | mediatype | filepath
----------|------------|--------------|---------
5 | 1 | Partitura |
5 | 1 | Instrumental |
5 | 1 | Voz |
8 | 1 | Partitura |
8 | 1 | Instrumental |
8 | 1 | Voz |
What's wrong!?
:-(
I can duplicate your bug in MySQL 8.4.4.
I believe this is a bug. MySQL introduced support for LATERAL in MySQL 8.0.14, and searching bugs.mysql.com there seem to be a steady stream of bugs reported regarding incorrect results from queries using LATERAL.
I don't know the precise reason for the bug, but I suggest you report it at bugs.mysql.com. Include a fully reproducible test case, including CREATE TABLE for the other tables used by your query, and INSERT statements to populate them with enough sample data to demonstrate the bug.
As a workaround, here's a query that doesn't use LATERAL. I tested this on 8.4.4, and it produced the correct result:
SELECT hymn_medias.* FROM
( SELECT hymn_number, 1, 'Partitura', pdf_path
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
) AS hymn_medias(hymn_id, num_melody, mediatype, filepath);