Re: Division by zero

On Fri, 31 Jul 2009 11:49:47 -0700 Jeff Davis wrote:
> "Find all store locations which have not achieved an average sale price
> of $100."
>
> SELECT store_name FROM sales WHERE totalsales/nsales < 100;
>
> The person running that query might be missing stores with no sales at
> all, and they might prefer an ERROR to the silent omission of results.

Not that I'm suggesting that Postgres mimic MySQL's behavior in this
case, but just for fun I tried the following SQL in both PG 8.3 and MySQL 5.0:

-----------------------------
CREATE TEMPORARY TABLE sales(
store_name varchar( 128 ) ,
totalsales float NOT NULL DEFAULT 0.0,
nsales int NOT NULL DEFAULT 0) ;

INSERT INTO sales(store_name, totalsales, nsales)
VALUES ('store A', 1000.0, 100), ('store B', 0, 0), ('store C', 51.1, 2);

SELECT store_name FROM sales WHERE totalsales / nsales < 100 ;
-----------------------------

MySQL gave results:
+------------+
| store_name |
+------------+
| store A |
| store C |
+------------+

since divide-by-zero errors in MySQL produce NULL values, while Postgres gave:
"ERROR: division by zero".

I am also no expert on the SQL Standard; there was a Stack Overflow discussion
relating to this topic which didn't produce any useful answers:
http://stackoverflow.com/questions/1140860/treatment-of-error-values-in-the-sql-standard

To the original poster, I'd suggest just using NULLIF(column, 0) on
your denominators to
achieve an effect similar to MySQL's behavior.
Cheers,
Josh