David> Hi!
David>
David> When you have a indexed field that allows NULL (as per mysql >= 3.23.2)
David> and there is more than one row that has null in that column,
David> then an equality comparison between that field and NULL returns true
David> if the index is used.
David>
David> The problem does not occur if the field is not indexed.
David> Comparisons with IS [NOT] NULL continue to function correctly.
David>
David> >How-To-Repeat:
David>
David> Simplest reproducing SQL:
David>
David> DROP TABLE IF EXISTS test_table;
David>
David> CREATE TABLE test_table (
David> indexed_field int default NULL,
David> KEY indexed_field (indexed_field)
David> ) TYPE=MyISAM;
David>
David> INSERT INTO test_table VALUES (NULL);
David> INSERT INTO test_table VALUES (NULL);
David>
David> select * from test_table where indexed_field=NULL;
David>
David> +---------------+
David> | indexed_field |
David> +---------------+
David> | NULL |
David> | NULL |
David> +---------------+
David> 2 rows in set (0.00 sec)
Here is a patch that fixes this problem. This will be in the 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 <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
---------------------------------------------------------------------
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
