I also fell into this NULL trap. Make sure to read the change log concerning NULL values! They are still buggy (imho) and with every change I have the feeling that new bugs/features are introduced.
Example: Select sum(1) from foo where 1 = 3; This statement actually returns a row with NULL. Which is a clear BUG imho because the where condition can never be true! But Victoria says in her/his mail that this is expected behavior... Cheers /rudy -----Original Message----- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: donderdag 17 juli 2003 10:33 To: [EMAIL PROTECTED] Subject: Re: join query result difference between 3.23.49 and 4.0.13 Doug Reese <[EMAIL PROTECTED]> 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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
