Ambiguous column name when putting together several nested SELECT statements

Question

Trying to use a snippet of code from https://gist.github.com/fhoffa/411cd5ac0d7b8bd2b17a.

Getting this error message: Column name c1 is ambiguous at [1:24]

SELECT 'c1' AS column, c1 AS value, rowid
FROM
    (SELECT 1 c1, 2 c2, 3 c3, 'id1' rowid),
    (SELECT 9 c1, 2 c2, 3 c3, 'id2' rowid), 
    (SELECT 4 c1, 1 c2, 3 c3, 'id3' rowid)

This code was posted 8 years ago. It may have worked at that time but it is not working now.

I appreciate any feedback.

Thanks

Answer 1

Using 'UNION ALL' got rid of the error. Also, there must be an extra parenthesis needed to group these 3 select statements.
Without the parenthesis, a misleading error message shows up: Something like first select has 3 columns but the second select has 4 columns. Weird error message.

SELECT 'c1' AS column, c1 as value, rowid

FROM

    (
     (SELECT 1 c1, 2 c2, 3 c3, 'id1' as rowid)
     UNION ALL
     (SELECT 9 c1, 2 c2, 3 c3, 'id2' as rowid)
     UNION ALL
     (SELECT 4 c1, 1 c2, 3 c3, 'id3' as rowid)
    )