From: Date: July 16 2003 6:38pm
Subject: join query result difference between 3.23.49 and 4.0.13
List-Archive: http://lists.mysql.com/mysql/145364
Message-Id: <5.1.0.14.2.20030716090930.00ab05f0@crash.cts.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

hello,

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.

========================================

thanks again for any suggestions,

doug