Regression: inference of subquery aliased field name and type (number instead of boolean)

[karlhorky]

Describe the bug

SafeQL infers that the field in the subquery is id and not the aliased name rsvped:

// 💥 Query has incorrect type annotation.
//    Expected: { rsvped: boolean }[]
//      Actual: { id: number }[]
await sql<{ rsvped: boolean }[]>`
  SELECT
    x.*
  FROM
    (
      SELECT
        event_rsvps.id IS NOT NULL AS rsvped
      FROM
        event_rsvps
    ) x
`

Workaround

1. Use explicit field names (x.rsvped instead of x.*)
2. Switch boolean type to boolean | null (not accurate, but I think this is a separate SafeQL nullability inference error)

// ✅
await sql<{ rsvped: boolean | null }[]>`
  SELECT
    x.rsvped
  FROM
    (
      SELECT
        event_rsvps.id IS NOT NULL AS rsvped
      FROM
        event_rsvps
    ) x
`

To Reproduce

See above

Expected behavior

Inference of the aliased name rsvped in the subquery

Screenshots

--

Desktop (please complete the following information):

• OS: macOS Sequoia 15.2 (24C101)
• PostgreSQL version 14.13
• Version 3.6.2

Additional context

Originally introduced in this PR:

• fix: correct type inference for arithmetic operations #359

[Newbie012]

fixed in 3.6.3

[karlhorky]

Hmm, still seeing this with @ts-safeql/eslint-plugin@3.6.4 🤔

// 💥 Query has incorrect type annotation.
//    Expected: { rsvped: boolean }[]
//      Actual: { id: number }[]
await sql<{ rsvped: boolean }[]>`
  SELECT
    x.*
  FROM
    (
      SELECT
        event_rsvps.id IS NOT NULL AS rsvped
      FROM
        event_rsvps
    ) x
`

Here's the minimal table definition, in case that works for reproducing for you:

CREATE TABLE event_rsvps (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY
);

[Newbie012]

does it still happening on 3.6.6? for some reason I was unable to reproduce it

[karlhorky]

I still see it on 3.6.6, yeah.

Is packages/generate/src/generate.test.ts the correct place for me to add a failing test?

Eg:

1. Set up tables:

   safeql/packages/generate/src/generate.test.ts

   Lines 14 to 142 in 39e978f

   	function runMigrations(sql: SQL) {
   	return sql.unsafe(`
   	CREATE TYPE certification AS ENUM ('HHA', 'RN', 'LPN', 'CNA', 'PCA', 'OTHER');
   	CREATE DOMAIN phone_number AS TEXT CHECK (VALUE ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$');
   	CREATE TABLE caregiver (
   	id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   	first_name TEXT NOT NULL,
   	last_name TEXT NOT NULL
   	);
   	CREATE TABLE caregiver_certification (
   	caregiver_id INTEGER NOT NULL REFERENCES caregiver(id),
   	certification certification NOT NULL
   	);
   	CREATE TABLE caregiver_phonenumber (
   	caregiver_id INTEGER NOT NULL REFERENCES caregiver(id),
   	phone_number phone_number NOT NULL
   	);
   	CREATE TABLE agency (
   	id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   	name TEXT NOT NULL
   	);
   	CREATE TABLE caregiver_agency (
   	id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   	caregiver_id INT NOT NULL REFERENCES caregiver(id),
   	agency_id INT NOT NULL REFERENCES agency(id)
   	);
   	CREATE TABLE test_date_column (
   	date_col DATE NOT NULL,
   	date_array date[] NOT NULL,
   	instant_arr timestamptz[] NOT NULL,
   	time_arr time[] NOT NULL,
   	timetz_arr timetz[] NOT NULL,
   	local_date_time_arr timestamp[] NOT NULL,
   	nullable_date_arr date[] NULL
   	);
   	CREATE TABLE test_nullability (
   	nullable_col TEXT
   	);
   	CREATE TABLE test_jsonb (
   	id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   	nullable_col TEXT
   	);
   	CREATE TYPE overriden_enum AS ENUM ('foo', 'bar');
   	CREATE TABLE test_overriden_enum (
   	col overriden_enum NOT NULL,
   	nullable_col overriden_enum
   	);
   	CREATE DOMAIN overriden_domain AS TEXT CHECK (VALUE ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$');
   	CREATE TABLE test_overriden_domain (
   	col overriden_domain NOT NULL,
   	nullable_col overriden_domain
   	);
   	CREATE TABLE all_types (
   	id SERIAL PRIMARY KEY NOT NULL,
   	text_column TEXT NOT NULL,
   	varchar_column VARCHAR(255) NOT NULL,
   	char_column CHAR(10) NOT NULL,
   	int_column INTEGER NOT NULL,
   	smallint_column SMALLINT NOT NULL,
   	bigint_column BIGINT NOT NULL,
   	decimal_column DECIMAL(10, 2) NOT NULL,
   	numeric_column NUMERIC(14, 4) NOT NULL,
   	real_column REAL NOT NULL,
   	double_column DOUBLE PRECISION NOT NULL,
   	serial_column SERIAL NOT NULL,
   	bigserial_column BIGSERIAL NOT NULL,
   	boolean_column BOOLEAN NOT NULL,
   	date_column DATE NOT NULL,
   	time_column TIME NOT NULL,
   	time_with_timezone_column TIME WITH TIME ZONE NOT NULL,
   	timestamp_column TIMESTAMP NOT NULL,
   	timestamp_with_timezone_column TIMESTAMP WITH TIME ZONE NOT NULL,
   	interval_column INTERVAL NOT NULL,
   	uuid_column UUID NOT NULL,
   	json_column JSON NOT NULL,
   	jsonb_column JSONB NOT NULL,
   	array_text_column TEXT[] NOT NULL,
   	array_int_column INTEGER[] NOT NULL,
   	bytea_column BYTEA NOT NULL,
   	inet_column INET NOT NULL,
   	cidr_column CIDR NOT NULL,
   	macaddr_column MACADDR NOT NULL,
   	macaddr8_column MACADDR8 NOT NULL,
   	tsvector_column TSVECTOR NOT NULL,
   	tsquery_column TSQUERY NOT NULL,
   	xml_column XML NOT NULL,
   	point_column POINT NOT NULL,
   	line_column LINE NOT NULL,
   	lseg_column LSEG NOT NULL,
   	box_column BOX NOT NULL,
   	path_column PATH NOT NULL,
   	polygon_column POLYGON NOT NULL,
   	circle_column CIRCLE NOT NULL,
   	money_column MONEY NOT NULL,
   	bit_column BIT(3) NOT NULL,
   	bit_varying_column BIT VARYING(5) NOT NULL
   	);
   	CREATE TABLE IF NOT EXISTS table1 (
   	id SERIAL PRIMARY KEY,
   	name INTEGER NOT NULL
   	);
   	CREATE SCHEMA IF NOT EXISTS schema1;
   	CREATE TABLE IF NOT EXISTS schema1.table1 (
   	id SERIAL PRIMARY KEY,
   	name TEXT NOT NULL
   	);
   	CREATE SCHEMA IF NOT EXISTS schema2;
   	CREATE TABLE IF NOT EXISTS schema2.table1 (
   	id SERIAL PRIMARY KEY,
   	name TEXT
   	);
   	`);
   	}

2. Test query against annotation:

   safeql/packages/generate/src/generate.test.ts

   Lines 309 to 319 in 39e978f

   	test("camel case field transform", async () => {
   	await testQuery({
   	options: { fieldTransform: "camel" },
   	query: `SELECT id, first_name, last_name from caregiver LIMIT 1`,
   	expected: [
   	["id", { kind: "type", value: "number", type: "int4" }],
   	["firstName", { kind: "type", value: "string", type: "text" }],
   	["lastName", { kind: "type", value: "string", type: "text" }],
   	],
   	});
   	});

[Newbie012]

yes

[Newbie012]

Can you try @ts-safeql/eslint-plugin@3.6.7?

[karlhorky]

Still seeing the failure with boolean vs number in @ts-safeql/eslint-plugin@3.6.7:

Code for copy+paste:

  await sql`
    CREATE TABLE event_rsvps (
      id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
      user_id integer NOT NULL REFERENCES users (id) ON DELETE cascade,
      event_id integer NOT NULL REFERENCES events (id) ON DELETE cascade,
      company_cohort_access_level_paid_id integer REFERENCES company_cohort_access_levels_paid (id),
      UNIQUE (user_id, event_id)
    )
  `;

  await sql<{ rsvped: boolean }[]>`
    SELECT
      x.*
    FROM
      (
        SELECT
          event_rsvps.id IS NOT NULL AS rsvped
        FROM
          event_rsvps
      ) x
  `;

[karlhorky]

Interestingly, @ts-safeql/eslint-plugin@3.6.7 *does* work for my original (more complicated) query (which I did not post anywhere in this issue).

But the simple query appears to continue to fail.

[Newbie012]

Had some time this morning to tackle this issue again. Looking back, implementing nested source resolution was a mistake—it only worked for actual columns, not expressions like id is not null. For now, I've reverted it to favor less strict inference over incorrect inference. A proper fix will require a more complex solution, which I'm planning on when I'll have time.