Issue Details (XML | Word | Printable)

Key: CORE-355
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Arno Brinkman
Reporter: fabianobonin
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Problem joining views

Created: 03/Dec/01 12:00 AM   Updated: 14/Jun/06 09:38 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 1.5.0

Time Tracking:
Not Specified

SF_ID: 488343


 Description  « Hide
SFID: 488343#
Submitted By: fabianobonin

/*
I had a problem today that i think it's very serious,
because it affects the result of aggregate fields and
the number of returned records, too.
When i join a view with a distinct clause, it returns
wrong results when there are aggregate fields.
The following script will show this: */

create table customers (
  id integer,
  name varchar(10) );

create table invoices (
  id integer,
  cust_id integer,
  code varchar(10),
  quantity integer );

create view v_customers as
select DISTINCT id, name from customers;

insert into customers values (1, 'Me');

insert into invoices values (1, 1, 'Inv.1', 5);
insert into invoices values (2, 1, 'Inv.2', 10);
insert into invoices values (3, 1, 'Inv.3', 15);
insert into invoices values (4, 1, 'Inv.4', 20);

/* table inner joining the table */
select sum(inv.quantity) from invoices inv inner join
customers cust on inv.cust_id = cust.id;
/* returns 50 */

/* table inner joining the view */
select sum(inv.quantity) from invoices inv inner join
v_customers cust on inv.cust_id = cust.id;
/* returns 20 !?!? */

/* table left joining the view */
select sum(inv.quantity) from invoices inv left join
v_customers cust on inv.cust_id = cust.id;
/* returns 50 */

/* view left joining the table */
select sum(inv.quantity) from v_customers cust left
join invoices inv on inv.cust_id = cust.id;
/* returns 20 !?!? */

/* all instructions should return 50 */

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:38 AM
Date: 2002-02-22 09:00
Sender: robocop
Logged In: YES
user_id=62823

This is probably a side effect of this older bug:
[ #224810 ] DISTINCT propagates outside a VIEW