MySQL Bugs: #29866: Uninitialized TEXT NOT NULL (with no default value set) column behavoiur

Bug #29866	Uninitialized TEXT NOT NULL (with no default value set) column behavoiur
Submitted:	18 Jul 2007 9:44	Modified:	18 Aug 2007 10:51
Reporter:	Maciej Dobrzanski	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	BLOB, empty string, null, text

Description:
During some work with MySQL I've encountered strange, inconsistent behaviour in treating uninitialized TEXT NOT NULL column that has no default values defined.

How to repeat:
CREATE TABLE `t6` (
  `t` text NOT NULL
) ENGINE=MyISAM;

mysq> insert into t6 () values ();

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1364 | Field 't' doesn't have a default value | 
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

I know, but I choose to ignore that warning.

mysql> SELECT * FROM t6;
+---+
| t |
+---+
|   | 
+---+
1 row in set (0.00 sec)

mysql> SELECT RIGHT(t,4) FROM t6;    
+------------+
| RIGHT(t,4) |
+------------+
|            | 
+------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(t) FROM t6;         
+-----------+
| LENGTH(t) |
+-----------+
|         0 | 
+-----------+
1 row in set (0.00 sec)

mysql> SELECT COALESCE(t) FROM t6;
+-------------+
| COALESCE(t) |
+-------------+
|             | 
+-------------+
1 row in set (0.00 sec)

So it's not NULL, but then:

mysql> SELECT CONCAT(t) FROM t6;           
+-----------+
| CONCAT(t) |
+-----------+
| NULL      | 
+-----------+
1 row in set (0.00 sec)

And what's even more interesting:

mysql> SELECT IF(0,CONCAT('x', t),t) FROM t6; 
+------------------------+
| IF(0,CONCAT('x', t),t) |
+------------------------+
| NULL                   | 
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT IF(0,1,t) FROM t6;              
+-----------+
| IF(0,1,t) |
+-----------+
|           | 
+-----------+
1 row in set (0.00 sec)

mysql> SELECT IF(0,CONCAT('x'),t) FROM t6; 
+---------------------+
| IF(0,CONCAT('x'),t) |
+---------------------+
|                     | 
+---------------------+
1 row in set (0.00 sec)

5.1.20 does not seem to be affected by this.

I've just remembered that TEXT/BLOB columns can't have default values. So I created this struecture:

mysql> CREATE TABLE `t6` (`t` text NULL) ENGINE=MyISAM;    
Query OK, 0 rows affected (0.00 sec)

And inserted there an empty string (which is in my opinion definitely non-null)

mysql> insert into t6 values ('');         
Query OK, 1 row affected (0.00 sec)

mysql> SELECT t FROM t6;                   
+------+
| t    |
+------+
| NULL | 
+------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(t) FROM t6;           
+-----------+
| CONCAT(t) |
+-----------+
| NULL      | 
+-----------+
1 row in set (0.00 sec)

Thank you for the report.

I can not repeat described behaviour.

Please provide your configuration file.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".