Description:
When I'm using a outer join, it happens, that some rows are given with NULL. The Problem is, that we can't calculate with NULL.
In the SQL-Statement it should be able to give a default for the NULL value (See suggested fix).
How to repeat:
Feature request. I can't repeat. See the fix. :-)
Suggested fix:
mysql>CREATE TABLE test ( n1 tinyint(3) unsigned NOT NULL, n2 mediumint(9) NOT NULL )
ENGINE=InnoDB;
mysql>CREATE TABLE test2 ( n1 tinyint(3) unsigned NOT NULL, n2 mediumint(9) NOT NULL )
ENGINE=InnoDB;
mysql> insert into test values(1,2),(2,3),(3,4);
mysql>insert into test2 values(2,3),(3,4);
mysql> select test.n1,test.n2,test2.n2,test.n2+test2.n2 from test inner join test2 on (test.n1=test2.n1);
+----+----+----+------------------+
| n1 | n2 | n2 | test.n2+test2.n2 |
+----+----+----+------------------+
| 2 | 3 | 3 | 6 |
| 3 | 4 | 4 | 8 |
+----+----+----+------------------+
2 rows in set (0.01 sec)
########### NICE - THIS WORKS
mysql> select test.n1,test.n2,test2.n2,test.n2+test2.n2 from test left outer join test2 on (test.n1=test2.n1);
+----+----+------+------------------+
| n1 | n2 | n2 | test.n2+test2.n2 |
+----+----+------+------------------+
| 1 | 2 | NULL | NULL | #### CRAP ! I WANT 2+0 not 2+NULL
| 2 | 3 | 3 | 6 |
| 3 | 4 | 4 | 8 |
+----+----+------+------------------+
3 rows in set (0.00 sec)
# HERE'S my suggestion:
mysql> select test.n1,test.n2,test2.n2 default 0,test.n2+test2.n2 from test left outer join test2 on (test.n1=test2.n1);
# OR:
mysql> select test.n1,test.n2,test2.n2,test.n2+test2.n2 from test left outer join test2 on (test.n1=test2.n1) WHEN test2.n2 is NULL return 0;
