1

Consider the SELECT statement below:

SELECT 1, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 3, 'C';

The result is obvious:

1    'A'
2    'B'
3    'C'

I tried to store it as a separate table:

CREATE TABLE tmp AS
    SELECT 1, 'A'
    UNION ALL
    SELECT 2, 'B'
    UNION ALL
    SELECT 3, 'C';

The contents of the tmp table are surprising:

1    'A'
2    'A'
3    'A'

Ok, that can be fixed providing explicit field names:

CREATE TABLE tmp AS
    SELECT 1 AS field1, 'A' AS field2
    UNION ALL
    SELECT 2, 'B'
    UNION ALL
    SELECT 3, 'C';

Now I have a question whether the observed behavior is defined and valid. I feel that the statement without explicit field names should end with an error instead of producing a surprising result.

I'm using SQLite.

Stu
  • 30,392
  • 6
  • 14
  • 33
Dmitry Kuzminov
  • 6,180
  • 6
  • 18
  • 40

0 Answers0