Description:
When I try to union two tables, it seems to replace "null" with 0's in columns of type "int".
How to repeat:
-- MySQL dump 9.09
--
-- Host: localhost Database: test
---------------------------------------------------------
-- Server version 4.0.15-standard
--
-- Table structure for table `bar`
--
CREATE TABLE bar (
id int(4) default NULL,
date date default NULL,
bar_data int(4) default NULL
) TYPE=MyISAM;
--
-- Dumping data for table `bar`
--
INSERT INTO bar VALUES (1,'2001-01-01',1);
INSERT INTO bar VALUES (1,'2002-02-02',2);
INSERT INTO bar VALUES (2,'2002-02-01',22);
--
-- Table structure for table `foo`
--
CREATE TABLE foo (
id int(4) default NULL,
date date default NULL,
foo_data int(4) default NULL
) TYPE=MyISAM;
--
-- Dumping data for table `foo`
--
INSERT INTO foo VALUES (1,'2001-01-02',3);
INSERT INTO foo VALUES (1,'2002-02-05',25);
INSERT INTO foo VALUES (2,'2001-02-22',15);
Ordinarily I would run the following query:
mysql> select id, date, null as foo_data, bar_data from bar
-> union
-> select id, date, foo_data, null as bar_data from foo;
hoping for the following table:
+------+------------+----------+----------+
| id | date | foo_data | bar_data |
+------+------------+----------+----------+
| 1 | 2001-01-01 | NULL | 1 |
| 1 | 2002-02-02 | NULL | 2 |
| 2 | 2002-02-01 | NULL | 22 |
| 1 | 2001-01-02 | 3 | NULL |
| 1 | 2002-02-05 | 25 | NULL |
| 2 | 2001-02-22 | 15 | NULL |
+------+------------+----------+----------+
6 rows in set (0.00 sec)
But I know that this would actually result in
+------+------------+----------+----------+
| id | date | foo_data | bar_data |
+------+------------+----------+----------+
| 1 | 2001-01-01 | NULL | 1 |
| 1 | 2002-02-02 | NULL | 2 |
| 2 | 2002-02-01 | NULL | 22 |
| 1 | 2001-01-02 | | NULL |
| 1 | 2002-02-05 | | NULL |
| 2 | 2001-02-22 | | NULL |
+------+------------+----------+----------+
6 rows in set (0.00 sec)
because the foo_data column's type is set by the first select clause (where foo_data is null). So, taking a tip I found in the comments in the manual, I add another select clause, as follows:
mysql> select id, date, 8888 as foo_data, 8888 as bar_data from foo where 1 = 0
-> union
-> select id, date, null as foo_data, bar_data from bar
-> union
-> select id, date, foo_data, null as bar_data from foo;
which gives the following table:
+------+------------+----------+----------+
| id | date | foo_data | bar_data |
+------+------------+----------+----------+
| 1 | 2001-01-01 | 0 | 1 |
| 1 | 2002-02-02 | 0 | 2 |
| 2 | 2002-02-01 | 0 | 22 |
| 1 | 2001-01-02 | 3 | 0 |
| 1 | 2002-02-05 | 25 | 0 |
| 2 | 2001-02-22 | 15 | 0 |
+------+------------+----------+----------+
6 rows in set (0.00 sec)
The nulls have been replaced by zero's!
If foo_data and bar_data are varchar(4) instead of int(4), and ' ' is used instead of 8888, then the nulls are replaced with what appear to be empty strings.
Description: When I try to union two tables, it seems to replace "null" with 0's in columns of type "int". How to repeat: -- MySQL dump 9.09 -- -- Host: localhost Database: test --------------------------------------------------------- -- Server version 4.0.15-standard -- -- Table structure for table `bar` -- CREATE TABLE bar ( id int(4) default NULL, date date default NULL, bar_data int(4) default NULL ) TYPE=MyISAM; -- -- Dumping data for table `bar` -- INSERT INTO bar VALUES (1,'2001-01-01',1); INSERT INTO bar VALUES (1,'2002-02-02',2); INSERT INTO bar VALUES (2,'2002-02-01',22); -- -- Table structure for table `foo` -- CREATE TABLE foo ( id int(4) default NULL, date date default NULL, foo_data int(4) default NULL ) TYPE=MyISAM; -- -- Dumping data for table `foo` -- INSERT INTO foo VALUES (1,'2001-01-02',3); INSERT INTO foo VALUES (1,'2002-02-05',25); INSERT INTO foo VALUES (2,'2001-02-22',15); Ordinarily I would run the following query: mysql> select id, date, null as foo_data, bar_data from bar -> union -> select id, date, foo_data, null as bar_data from foo; hoping for the following table: +------+------------+----------+----------+ | id | date | foo_data | bar_data | +------+------------+----------+----------+ | 1 | 2001-01-01 | NULL | 1 | | 1 | 2002-02-02 | NULL | 2 | | 2 | 2002-02-01 | NULL | 22 | | 1 | 2001-01-02 | 3 | NULL | | 1 | 2002-02-05 | 25 | NULL | | 2 | 2001-02-22 | 15 | NULL | +------+------------+----------+----------+ 6 rows in set (0.00 sec) But I know that this would actually result in +------+------------+----------+----------+ | id | date | foo_data | bar_data | +------+------------+----------+----------+ | 1 | 2001-01-01 | NULL | 1 | | 1 | 2002-02-02 | NULL | 2 | | 2 | 2002-02-01 | NULL | 22 | | 1 | 2001-01-02 | | NULL | | 1 | 2002-02-05 | | NULL | | 2 | 2001-02-22 | | NULL | +------+------------+----------+----------+ 6 rows in set (0.00 sec) because the foo_data column's type is set by the first select clause (where foo_data is null). So, taking a tip I found in the comments in the manual, I add another select clause, as follows: mysql> select id, date, 8888 as foo_data, 8888 as bar_data from foo where 1 = 0 -> union -> select id, date, null as foo_data, bar_data from bar -> union -> select id, date, foo_data, null as bar_data from foo; which gives the following table: +------+------------+----------+----------+ | id | date | foo_data | bar_data | +------+------------+----------+----------+ | 1 | 2001-01-01 | 0 | 1 | | 1 | 2002-02-02 | 0 | 2 | | 2 | 2002-02-01 | 0 | 22 | | 1 | 2001-01-02 | 3 | 0 | | 1 | 2002-02-05 | 25 | 0 | | 2 | 2001-02-22 | 15 | 0 | +------+------------+----------+----------+ 6 rows in set (0.00 sec) The nulls have been replaced by zero's! If foo_data and bar_data are varchar(4) instead of int(4), and ' ' is used instead of 8888, then the nulls are replaced with what appear to be empty strings.