dynamobi/dynamobi — Issue #229: [LDB-92] avg function returning different values for with indices and without indices
Author: dynamobi-build
Created: 2011-12-30 02:23:13+00:00
State: open
Labels: LDB, LHX

[reporter="elin", created="Tue, 2 May 2006 17:00:28 -0500 (GMT-05:00)"]
It looks like AVG is returning a value rounded up when indexes used and rounded down or truncated when indexes are not used.

The explain plan difference is that without indexes LhxJoinRel is being used and with indexes FennelCartesianProductRel is being used.

Here's what the indexed version returns:

0: jdbc:luciddb:> alter system set "calcVirtualMachine" = 'CALCVM_JAVA';

0: jdbc:luciddb:> explain plan excluding attributes for

. . . . . . . . > SELECT min(EMP.LNAME), min(DEPT.DNAME), LOCATION.CITY, avg(EMP.EMPNO),

. . . . . . . . > floor(avg(EMP.EMPNO))

. . . . . . . . > from EMP, DEPT, LOCATION

. . . . . . . . > where EMP.DEPTNO=DEPT.DEPTNO and DEPT.LOCID=LOCATION.LOCID

. . . . . . . . > group by LOCATION.CITY order by LOCATION.CITY;

+--------------------------------------------------------+

|column0 |

+--------------------------------------------------------+

| FennelToIteratorConverter |

| FennelSortRel|

| IteratorToFennelConverter |

| IterCalcRel |

| FennelToIteratorConverter |

| FennelAggRel |

| FennelSortRel |

| IteratorToFennelConverter|

| IterCalcRel |

| FennelToIteratorConverter |

| FennelCartesianProductRel |

| IteratorToFennelConverter|

| IterCalcRel |

| FennelToIteratorConverter |

| FennelCartesianProductRel |

| LcsRowScanRel |

| LcsRowScanRel |

| LcsRowScanRel |

+--------------------------------------------------------+

0: jdbc:luciddb:>

0: jdbc:luciddb:> SELECT min(EMP.LNAME), min(DEPT.DNAME), LOCATION.CITY, avg(EMP.EMPNO),

. . . . . . . . > floor(avg(EMP.EMPNO))

. . . . . . . . > from EMP, DEPT, LOCATION

. . . . . . . . > where EMP.DEPTNO=DEPT.DEPTNO and DEPT.LOCID=LOCATION.LOCID

. . . . . . . . > group by LOCATION.CITY order by LOCATION.CITY;

+----------+--------------+----------------+---------+---------+

| EXPR$0 | EXPR$1 | CITY | EXPR$3 | EXPR$4 |

+----------+--------------+----------------+---------+---------+

| Brown | Support | Menlo Park | 104 | 104 |

| Fratz | Accounting | San Francisco | 103 | 103 |

| Andrews | Development | San Mateo | 105 | 105 |

+----------+--------------+----------------+---------+---------+

0: jdbc:luciddb:>

Here's the unindexed version:

0: jdbc:luciddb:> alter system set "calcVirtualMachine" = 'CALCVM_JAVA';

0: jdbc:luciddb:> explain plan excluding attributes for

. . . . . . . . > SELECT min(EMP.LNAME), min(DEPT.DNAME), LOCATION.CITY, avg(EMP.EMPNO),

. . . . . . . . > floor(avg(EMP.EMPNO))

. . . . . . . . > from EMP, DEPT, LOCATION

. . . . . . . . > where EMP.DEPTNO=DEPT.DEPTNO and DEPT.LOCID=LOCATION.LOCID

. . . . . . . . > group by LOCATION.CITY order by LOCATION.CITY;

+------------------------------------------------------+

| column0|

+------------------------------------------------------+

| FennelToIteratorConverter |

| FennelSortRel |

| IteratorToFennelConverter|

| IterCalcRel |

| FennelToIteratorConverter |

| FennelAggRel |

| FennelSortRel |

| IteratorToFennelConverter |

| IterCalcRel |

| FennelToIteratorConverter |

| LhxJoinRel |

| IteratorToFennelConverter |

| IterCalcRel |

| FennelToIteratorConverter |

| LhxJoinRel |

| LcsRowScanRel |

| LcsRowScanRel |

| LcsRowScanRel |

+------------------------------------------------------+

0: jdbc:luciddb:>

0: jdbc:luciddb:> SELECT min(EMP.LNAME), min(DEPT.DNAME), LOCATION.CITY, avg(EMP.EMPNO),

. . . . . . . . > floor(avg(EMP.EMPNO))

. . . . . . . . > from EMP, DEPT, LOCATION

. . . . . . . . > where EMP.DEPTNO=DEPT.DEPTNO and DEPT.LOCID=LOCATION.LOCID

. . . . . . . . > group by LOCATION.CITY order by LOCATION.CITY;

+----------+--------------+----------------+---------+---------+

| EXPR$0 | EXPR$1 | CITY | EXPR$3 | EXPR$4 |

+----------+--------------+----------------+---------+---------+

| Brown | Support | Menlo Park | 105 | 105 |

| Fratz | Accounting | San Francisco | 104 | 104 |

| Andrews | Development | San Mateo | 106 | 106 |

+----------+--------------+----------------+---------+---------+

0: jdbc:luciddb:>