You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
droptable if exists t;
createtablet(a char(20), index idx_a(a));
The following two queries result in the same explain result but they represent different DML queries:
descselect*from t where a ="<nil>";
descselect*from t where a is null;
TiDB(localhost:4000) >descselect*from t where a ="<nil>";
+-------------------+------+---------------------------------------------------------+-------+
| id | task | operator info | count |
+-------------------+------+---------------------------------------------------------+-------+
| IndexReader_9 | root | index:IndexScan_8 | 10.00 |
| └─IndexScan_8 | cop | table:t, index:a, range:[<nil>,<nil>], keep order:false | 10.00 |
+-------------------+------+---------------------------------------------------------+-------+2 rows inset (0.00 sec)
TiDB(localhost:4000) >descselect*from t where a is null;
+-------------------+------+---------------------------------------------------------+-------+
| id | task | operator info | count |
+-------------------+------+---------------------------------------------------------+-------+
| IndexReader_9 | root | index:IndexScan_8 | 10.00 |
| └─IndexScan_8 | cop | table:t, index:a, range:[<nil>,<nil>], keep order:false | 10.00 |
+-------------------+------+---------------------------------------------------------+-------+2 rows inset (0.00 sec)
Actually no mater what string we use to represent NULL, there is a chance that a is null and a = xx can result in the same range representation once the string we use to represent is equal to xx.
A way to solve this problem is to wrap strings with ", we can use the string "NULL" to represent NULL, which means we can distinguish NULL from other strings:
TiDB(localhost:4000) >descselect*from t where a ="NULL";
+-------------------+------+---------------------------------------------------------+-------+
| id | task | operator info | count |
+-------------------+------+---------------------------------------------------------+-------+
| IndexReader_9 | root | index:IndexScan_8 | 10.00 |
| └─IndexScan_8 | cop | table:t, index:a, range:["NULL","NULL"], keep order:false | 10.00 |
+-------------------+------+---------------------------------------------------------+-------+2 rows inset (0.00 sec)
TiDB(localhost:4000) >descselect*from t where a is null;
+-------------------+------+---------------------------------------------------------+-------+
| id | task | operator info | count |
+-------------------+------+---------------------------------------------------------+-------+
| IndexReader_9 | root | index:IndexScan_8 | 10.00 |
| └─IndexScan_8 | cop | table:t, index:a, range:[NULL,NULL], keep order:false | 10.00 |
+-------------------+------+---------------------------------------------------------+-------+2 rows inset (0.00 sec)
The text was updated successfully, but these errors were encountered:
let's say we have the following table:
The following two queries result in the same explain result but they represent different DML queries:
Actually no mater what string we use to represent NULL, there is a chance that
a is null
anda = xx
can result in the same range representation once the string we use to represent is equal to xx.A way to solve this problem is to wrap strings with
"
, we can use the string"NULL"
to represent NULL, which means we can distinguish NULL from other strings:The text was updated successfully, but these errors were encountered: