You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
/*
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 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 */
The text was updated successfully, but these errors were encountered:
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 */
The text was updated successfully, but these errors were encountered: