Submitted by: Pieter Libin (pieter_emweb.be)

Consider we have a new database containing the following tables:

create table "table_b" (
"id" bigint primary key,
"name" varchar(50) not null
);

create table "table_a" (
"id" bigint primary key,
"date" date,
"b_id" bigint,
constraint "fk_table_a_b" foreign key ("b_id") references "table_b" ("id") on update cascade on delete cascade
);

When we try to execute a count query, which appears to me to be valid SQL:

select count(1) from ( select B."id", B."name", A."id", A."date", A."b_id" from "table_b" B join "table_a" A on A."b_id" = B."id");

an error is reported

column id was specified multiple times for derived table <unnamed>