List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 19 1999 3:11pm
Subject:IS NOT NULL for indexed column return empty result
View as plain text  
>>>>> "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
Thread
IS NOT NULL for indexed column return empty resulttommy18 Sep
  • IS NOT NULL for indexed column return empty resultMichael Widenius19 Sep