Re: join query result difference between 3.23.49 and 4.0.13

[Victoria Reznichenko]

Doug Reese <getjazzed@stripped> wrote:
> 
> i have what seems to me a very common operation i'm performing.  i need to 
> find the balance on an invoice.  i was not having any problems until the 
> production server was upgraded to mysql v4.0.13-standard for 
> pc-linux.  there must be a better way to query for this information than 
> the method i'm using, since the result with v4.0 is not what i expected, 
> nor what i received with v3.23.  i'm including sample data and queries with 
> my results.  i've not been able to find any relevant messages in the list 
> archives.  any comments are greatly appreciated.
> 
> ========================================
> 
> here's the table structure and some sample data:
> 
> CREATE TABLE `billing` (
>   `invoice` mediumint(9) NOT NULL auto_increment,
>   `user_id` mediumint(9) NOT NULL default '0',
>   `invoice_date` date NOT NULL default '0000-00-00',
>   `amount` float NOT NULL default '0',
>   `timestamp` timestamp(14) NOT NULL,
>   PRIMARY KEY  (`invoice`),
>   KEY `user_id` (`user_id`),
>   KEY `user_invoice` (`user_id`,`invoice`)
> ) TYPE=MyISAM COMMENT='invoices';
> 
> 
> INSERT INTO `billing` VALUES (10000, 1, '2003-07-01', '500', 20030716092700);
> INSERT INTO `billing` VALUES (10001, 1, '2003-07-03', '600', 20030716092807);
> 
> # --------------------------------------------------------
> 
> CREATE TABLE `billing_payment` (
>   `invoice` mediumint(9) NOT NULL default '0',
>   `amount_paid` mediumint(9) NOT NULL default '0',
>   `payment_status` varchar(15) NOT NULL default '',
>   `pending_reason` varchar(15) default NULL,
>   `payment_date` date default NULL,
>   `txn_id` varchar(20) default NULL,
>   `timestamp` timestamp(14) NOT NULL,
>   KEY `txn_id` (`txn_id`),
>   KEY `invoice` (`invoice`)
> ) TYPE=MyISAM COMMENT='payments on invoices';
> 
> INSERT INTO `billing_payment` VALUES (10000, 500, 'Completed', NULL, 
> '2003-07-02', '112233', 20030716092746);
> 
> ========================================
> 
> here are the queries and results.  note that query #1 gives the expected 
> (and desired) result on both versions, but query #2 only gives the expected 
> (and useful) result in v3.23.  to give a brief explanation of the 
> difference between the queries: invoice 10000 has a payment against it, 
> while invoice 10001 has no payment records in the payment table.
> 
> === v3.23.49
> 
> query #1
> mysql> SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - 
> SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( 
> billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10000 GROUP 
> BY billing_payment.invoice;
> +---------+------+---------+
> | invoice | paid | balance |
> +---------+------+---------+
> |   10000 |  500 |       0 |
> +---------+------+---------+
> 1 row in set (0.00 sec)
> 
> query #2
> mysql> SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - 
> SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( 
> billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP 
> BY billing_payment.invoice;
> +---------+------+---------+
> | invoice | paid | balance |
> +---------+------+---------+
> |   10001 |    0 |     600 |
> +---------+------+---------+
> 1 row in set (0.00 sec)
> 
> === v4.0.13
> 
> query #1
> mysql> SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - 
> SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( 
> billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10000 GROUP 
> BY billing_payment.invoice;
> +---------+------+---------+
> | invoice | paid | balance |
> +---------+------+---------+
> |   10000 |  500 |       0 |
> +---------+------+---------+
> 1 row in set (0.01 sec)
> 
> query #2
> mysql> SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - 
> SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( 
> billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP 
> BY billing_payment.invoice;
> +---------+------+---------+
> | invoice | paid | balance |
> +---------+------+---------+
> |   10001 | NULL |    NULL |
> +---------+------+---------+
> 1 row in set (0.00 sec)
> 
> NULL values in this result are not expected, nor are they helpful in 
> determining the invoice balance.

It's correct result. Since 4.0.13 SUM() returns NULL if there is no rows in the result or
if all values are NULL (as in your case).
And SELECT billing.amount - NULL also returns NULL.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   Victoria.Reznichenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
       <___/   www.mysql.com