Hello.
On Sun, Feb 18, 2001 at 07:43:25PM -0600, [EMAIL PROTECTED] wrote:
[...]
> When I execute the following query:
>
> select acts.resolution, title from acts, acts_text
> where acts.resolution=acts_text.resolution and
> words like "% china %" or words like "% japan %";
>
> I get what appears to be all the rows in the acts table repeated by a
> factor of 10; i.e. there are 2000 rows in the table, and I'm getting a
> result containing 20,000 rows.
>
>
> However, when I change the query slightly by putting () around the or
> (which is what I intended anyway) as in
>
[...]
> I get exactly the correct results. I understand that without the () the
> Boolean isn't being processed the way I intended, but what I don't
> understand is why every row in the table is being returned 10 times over,
> especially since most of these rows don't contain the words china or
> japan.
Simply look what MySQL executes if you drop the parens... I emphasize
this by setting other parens:
SELECT acts.resolution, title FROM acts, acts_text
WHERE (acts.resolution=acts_text.resolution AND words LIKE "% china %")
OR words LIKE "% japan %";
You see? The left part finds all rows you intended, matching the word
china. But the right side doesn't even enforce the join constraint
(acts.resolution=acts_text.resolution).
Therefore the second part produces the cartesian product of both
tables (i.e. (rows of acts) times (rows of acts_text) rows), only
restricted to the word 'japan' in one of the tables.
A simple example (I simplified your structure):
table acts
resolution title
---------------------
1 one
2 two
3 three
table acts_text
resolution words
---------------------
1 china
2 japan
3 china
The right part of the OR condition will create the cartesian product
acts.resolution acts.title acts_text.resolution acts_text.words
1 one 1 china
1 one 2 japan
1 one 3 china
2 two 1 china
2 two 2 japan
2 two 3 china
3 three 1 china
3 three 2 japan
3 three 3 china
Now, the OR condition selects rows 1,3,4,6,7 and 9, because all lists
acts_text.words including 'china'. This way you get title "one" and
"three" several times (row 1 and 3, row 7 and 9) and get title "two"
which shouldn't be listed (by row 4 and 6).
Therefore you will get the following end result (ignoring the left
part of the OR clause for a moment).
acts.resolution acts.title
1 one
1 one
2 two
2 two
3 three
3 three
which looks quite like what you described.
I hope, this explanation was understandable.
Bye,
Benjamin.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
