>>>>> "tommy" == tommy <tommy@stripped> writes:
>> Description:
tommy> IS NOT NULL for indexed column return empty result.
tommy> For non-indexed column, it work fine.
>> How-To-Repeat:
mysql> create table test (id int not null, str char(10), index(str));
tommy> Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values (1, null), (2, null), (3, "foo"), (4, "bar");
tommy> Query OK, 4 rows affected (0.02 sec)
tommy> Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test;
tommy> +----+------+
tommy> | id | str |
tommy> +----+------+
tommy> | 1 | NULL |
tommy> | 2 | NULL |
tommy> | 3 | foo |
tommy> | 4 | bar |
tommy> +----+------+
tommy> 4 rows in set (0.02 sec)
mysql> select * from test where str is not null;
tommy> Empty set (0.03 sec)
mysql> drop index str on test;
tommy> Query OK, 4 rows affected (0.02 sec)
tommy> Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test where str is not null;
tommy> +----+------+
tommy> | id | str |
tommy> +----+------+
tommy> | 3 | foo |
tommy> | 4 | bar |
tommy> +----+------+
tommy> 2 rows in set (0.03 sec)
>> Fix:
tommy> I don't know.
Better fix:
(/my/monty/sql) diff -c /my/monty/master/mysql-3.23.3-alpha/sql/opt_range.cc .
*** /my/monty/master/mysql-3.23.3-alpha/sql/opt_range.cc Mon Aug 30 00:03:06 1999
--- ./opt_range.cc Sun Sep 19 18:08:48 1999
***************
*** 958,964 ****
--- 958,967 ----
if (!tree)
DBUG_RETURN(0);
if (type == Item_func::ISNOTNULL_FUNC)
+ {
tree->min_flag=NEAR_MIN; /* IS NOT NULL -> X > NULL
*/
+ tree->max_flag=NO_MAX_RANGE;
+ }
DBUG_RETURN(tree);
}
Regards,
Monty
