Hi!
>>>>> "Bradley" == Bradley Baetz <bbaetz@stripped> writes:
Bradley> On Wed, 17 Oct 2001, Michael Widenius wrote:
bbaetz> SELECT bugs.bug_id FROM bugs LEFT JOIN cc ON (bugs.bug_id =
bbaetz> cc.bug_id AND cc.who = 2) WHERE (bugs.reporter = 2 OR cc.who =
bbaetz> 2);
>>
Bradley> <snip>
>> In other words:
>>
>> The The cc.who = 2 restriction in the ON clause does NOT affect the
>> rows that is considered from the LEFT table. It only affects if the
>> rows should be considered from the RIGHT table or if we should
>> generate a NULL row for the right table.
>>
>> Fix: Move all tests that only affects the 'cc' table from the ON
>> clause to the WHERE part..
Bradley> I realise this, and want the behaviour, since I still want bugs with no
Bradley> people cc'd to match if the reporter matches.
Bradley> However, the above query _does_ have the restriction in the WHERE part (as
Bradley> well as the on part - this is a simplified version of a more complicated,
Bradley> partly autogenerated, query). And removing the index changes the result,
Bradley> which seems really strange. Also, if you change the index on the cc table
Bradley> to be unique(bug_id,who), then the result changes based on whether or not
Bradley> the who column is NOT NULL.
Here is a patch that fixes this problem and also the field=NULL
problem that was reported yesterday. This will be in next MySQL
release (both 3.23.44 and 4.0.1)
===== sql/sql_select.cc 1.105 vs edited =====
*** /tmp/sql_select.cc-1.105-21548 Thu Sep 27 22:02:37 2001
--- edited/sql/sql_select.cc Fri Oct 19 21:44:30 2001
***************
*** 4963,4977 ****
static bool test_if_ref(Item_field *left_item,Item *right_item)
{
Field *field=left_item->field;
! if (!field->table->const_table) // No need to change const test
{
Item *ref_item=part_of_refkey(field->table,field);
if (ref_item && ref_item->eq(right_item))
{
if (right_item->type() == Item::FIELD_ITEM)
! return (field->eq_def(((Item_field *) right_item)->field) &&
! !field->table->maybe_null);
! if (right_item->const_item())
{
// We can remove binary fields and numerical fields except float,
// as float comparison isn't 100 % secure
--- 4963,4978 ----
static bool test_if_ref(Item_field *left_item,Item *right_item)
{
Field *field=left_item->field;
! // No need to change const test. We also have to keep tests on LEFT JOIN
! if (!field->table->const_table && !field->table->maybe_null)
{
Item *ref_item=part_of_refkey(field->table,field);
if (ref_item && ref_item->eq(right_item))
{
if (right_item->type() == Item::FIELD_ITEM)
! return (field->eq_def(((Item_field *) right_item)->field));
! if (right_item->const_item() &&
! (right_item->val_int() || !right_item->null_value))
{
// We can remove binary fields and numerical fields except float,
// as float comparison isn't 100 % secure
Both problems was related to the following optimization:
If you use the following construct in MySQL:
WHERE index_column=constant
and MySQL internally uses 'constant' to look up the field through an
index (a 'ref' key), then MySQL will remove the
'index_column=constant' expression from the WHERE clause, because the
test will implicitly done by the index lookup function.
This optimization doesn't however work when you are using a LEFT JOIN
or when comparing with = NULL.
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <monty@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
