Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bugcheck 232 (invalid operation) for an aggregated query [CORE1605] #2026

Closed
firebird-issue-importer opened this issue Nov 20, 2007 · 14 comments

Comments

@firebird-issue-importer

Submitted by: @dyemanov

Is related to QA536

Votes: 2

recreate table some_table (id int);

recreate procedure some_proc (id int)
returns (result int)
as
begin
result = 1;
suspend;
end;

insert into some_table values (1);
commit;

select
count(*),
sum( (select result from some_proc(id)) )
from
some_table
group by
2

-- produces bugcheck 232 (invalid operation) instead of throwing a proper error

Commits: 23c8c92 FirebirdSQL/fbt-repository@058dd20

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

Workflow: jira [ 13471 ] => Firebird [ 14077 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 11, 2008

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 14, 2011

Commented by: @dyemanov

Adriano, hopefully you understand the DSQL internals better in order to fix that ;-)

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 14, 2011

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ] => Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 11, 2012

Commented by: leonon (leonon)

Hello!

Server WI-V6.3.2.26539 Firebird 2.5
Client 2.5.2.26539

Example:

CREATE TABLE TEST1 (
ID INTEGER NOT NULL,
BALANCE DOUBLE PRECISION
);

ALTER TABLE TEST1 ADD CONSTRAINT PK_TEST1 PRIMARY KEY (ID);

SET TERM ^ ;

create or alter procedure S_TEST1 (
I_ID integer)
returns (
O_BALANCE double precision)
as
begin
select T.BALANCE
from TEST1 T
where http://T.ID = :I_ID
into O_BALANCE;

O_BALANCE = coalesce(O_BALANCE, 0);

suspend;
end^

SET TERM ; ^

GRANT SELECT ON TEST1 TO PROCEDURE S_TEST1;

Generating random test data (50,000 records)

When run the query:

select http://t.id,
sum((select O_BALANCE from s_test1(http://t.ID)))
from test1 t
group by 1, 2

server is shut down with error:
internal Firebird consistency check (EVL_expr: invalid operation (232), file: evl.cpp line: 1207)

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 11, 2012

Commented by: @asfernandes

This was fixed in some now unknown commit.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 11, 2012

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

Link: This issue is related to QA536 [ QA536 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 15, 2015

Commented by: @pavel-zotov

> produces bugcheck 232 (invalid operation) instead of throwing a proper error

Currently error "Cannot use an aggregate or window function in a GROUP BY clause" is produced in FB 3.0 only if expression does NOT contain aggregate of stored procedure result:

Test-1:

create table test01(x int);
create table test02(x int);
create table test03(x int);
create table test04(x int);
commit;

select count(*), sum( ( select rdb$format from rdb$formats f where f.rdb$relation_id = r.rdb$relation_id ) )
from rdb$relations r;

            COUNT                   SUM

===================== =====================
54 4

select count(*), sum( ( select rdb$format from rdb$formats f where f.rdb$relation_id = r.rdb$relation_id ) )
from rdb$relations r
group by 2; ------------------------------- [ 1 ]

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Cannot use an aggregate or window function in a GROUP BY clause

Test-2:

set term ^;
create or alter procedure sp_sum(a_id int) returns(s int) as
begin
for
select rdb$format
from rdb$formats f
where f.rdb$relation_id = :a_id
into s
do suspend;
end
^
set term ;^
commit;
select count(*), sum( (select s from sp_sum(r.rdb$relation_id)) )
from rdb$relations r;

       COUNT                   SUM

================ =====================
54 4

select count(*), sum( (select s from sp_sum(r.rdb$relation_id)) )
from rdb$relations r
group by 2; -------------------------------- [ 2 ]

       COUNT                   SUM

================ =====================
54 4

NO errors. Is it correct ?

PS.

ISQL Version: WI-T3.0.0.31785 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31785 Firebird 3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.31785 Firebird 3.0 Beta 2/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31785 Firebird 3.0 Beta 2/tcp (csprog)/P13"
on disk structure version 12.0

PPS. List of "Affects Version/s:" should be added with 2.5 because following messages appear on console:

Statement failed, SQLSTATE = XX000
internal Firebird consistency check (EVL_expr: invalid operation (232), file: evl.cpp line: 1219)

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 16, 2015

Commented by: @asfernandes

Please test now.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 16, 2015

Commented by: @pavel-zotov

> Please test now.

It's OK now (LI-T3.0.0.31789):
. . .
select
sum( id ),
sum( (select result from some_proc(id)) )
from some_table
group by 2;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Cannot use an aggregate or window function in a GROUP BY clause

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 28, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 25, 2015

Commented by: @pcisar

Test created.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 25, 2015

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment