Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Subselect Bug? |
Mon, Oct 8 2012 1:38 AM | Permanent Link |
Barry | 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 ************************************************************/ |
Mon, Oct 8 2012 4:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Mon, Oct 8 2012 2:15 PM | Permanent Link |
Barry | 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 |
This web page was last updated on Friday, April 26, 2024 at 06:09 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |