Re: Strange select query

On Wed, Sep 12, 2001 at 10:28:33AM +0200, Partyka Robert wrote:
> Hellow,
>
> I notice wired behavior of backend,
>
> for instance I've 3 tables TA, TB, TC with about 1k of records each,
>
> and I do something like that:
>
> select * from TA as a, TB as b, TC as c where
> position('some text' in a.textfield)>0 or
> (position('some text' in b.textfield)>0 and a.index=b.referencefield)
> or
> (position('some text' in c.textfield)>0 and a.index=b.referencefield
> and b.other_referencefield=c.index);
Are you sure that is what you want?
This should result in
(TB x TC x rows in TA matching clause 1) + (TC x rows in TA matching clause 2) + (rows in TA matching clause 3)
rows being returned. If 10 rows match clause 1 and 3 rows clause 2 and 1 row clause 3 that will make
(assuming all tables have 1000 records)
r = a + b + c
a = 1000 * 1000 * 10 = 10 000 000
b = 1000 * 3 = 3 000
c = 1 = 1

r = 10 003 001 rows being returned.

> the backend start to execute query, but it seems to be 'never
> ending story' because after 15 minutes of work I still haven't result,
> and backend still was eating my RAM. I know that this query is
> ugly and bad, but I think backend will reject such query at parsing.
>
The query is valid you should get more ram or rethink your query :-)

- Einar Karttunen