Bug #42533 SELECT shows only 'null',when various values inserted to UNIQUE(a),with ibmdb2i
Submitted: 2 Feb 2009 6:44 Modified: 5 Aug 2009 8:05
Reporter: Nirbhay Choubey Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DB2SE for IBM i Severity:S3 (Non-critical)
Version:5.1.30 OS:Other (IBMi 5.4)
Assigned to: Nirbhay Choubey CPU Architecture:Any

[2 Feb 2009 6:44] Nirbhay Choubey
Description:
Follow the statements : 

With ibmdb2i storage engine :

mysql> create table t1 ( a int, unique(a));
Query OK, 0 rows affected (0.18 sec)

mysql> insert into t1 values (4),(5),(null),(1),(7),(null);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t1;

#Only 'null' values are returned by select statement.

+------+     
| a    |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.14 sec)

--------
With MyISAM storage engine:

mysql> create table t1 ( a int, unique(a)) engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (4),(5),(null),(1),(7),(null);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
| NULL |
| NULL |
|    1 |
|    4 |
|    5 |
|    7 |
+------+
6 rows in set (0.00 sec)

How to repeat:
See the above description;
[13 May 2009 7:11] Nirbhay Choubey
Fixed by IBM.
Verified with mysql-5.1.34 on IBMi 5.4 and 6.1
[5 Aug 2009 8:05] Sanjay Manwani
Seems this patch was committed without specifying the bug maybe as a part of another fix.
Since it is not reproducible now and there should not be any Doc impact closing this bug.