Thread: Strange select query

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);

the backend start to execute query, but it seems to be 'never ending
story' because after 15 minutes of work I steel 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.

regards
Robert Partyka

bobson@alpha.pl 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);
>
>the backend start to execute query, but it seems to be 'never ending
>story' because after 15 minutes of work I steel 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.

It seems to me that the query is valid, but is basically doing a
cross join or cartesian product join on 3 tables. This means all
combinations of rows from all 3 tables needs to be looked at. With
1000 rows per table, that is 1,000,000,000 rows that the database
needs to look at to see if it matches your other conditions.