List:General Discussion« Previous MessageNext Message »
From: mysql Date:October 12 2007 4:38pm
Subject:LEFT JOIN (SELECT ...) -- no joy
View as plain text  
using 5.0.24

mysql> describe order_details;
+------------+-----------------------+------+-----+---------+----------------+
| Field      | Type                  | Null | Key | Default | Extra      |
+------------+-----------------------+------+-----+---------+----------------+
| id         | mediumint(8) unsigned | NO   | PRI | NULL    | 
auto_increment |
| order_id   | mediumint(8) unsigned | NO   | MUL |         |      |
| product_id | smallint(5) unsigned  | NO   | MUL |         |      |
| quantity   | smallint(5) unsigned  | NO   |     |         |      |
| unit_price | decimal(8,2) unsigned | NO   |     |         |      |
+------------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT order_id, SUM(quantity * unit_price) AS subtotal
     -> FROM order_details GROUP BY order_id;

+----------+----------+
| order_id | subtotal |
+----------+----------+
|        1 |   101.94 |
|        2 |    47.97 |
+----------+----------+
2 rows in set (0.00 sec)

-- same query used in LEFT JOIN clause:

mysql> SELECT po.id, po.customer_id, po.delivered, od.subtotal
     -> FROM purchase_order AS po
     -> LEFT JOIN (SELECT order_id, SUM(quantity * unit_price)
     -> AS subtotal FROM order_details GROUP BY order_id)
     -> AS od ON od.order_id = po.id
     -> ORDER BY po.id\G

*************************** 1. row ***************************
                id: 10001
       customer_id: 1
         delivered: 2007-10-10 23:51:32
          subtotal: NULL
*************************** 2. row ***************************
                id: 10002
       customer_id: 2
         delivered: 2007-10-10 23:51:32
          subtotal: NULL
2 rows in set (0.00 sec)


So, i'm wondering why this inner query is returning NULL for subtotal 
here. I know that SUM() returns NULL on error but i can't see how that's 
the situation here.

This works fine with Postgres. Is this a bug, possibly?

brian

Thread
LEFT JOIN (SELECT ...) -- no joymysql12 Oct
Re: LEFT JOIN (SELECT ...) -- no joymysql12 Oct