I have a problem with Subselects returning the same value even though they have different Where clauses.It seems to be ignoring part of the Where clause that has "texp2.expense_code='LNC".

If I omit the "/sum(income)" it returns proper values. It appears to me EDB thinks both subselects are the same and will get the value for the 2nd subselect from the cache of the 1st subselect.

I don't see anything wrong with the SQL statement. Can you?

I have submitted a couple of simple tables and an SQL statement for someone to try out if they have the time.  It is either an SQL bug or I'm missing something that I'm not seeing. I'd like someone to verify it.

TIA
Barry
v2.10

select
sum((select expense_amount from Expenses texp  where texp.cust_id=tc.cust_id   and texp.expense_code='PRK')) / sum(income)  as Avg_PRK
,sum((select expense_amount from Expenses texp2 where texp2.cust_id=tc.cust_id  and texp2.expense_code='LNC'))/ sum(income)  as Avg_LNC
from customers tc

Result Set:
---------------
Avg_PRK         Avg_LNC
4.78333333      4.78333333


/************************************************************
*
* ElevateDB Reverse-Engineered script for the
* EDBBugs database
*
* Generated on 10/8/2012 0:20:53
* By the user Administrator
*
************************************************************/

SCRIPT
BEGIN

/************************************************************
* Tables
************************************************************/

EXECUTE IMMEDIATE 'CREATE TABLE "Expenses"
(
"Cust_Id" GUID,
"Expense_Code" VARCHAR(3) COLLATE "UNI",
"Expense_Amount" FLOAT
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'CREATE TABLE "Customers"
(
"Cust_Id" GUID DEFAULT Current_GUID()  NOT NULL,
"Street1" VARCHAR(30) COLLATE "UNI",
"Street2" VARCHAR(30) COLLATE "UNI",
"Income" FLOAT DEFAULT 0

)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';


/************************************************************
* Table Rows
************************************************************/

EXECUTE IMMEDIATE 'INSERT INTO "Expenses" VALUES (''{5A5A15AE-4058-4609-93B0-F8EA1382168D}'',
                 ''PRK'',
                 4.35)';

EXECUTE IMMEDIATE 'INSERT INTO "Expenses" VALUES (''{5A5A15AE-4058-4609-93B0-F8EA1382168D}'',
                 ''LNC'',
                 100)';

EXECUTE IMMEDIATE 'INSERT INTO "Expenses" VALUES (''{482865E7-CAEC-4ED3-A77A-55A8795F6B3F}'',
                 ''PRK'',
                 10)';

EXECUTE IMMEDIATE 'INSERT INTO "Customers" VALUES (''{5A5A15AE-4058-4609-93B0-F8EA1382168D}'',
                 ''123 Main Street'',
                 ''Apt 100'',
                 1)';

EXECUTE IMMEDIATE 'INSERT INTO "Customers" VALUES (''{482865E7-CAEC-4ED3-A77A-55A8795F6B3F}'',
                 ''500 Park Lane'',
                 ''#22'',
                 2)';


/************************************************************
* Views
************************************************************/


/************************************************************
* Functions
************************************************************/


/************************************************************
* Procedures
************************************************************/


/************************************************************
* Table triggers, indexes, and constraints
************************************************************/

END

/************************************************************
* End of generated SQL
************************************************************/

Barry


As you've found its actually the division that's causing the problem. Without it the sum of expenses for each code is correct. Event dividing by a constant still gives the wrong answer. However, moving the sum(income) "inboard" works

select

sum(select expense_amount / (SELECT sum(income) FROM CUSTOMERS) from Expenses texp  where texp.cust_id=tc.cust_id   and texp.expense_code='PRK')
,
sum(select expense_amount / (SELECT sum(income) FROM CUSTOMERS) from Expenses texp2 where texp2.cust_id=tc.cust_id  and texp2.expense_code='LNC')

from customers tc


The parser probably just gets confused - I know I was

I suggest posting as a bug.

Roy Lambert [Team Elevate]

Roy Lambert wrote:
>As you've found its actually the division that's causing the problem. Without it the sum of expenses for each code is >correct. Event dividing by a constant still gives the wrong answer. However, moving the sum(income) "inboard" works

Thanks for confirming it. I emailed Tim this morning and he will have a fix for it in 2.11 due out later today.
(I wonder if Oracle fixes bugs that fast?)

Barry