Firebird can't count records from a subquery with unnamed column
----------------------------------------------------------------
Key: CORE-6484
URL: http://tracker.firebirdsql.org/browse/CORE-6484
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.7
Environment: Windows 10 64-bit
Reporter: Silvio Clécio
Consider the following table:
{code:sql}
CREATE TABLE persons (
ID integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name VARCHAR(100) NOT NULL
age SMALLINT NOT NULL
);
{code:sql}
and the following records:
{code:sql}
INSERT INTO persons (id, name, age) VALUES (1, 'Joe', 12);
INSERT INTO persons (id, name, age) VALUES (2, 'Annie', 29);
INSERT INTO persons (id, name, age) VALUES (3, 'Robert', 35);
INSERT INTO persons (id, name, age) VALUES (4, 'Margaret', 12);
{code}
when you try to run (I have been using FlameRobin) the following SQL:
{code:sql}
SELECT COUNT(*) FROM (
SELECT age, COUNT(*) FROM persons
GROUP BY age
) AS t
{code}
the Firebird returns the following error:
{code}
Error: *** IBPP::SQLException ***
Context: Statement::Prepare( SELECT COUNT(*) FROM (
SELECT age, COUNT(*) FROM persons
GROUP BY age
) AS t )
Message: isc_dsql_prepare failed
SQL Message : -104
Invalid token
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Invalid command
no column name specified for column number 2 in derived table T
{code}
however, it works fine in other DBMSes, like PostgreSQL, MySQL or even in a
non-DBMS, like SQLite. 😕
I'm not Firebird expert, but it sounds like a bug.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
