Bug #30945 not_null_flag set but results contain null

• Submitted: 11 Sep 2007 7:11
• Modified: 11 Sep 2007 13:35
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 6.0.2
• OS: Any
• CPU Architecture: Any

[11 Sep 2007 7:11] Shane Bester

Description:
6.0.2 returns NOT_NULL flag in metadata, but there are NULL's in the resultset!

On 6.0.2-alpha:
----------------
mysql> select instr(null,null);
Field   1:  `instr(null,null)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+------------------+
| instr(null,null) |
+------------------+
|             NULL |
+------------------+
1 row in set (0.00 sec)

mysql> select * from (select instr(null,null))a;
ERROR 1048 (23000): Column 'instr(null,null)' cannot be null
mysql>

On 5.0.48:
----------

mysql> select instr(null,null);
Field   1:  `instr(null,null)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      BINARY NUM

+------------------+
| instr(null,null) |
+------------------+
|             NULL |
+------------------+
1 row in set (0.00 sec)

mysql> select * from (select instr(null,null))a;
Field   1:  `instr(null,null)`
Catalog:    `def`
Database:   ``
Table:      `a`
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM

+------------------+
| instr(null,null) |
+------------------+
|             NULL |
+------------------+
1 row in set (0.00 sec)

How to repeat:
on 6.0.2 run this:

select instr(null,null);
select * from (select instr(null,null))a;

Suggested fix:
.

[11 Sep 2007 7:16] MySQL Verification Team

same problem with: select locate(null,null);

[11 Sep 2007 7:26] MySQL Verification Team

same problem with: select position(null in null);

this bug can lead to wrong number of rows returned in group by, for example.

[11 Sep 2007 13:35] MySQL Verification Team

closing as it appears 6.0.3-BK behaves correctly.  Bug has been fixed somewhere.