Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problem joining views [CORE355] #695

Closed
firebird-automations opened this issue Dec 3, 2001 · 3 comments
Closed

Problem joining views [CORE355] #695

firebird-automations opened this issue Dec 3, 2001 · 3 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: fabianobonin (fabianobonin)

Assigned to: @ArnoBrinkman

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 = http://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 = http://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 = http://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 = http://cust.id;
/* returns 20 !?!? */

/* all instructions should return 50 */

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10379 ] => Firebird [ 14635 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant