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-automations opened this issue Nov 20, 2007 · 14 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @asfernandes

This was fixed in some now unknown commit.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA536 [ QA536 ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Please test now.

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

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