View returns erroneous result for squared average function as compared to query

[monetdb-team]

Date: 2011-03-10 14:15:33 +0100
From: @bartscheers
To: SQL devs <>
Version: 2.40.1 (Oct2010) [obsolete]
CC: @njnes

Last updated: 2011-03-28 17:31:32 +0200

Comment 15562

Date: 2011-03-10 14:15:33 +0100
From: @bartscheers

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-GB; rv:1.9.2.14) Gecko/20110301 Fedora/3.6.14-1.fc14 Firefox/3.6.14
Build Identifier:

This bug might be related to 2739.

Using version Oct2010-SP1.
When executing a query with avg(d)*avg(d) the results are correct, but when this is created within a view, the results are wrong.

Reproducible: Always

Steps to Reproduce:

1. create table t (d double);
2. insert into d values (2),(3);
3. select avg(d) as avg_d, avg(d*d) as avg_d2, avg(d)*avg(d) as avg2_d_mult from t;
   this gives the expected results.
4. create view tv as select avg(d) as avg_d, avg(d*d) as avg_d2, avg(d)*avg(d) as avg2_d_mult from t;
   this gives for avg2_d_mult the wrong results.

Actual Results:

View returns erroneous value.

Expected Results:

Correct result

Tried a workaround by using power(avg(d),2) but this did also not work.

1. select avg(d) as avg_d, avg(d*d) as avg_d2, power(avg(d),2) as avg2_d_pow from t;
2. create view tv1 as select avg(d) as avg_d, avg(d*d) as avg_d2, power(avg(d),2) as avg2_d_pow from t;
3. select * from tv1;
   TypeException:user.s0_1[48]:'mmath.pow' undefined in: _60:any := mmath.pow(_29:wrd, _59:dbl)
   SQLException:SQLengine:Program contains errors

Comment 15605

Date: 2011-03-18 16:42:53 +0100
From: @njnes

added test view_avg_incorrect_result.Bug-2790.sql
fixed by using unique label numbers

Comment 15630

Date: 2011-03-28 17:31:32 +0200
From: @sjoerdmullender

The Mar2011 version has been released.