Den 2011-11-07 11:20 skrev Pieter Libin (JIRA) såhär: > count query causes error > ------------------------ > > Key: CORE-3654 > URL: http://tracker.firebirdsql.org/browse/CORE-3654 > Project: Firebird Core > Issue Type: Bug > Components: Engine > Affects Versions: 2.5.1 > Environment: Ubuntu Linux, but I doubt this is a OS related bug. > Reporter: Pieter Libin > > > 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>
I think this is invalid in newer versions of Firebird, beucase it is stricter re. ambiguity now. You've got B."id" and A."id" in the subquery, both resulting in a column named "id" in the derived table. That's what the error message means. Try giving an alias to (at least) one of them, e.g. B."id" "B_id", ... A."id" "A_id", ... A."b_id" "A_b_id"... Kjell -- -------------------------------------- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 ------------------------------------------------------------------------------ RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
