Re: bzr commit into mysql-5.1-bugteam branch (v.narayanan:3197) Bug#47762

[Sergei Golubchik]

Hi, Kevin!

On Nov 04, Kevin Lewis wrote:
> V Narayanan,
>
> I have been trying to debug this recently as a way to start understanding 
> the server code.  While I was looking at it, it was assigned to you and you 
> seem to have a fix that works.  I agree that the possible -1 result of 
> item->save_in_field() needs to be passed through store_val_in_field(), just 
> like it is in  Item::save_in_field_no_warnings().

I'm not quite sure about it. It uses CHECK_FIELD_IGNORE, so apparently
there could be no warning or failure here.

Looking at it I found that CHECK_FIELD_IGNORE was treated incorrectly,
this is a separate bug now #48525.

But assuming that matching_cond() is fixed to use
CHECK_FIELD_ERROR_FOR_NUL, the error should be expected, right.

...
> All that analysis above was done as much for my benefit as yours,
> since I'm sure you saw all that. But it is interesting to note that
> the failure to find any matching records was based on the fact that
> the field was a NOT NULL field.  I wondered what would happen if the
> key was not a primary key and was nullable.

Exactly!
That's my main problem with the proposed fix. It simply doesn't feel
right that a correct result relies on the field being declared declared
NOT NULL. After all the field can be declared nullable too - strangely
enough the result will be correct there :-[]

> The following test show mostly the correct result, but 
> they get there by different paths.

Right

> However, WHERE a > NULL does not work so well. . .

Cool, thanks. I didn't get that far :)
stopped after trying nullable field = NULL.

> mysql> CREATE TABLE t2 (a int(11), key k2 (a)) engine innod
> Query OK, 0 rows affected (0.08 sec)
>
> mysql> INSERT INTO t2 VALUES (1),(2);
> Query OK, 2 rows affected (0.03 sec)
> Records: 2  Duplicates: 0  Warnings: 0
>
> mysql> SELECT a FROM t2 WHERE a = NULL;
> Empty set (0.00 sec)
>
> mysql> SELECT MIN(a) FROM t2 WHERE a = NULL;
> +--------+
> | MIN(a) |
> +--------+
> |   NULL |
> +--------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT a FROM t2 WHERE a <> NULL;
> Empty set (0.00 sec)
>
> mysql> SELECT MIN(a) FROM t2 WHERE a <> NULL;
> +--------+
> | MIN(a) |
> +--------+
> |   NULL |
> +--------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT a FROM t2 WHERE a > NULL;
> Empty set (0.00 sec)
>
> mysql> SELECT MIN(a) FROM t2 WHERE a > NULL;
> +--------+
> | MIN(a) |
> +--------+
> |      1 |
> +--------+
> 1 row in set (0.00 sec)

It's a bug.

> mysql> SELECT a FROM t2 WHERE a < NULL;
> Empty set (0.00 sec)
>
> mysql> SELECT MIN(a) FROM t2 WHERE a < NULL;
> +--------+
> | MIN(a) |
> +--------+
> |   NULL |
> +--------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT a FROM t2 WHERE a between NULL and 10;
> Empty set (0.00 sec)
>
> mysql> SELECT MIN(a) FROM t2 WHERE a between NULL and 10;
> +--------+
> | MIN(a) |
> +--------+
> |   NULL |
> +--------+
> 1 row in set (0.00 sec)
>
> I hope my analysis has been helpful to you.

Yes, very.
Oh, well, more debugging...

Regards / Mit vielen Grüßen,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
/_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München 161028
       <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Häring