Table aliases required to disambiguate duplicated column in a JOIN with SQLite

[jamietanna]

Version

1.17.2

What happened?

When trying to write a query that uses multiple tables with duplicated columns (through a NATURAL JOIN) it appears we need to use table aliases, instead of being able to reference the table name.

Relevant log output

# package db
queries.sql:59:3: column "engine" does not exist
exit status 1
internal/datasources/awsrds/db/generate.go:3: running "go": exit status 1

Database schema

CREATE TABLE IF NOT EXISTS aws_rds_databases (
  account_id TEXT NOT NULL,
  region TEXT NOT NULL,
  arn TEXT NOT NULL,
  name TEXT NOT NULL,
  engine TEXT NOT NULL,
  engine_version TEXT NOT NULL,

  -- tags is a JSON object
  tags TEXT NOT NULL,

  UNIQUE (account_id, region, arn) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS aws_rds_databases_engines (
  engine TEXT NOT NULL,
  engine_version TEXT NOT NULL,
  deprecation TEXT NOT NULL,

  UNIQUE (engine, engine_version) ON CONFLICT REPLACE
);

SQL queries

------------------
-- this is expected to fail, due to:
--> # package db
--> queries.sql:59:3: column reference "engine" is ambiguous

-- name: RetrieveAllWithDeprecation :many
select
  arn,
  name,
  engine
from
  aws_rds_databases
  natural join aws_rds_databases_engines;

------------------
-- expected to work
-- name: RetrieveAllWithDeprecation :many
select
  arn,
  name,
  aws_rds_databases.engine
from
  aws_rds_databases r
  natural join aws_rds_databases_engines
;

------------------
-- this works, however
-- name: RetrieveAllWithDeprecation :many
select
  arn,
  name,
  r.engine
from
  aws_rds_databases r
  natural join aws_rds_databases_engines
;

Configuration

version: 2
sql:
  - engine: "sqlite"
    schema: "schema.sql"
    queries: "queries.sql"
    gen:
      go:
        package: db
        out: .

Playground URL

https://play.sqlc.dev/p/9c6cd6f2df6585a2c1b5d5762b350179a9c78af984f8a657233c433153ca8732

What operating system are you using?

Linux, macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[mvdan]

Duplicate of #2271, I think.

[orisano]

This issue seems to be different from #2271.

sqlc/internal/compiler/output_columns.go

Lines 558 to 577 in 64e6c9c

	case *ast.RangeVar:
	fqn, err := ParseTableName(n)
	if err != nil {
	return nil, err
	}
	table, cerr := qc.GetTable(fqn)
	if cerr != nil {
	// TODO: Update error location
	// cerr.Location = n.Location
	// return nil, *cerr
	return nil, cerr
	}
	if n.Alias != nil {
	table.Rel = &ast.TableName{
	Catalog: table.Rel.Catalog,
	Schema: table.Rel.Schema,
	Name: *n.Alias.Aliasname,
	}
	}
	tables = append(tables, table)

This prevents searching by the original table name.

[kyleconroy]

We've upgraded from column reference error to a nil pointer dereference

https://play.sqlc.dev/p/333ef002c390049e4acb9d65e34b7823ff65a27b01ee94558630045ec534bee1