| Bug #9168 | Traditional: DECIMAL/NUMERIC/BIGINT column: col / 0 returns 0 or NULL | ||
|---|---|---|---|
| Submitted: | 14 Mar 2005 12:53 | Modified: | 10 Feb 2014 8:10 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Errors | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[14 Mar 2005 12:54]
Matthias Leich
test case
Attachment: ml45.test (application/test, text), 667 bytes.
[14 Mar 2005 22:13]
Trudy Pelzer
See Bug #5929 and #6105. Under sql_mode='traditional', division by zero is an error and must return SQLSTATE 22012 "division by zero" -- it is not correct to have the server return NULL instead.
[15 Mar 2005 12:15]
Matthias Leich
I was not aware that there is already such final decision, that in 'traditional'
mode a division by zero should give an error.
There are other DBMs which provide
1. SELECT col/0 --> result with "special" NULL, whis is similar but not
equal to NULL
2. DELETE FROM ... WHERE col/0 = <value --> error message
[15 Mar 2005 17:15]
Trudy Pelzer
This is as required by standard SQL. The expected behaviour is documented; see the description of ERROR_FOR_DIVISION_BY_ZERO at: http://dev.mysql.com/doc/mysql/en/server-sql-mode.html ERROR_FOR_DIVISION_BY_ZERO is included in TRADITIONAL.
[6 May 2005 16:16]
Alexey Botchkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Fixed with the 'big' decimal patch
[6 May 2005 16:17]
Alexey Botchkov
The current state doesn't make Trudy happy though...
[7 May 2005 6:42]
Alexey Botchkov
This can't be fixed completely in 5.0 - we still return records with NULLs.
Description: set sql_mode='traditional'; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (f1 DECIMAL(4)); INSERT INTO t1 values(12),(0); SELECT f1/0 FROM t1; f1/0 NULL 0.00000 <------------- This value is wrong. NULL would be correct. Warnings: Error 1365 Division by 0 <---- Both records must cause a warning. For comparison numbers of the data type double give the expected result: DROP TABLE t1; CREATE TABLE t1 (f1 double); INSERT INTO t1 values(12),(0); SELECT f1/0 FROM t1; f1/0 NULL NULL Warnings: Error 1365 Division by 0 Error 1365 Division by 0 My environment: - Intel PC with Linux(SuSE 9.1) - MySQL compiled from source Version 5.0 ChangeSet@1.1807.1.1, 2005-03-13 MySQL 4.1 ChangeSet@1.2112, 2005-03-14 does not show this bug. The probability that both numbers are zero is very low --> low priority We get a wrong result and there is no comfortable workaround --> medium severity How to repeat: Please use the statements above or the attached test case ml45.test , copy it to mysql-test/t touch r/ml45.result # Produce a dummy file with expected # results. ./mysql-test-run ml45 inspect r/ml45.reject # The protocol of the execution.