Bug #832 left join and union with null fields making nulls from some fields

• Submitted: 11 Jul 2003 4:13
• Modified: 11 Jul 2003 6:38
• Reporter: Tomasz Dubinski
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.13
• OS: Linux (linux-intel)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[11 Jul 2003 4:13] Tomasz Dubinski

Description:
When in select there is a column from table which was left joined (and this field can contain null if there was no rows selected in joined table) and that column was declared as not null, this and every next field contain ''. That bug requires that there must be at least two selects joined with union.

How to repeat:
CREATE TABLE tab1 (
  sid int(11) NOT NULL default '0',
  nazwa char(10) NOT NULL default '',
  PRIMARY KEY  (sid)
) TYPE=MyISAM;

CREATE TABLE tab2 (
  id int(11) NOT NULL default '0',
  link int(11) default NULL,
  bubu char(10) NOT NULL default '',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

INSERT INTO tab2 VALUES (1,2,'keke');

SELECT bubu, nazwa, bubu 
FROM tab2 LEFT JOIN tab1 ON sid = link
WHERE id=1 
UNION
SELECT 'bu', null, 'bu';

This produces:
+------+-------+------+
| bubu | nazwa | bubu |
+------+-------+------+
| keke |       |      |
| bu   |       |      |
+------+-------+------+
instead of:
+------+-------+------+
| bubu | nazwa | bubu |
+------+-------+------+
| keke | NULL  | keke |
| bu   | NULL  | bu   |
+------+-------+------+

[11 Jul 2003 6:38] MySQL Verification Team

4.0.14 works just fine for me:

bubu    nazwa   bubu
keke    NULL    keke
bu      NULL    bu