sqlc gets confused about ORDER BY and column names when a UNION is involved.

Version

Other

What happened?

With version 1.19.0, sqlc gets confused when I have two parts of a UNION query emit a column with the same alias, like this:

-- name: SelectExactlyOneRow :one
SELECT
    'hello' AS greeting
    FROM stuff
    WHERE needed = 42
UNION
SELECT 'goodbye'::jsonb AS greeting
ORDER BY greeting -- replace with ORDER BY 1 to make this all work properly
LIMIT 1;

Here is a demonstration of the problem: https://play.sqlc.dev/p/03c0103fb5161edafab0acdaf7bfa7c31ece807e2477305e2719452b6760ed7e

Here it is working: https://play.sqlc.dev/p/e9d330cf86921afa1e5ce6cc6fbe5711f9e3289e2eaa34bb6da72398c533c6cd

The error message talks about strict_order_by, but I don't really want to turn off all of the validation of ORDER BY clauses in my project. Also, I couldn't find a reference to this parameter in the documentation.

Relevant log output

# package db
query.sql:11:1: column reference "greeting" not found: if you want to skip this validation, set 'strict_order_by' to false

Database schema

CREATE TABLE stuff (
  id SERIAL PRIMARY KEY,
  needed INTEGER
);

SQL queries

-- name: SelectExactlyOneRow :one
SELECT
    'hello' AS greeting
    FROM stuff
    WHERE needed = 42
UNION
SELECT 'goodbye'::jsonb AS greeting
ORDER BY greeting -- replace with ORDER BY 1 to make this all work properly
LIMIT 1;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/03c0103fb5161edafab0acdaf7bfa7c31ece807e2477305e2719452b6760ed7e

sqlc-dev / sqlc

sqlc gets confused about ORDER BY and column names when a UNION is involved. #2419