dynamobi/dynamobi — Issue #200: [LDB-121] SUM aggregate gives incorrect results when using index-only scan
Author: dynamobi-build
Created: 2011-12-30 02:21:14+00:00
State: open
Labels: LDB

[reporter="elin", created="Tue, 25 Jul 2006 11:51:02 -0500 (GMT-05:00)"]
To reproduce:

create schema s;

set schema 's';

create table SALES(

CUSTID integer,

EMPNO integer,

PRODID integer,

PRICE numeric(3,2)

);

create index SALES_PRICE on SALES(PRICE);

create server ff_server

foreign data wrapper sys_file_wrapper

options(

directory '/home/elin/open/luciddb/test/sql/company/csv-copy',

file_extension '.csv',

ctrl_file_extension '.bcp',

field_delimiter ',',

line_delimiter '\n',

quote_char '"',

escape_char '',

with_header 'yes',

num_rows_scan '9'

);

INSERT INTO SALES SELECT * FROM ff_server."BCP".SALES;

select sum(price) from sales;

==========================

The correct sum should be 4997.78, instead we get 27742174025073455.36

Here's what the plan looks like:

'column0'

'IterCalcRel(expr#0=[{inputs}], EXPR$0=[$t0])'

' FennelToIteratorConverter'

' LcsIndexAggRel(groupCount=[0], agg#0=[SUM(0)])'

' FennelRenameRel(fieldNames=[[$f0, SRID, SegmentDesc, Segment]])'

'LcsIndexOnlyScanRel(table=[[LOCALDB, S, SALES]], index=[SALES_PRICE], projection=[[0, 1, 2, 3]])'

If the SALES_PRICE index isn't created, it gives the correct result 4997.78 With the following plan:

'column0'

'IterCalcRel(expr#0=[{inputs}], EXPR$0=[$t0])'

' FennelToIteratorConverter'

' FennelAggRel(groupCount=[0], agg#0=[SUM(0)])'

' FennelRenameRel(fieldNames=[[$f0]])'

'LcsRowScanRel(table=[[LOCALDB, S, SALES]], projection=[[3]], clustered indexes=[[SYS$CLUSTERED_INDEX$S$SALES$PRICE]])'