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

[danielbprice]

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

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[danielbprice]

Uhh, also, I realize that sometimes the code above is outputing JSONB and sometimes a string-- that's because I was rapidly simplifying a more complicated example. The bug persists even with this corrected. 🤦

https://play.sqlc.dev/p/fb3aadb5e1035fb69e97e96cfb2e3be9eaf3bedd156f04ab604987353de4ddec