Thread: Too many function calls in view with LEFT JOIN

Hello!

If a view which calls a function is LEFT JOINed to a table but not all
result rows are matched by some criteria, then the function is called
for each row of the view nevertheless.

It is interesting, that the same query without using a view calls the
function only for those rows wich are really in the result set.

This discrepancy is of course Not-A-Good-Thing(tm) if the function has side
effects or is very expensive.

Note that this seems to happen only for left joins, not for a inner join.


The following example illustrates this by using a "noisy" function.

-----------------------------------------------
CREATE TABLE t1 (id int, t1val text);
CREATE TABLE t2 (id int, t2val int);

-- insert some test values
INSERT INTO t1 SELECT i, 'foo bar ' || i FROM generate_series(0, 20) i;
INSERT INTO t2 SELECT i, i*i FROM generate_series(0, 20) i;

-- create a noisy function
CREATE OR REPLACE FUNCTION notice(id int, val int) RETURNS int AS $$
BEGIN
    RAISE NOTICE 'function called for (%, %)', id, val;
    RETURN id;
END;
$$ LANGUAGE plpgsql;


-- direct query
SELECT t1.*, t2.*, notice(t2.id, t2.t2val)
    FROM t1 LEFT JOIN t2 USING (id) WHERE id < 10;
-- result: only 10 NOTICE messages


-- same query with a view
CREATE VIEW t2v AS SELECT *, notice(id, t2val) FROM t2;
SELECT t1.*, t2v.* FROM t1 LEFT JOIN t2v USING (id) WHERE id < 10;
-- result: 20 NOTICE messages, 10 to much

-----------------------------------------------

I hope, this is really a bug and not something I didn't understand :-)

Best Regards
Andreas Heiduk

______________________________________________________________
Verschicken Sie romantische, coole und witzige Bilder per SMS!
Jetzt bei WEB.DE FreeMail: http://f.web.de/?mc=021193

Andreas Heiduk <Andreas.Heiduk@web.de> writes:
> If a view which calls a function is LEFT JOINed to a table but not all
> result rows are matched by some criteria, then the function is called
> for each row of the view nevertheless.

> Note that this seems to happen only for left joins, not for a inner join.

I believe that's because the column is required to go to NULL in an
unjoined row.  With a non-strict function, evaluating it after the join
could yield wrong answers.  Try making the function strict.

            regards, tom lane

Andreas Heiduk <Andreas.Heiduk@web.de> writes:
> But as far as I can tell both queries should always return the same
> results. So I don't understand why the STRICT does not matter in the
> first query but is necessary in the second one. Especially because the
> JOIN criterium is not affected by the function call.

Because if the function's not strict, you don't get the right answer
after flattening the join.  If we postpone the function call until after
the join, then we have a query that looks like

    select x.f1, x.f2, ..., myfunc(y.f3), ... from x left join y ...

The LEFT JOIN operator will produce y.f3 = null in join rows that are
generated from unmatched x rows.  If myfunc is not strict, it could
produce a non-null result despite being fed a null argument, and then
you would see wrong results from the SELECT: a column that ought to be
null is not.

The planner knows that it can postpone evaluation of strict functions in
this sort of context, but it won't risk it for non-strict.  This goes
back to this bug report:
    http://archives.postgresql.org/pgsql-bugs/2001-04/msg00223.php
which is exactly parallel to your query if you imagine a constant as
being like a function of no arguments.

I have some thoughts about changing this, but it's a major planner
re-engineering project not a bug fix.  Don't hold your breath.

            regards, tom lane

Hello!


Tom Lane <tgl@sss.pgh.pa.us> wrote
>
> Andreas Heiduk <Andreas.Heiduk@web.de> writes:
> > If a view which calls a function is LEFT JOINed to a table but not all
> > result rows are matched by some criteria, then the function is called
> > for each row of the view nevertheless.
>
> > Note that this seems to happen only for left joins, not for a inner join.
>
> I believe that's because the column is required to go to NULL in an
> unjoined row.  With a non-strict function, evaluating it after the join
> could yield wrong answers.  Try making the function strict.

First: setting the function to STRICT really cuts down the number of function calls even with the SELECT via view.

But as far as I can tell both queries should always return the same results. So I don't understand why the STRICT does
notmatter in the first query but is necessary in the second one. Especially because the JOIN criterium is not affected
bythe function call. 


Oh, and sorry that I forgot the version: "PostgreSQL 8.1.4 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.4
20060507(prerelease) (Debian 4.0.3-3)" 

Best regards,
Andreas Heiduk


______________________________________________________________________
XXL-Speicher, PC-Virenschutz, Spartarife & mehr: Nur im WEB.DE Club!
Jetzt gratis testen! http://freemail.web.de/home/landingpad/?mc=021130