Bug #9425 A user variable doesn't always have implicit coercibility
Submitted: 28 Mar 2005 6:35 Modified: 1 Apr 2005 23:09
Reporter: Alexander Barkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1, 5.0 OS:
Assigned to: Alexander Barkov CPU Architecture:Any

[28 Mar 2005 6:35] Alexander Barkov
Description:
Results of some tests for 4.1.11 and 5.0.3:

mysql> select @v, coercibility(@v);
+------+------------------+
| @v   | coercibility(@v) |
+------+------------------+
| NULL |             NULL |
+------+------------------+
1 row in set (0.00 sec)

mysql> set @v = null;
Query OK, 0 rows affected (0.00 sec)

mysql> select @v, coercibility(@v);
+------+------------------+
| @v   | coercibility(@v) |
+------+------------------+
| NULL |                2 |
+------+------------------+
1 row in set (0.00 sec)

Here we check the coercibility for a variable that is NULL because it
has never been set, and for a variable that has been set to NULL explicitly.
The results are different.

Shouldn't the two results be the same?  Or are these two NULL values
different in character somehow?

mysql> set @v = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> select @v, coercibility(@v);
+------+------------------+
| @v   | coercibility(@v) |
+------+------------------+
| 1    |                1 |
+------+------------------+
1 row in set (0.00 sec)

Variables that are set to a numeric value do not have IMPLICIT
coercibility.  (This means that the "always IMPLICIT" principle is
not true, unless this result is a bug.)

mysql> set @v = now();
Query OK, 0 rows affected (0.00 sec)

mysql> select @v, coercibility(@v);
+---------------------+------------------+
| @v                  | coercibility(@v) |
+---------------------+------------------+
| 2005-03-01 08:41:18 |                2 |
+---------------------+------------------+
1 row in set (0.00 sec)

Okay, this is IMPLICIT ... presumably because the temporal value is converted
to a string when stored in the user variable?

mysql> set @v = binary 'a';
Query OK, 0 rows affected (0.00 sec)

mysql> select @v, coercibility(@v);
+------+------------------+
| @v   | coercibility(@v) |
+------+------------------+
| a    |                2 |
+------+------------------+
1 row in set (0.01 sec)

mysql> set @v = 'a';
Query OK, 0 rows affected (0.00 sec)

mysql> select @v, coercibility(@v);
+------+------------------+
| @v   | coercibility(@v) |
+------+------------------+
| a    |                2 |
+------+------------------+
1 row in set (0.00 sec)

Okay, these are IMPLICIT, too.  I take it that if the value assigned
to the variable is a string, it doesn't matter whether it's a binary
or non-binary string, or what the collation is for a non-binary string?
(That is, is it the case that *any* kind of string assigned to a user
variable results in the variable having IMPLICIT coercibility?)

How to repeat:
Runt the above queries.
[28 Mar 2005 9:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23407
[1 Apr 2005 23:09] Paul Dubois
Noted in 4.1.11, 5.0.4 changelogs.