join query result difference between 3.23.49 and 4.0.13

[Doug Reese]

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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw