Issue Details (XML | Word | Printable)

Key: CORE-1605
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Dmitry Yemanov
Votes: 2
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Bugcheck 232 (invalid operation) for an aggregated query

Created: 20/Nov/07 02:37 AM   Updated: 28/May/15 03:51 PM
Component/s: Engine
Affects Version/s: 2.0.0, 2.0.1, 2.1 Alpha 1, 2.1 Beta 1, 2.0.2, 2.0.3, 2.1 Beta 2
Fix Version/s: 3.0 Alpha 1

Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 14/Feb/11 10:20 AM
Adriano, hopefully you understand the DSQL internals better in order to fix that ;-)

leonon added a comment - 11/Dec/12 08:21 AM - edited
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 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 t.id,
       sum((select O_BALANCE from s_test1(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)

Adriano dos Santos Fernandes added a comment - 11/Dec/12 01:52 PM
This was fixed in some now unknown commit.

Pavel Zotov added a comment - 15/Apr/15 07:44 PM
> 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)
===

Adriano dos Santos Fernandes added a comment - 16/Apr/15 01:44 AM
Please test now.

Pavel Zotov added a comment - 16/Apr/15 02:36 AM
> 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