Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: 0::decimal/infinity returns 0E-2019 instead of 0 #103633

Closed
otan opened this issue May 18, 2023 · 2 comments · Fixed by #106472
Closed

sql: 0::decimal/infinity returns 0E-2019 instead of 0 #103633

otan opened this issue May 18, 2023 · 2 comments · Fixed by #106472
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)

Comments

@otan
Copy link
Contributor

otan commented May 18, 2023 •
edited
Loading

In PG:

otan=# select 0::decimal/'infinity';
 ?column?
----------
        0
(1 row)

In CRDB:

root@:26257/defaultdb> select 0::decimal/'infinity';
  ?column?
------------
  0E-2019
(1 row)


Time: 1ms total (execution 1ms / network 1ms)

The reason why it uses E-2019 is because it uses the HighPrecisionCtx to divide. We should probably not do that (or make the apd library not do that in Divide)

Note this is reporting correctly for non-infinity:

root@:26257/defaultdb> select 0 / 5::decimal;
  ?column?
------------
         0
(1 row)


Time: 1ms total (execution 1ms / network 0ms)

Issue from #102299

Jira issue: CRDB-28105
@otan otan added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label May 18, 2023
@blathers-crl blathers-crl bot added the T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) label May 18, 2023
@yuzefovich
Copy link
Member

Known issue #40929

@otan
Copy link
Contributor Author

otan commented May 22, 2023

#40929 seems like it's a panic, which i can't repro. nonetheless, the result is incorrect

@otan otan mentioned this issue May 22, 2023
Closed
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Jul 8, 2023
@DrewKimball
sql: fix decimal evaluation edge cases
59ed56c 
Previously, the logic for decimal and float division, floor division
and mod operators was incorrect for a few edge cases involving `NaN`
or `Infinity` values. For example, `'NaN'::DECIMAL / 0` would throw
a division-by-zero error when it should evaluate to `NaN` and
`0/'inf'::DECIMAL` returned `0E-2019` instead of just `0`.

This patch updates the special-case logic to mirror that of postgres,
so division-by-zero errors always check the `NaN` case and the division
by infinity case returns a `0` without extra digits.

Fixes cockroachdb#40929
Fixes cockroachdb#103633

Release note (bug fix): Fixed edge cases in decimal and float evaluation
for division operators. `'NaN'::DECIMAL / 0` will now return `NaN` instead
of a division-by-zero error, and `0 / 'inf'::DECIMAL` will return `0`
instead of `0E-2019`.
@DrewKimball DrewKimball mentioned this issue Jul 8, 2023
Merged
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Jul 10, 2023
@DrewKimball
sql: fix decimal evaluation edge cases
b59d6c6 
Previously, the logic for decimal and float division, floor division
and mod operators was incorrect for a few edge cases involving `NaN`
or `Infinity` values. For example, `'NaN'::DECIMAL / 0` would throw
a division-by-zero error when it should evaluate to `NaN` and
`0/'inf'::DECIMAL` returned `0E-2019` instead of just `0`.

This patch updates the special-case logic to mirror that of postgres,
so division-by-zero errors always check the `NaN` case and the division
by infinity case returns a `0` without extra digits.

Fixes cockroachdb#40929
Fixes cockroachdb#103633

Release note (bug fix): Fixed edge cases in decimal and float evaluation
for division operators. `'NaN'::DECIMAL / 0` will now return `NaN` instead
of a division-by-zero error, and `0 / 'inf'::DECIMAL` will return `0`
instead of `0E-2019`.
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Jul 10, 2023
@DrewKimball
sql: fix decimal evaluation edge cases
38fe3e8 
Previously, the logic for decimal and float division, floor division
and mod operators was incorrect for a few edge cases involving `NaN`
or `Infinity` values. For example, `'NaN'::DECIMAL / 0` would throw
a division-by-zero error when it should evaluate to `NaN` and
`0/'inf'::DECIMAL` returned `0E-2019` instead of just `0`.

This patch updates the special-case logic to mirror that of postgres,
so division-by-zero errors always check the `NaN` case and the division
by infinity case returns a `0` without extra digits.

Fixes cockroachdb#40929
Fixes cockroachdb#103633

Release note (bug fix): Fixed edge cases in decimal and float evaluation
for division operators. `'NaN'::DECIMAL / 0` will now return `NaN` instead
of a division-by-zero error, and `0 / 'inf'::DECIMAL` will return `0`
instead of `0E-2019`.
craig bot pushed a commit that referenced this issue Jul 13, 2023
@DrewKimball @andyyang890
Merge #106472 #106790
115c9ba 
106472: sql: fix decimal evaluation edge cases r=DrewKimball a=DrewKimball

Previously, the logic for decimal and float division, floor division and mod operators was incorrect for a few edge cases involving `NaN` or `Infinity` values. For example, `'NaN'::DECIMAL / 0` would throw a division-by-zero error when it should evaluate to `NaN` and `0/'inf'::DECIMAL` returned `0E-2019` instead of just `0`.

This patch updates the special-case logic to mirror that of postgres, so division-by-zero errors always check the `NaN` case and the division by infinity case returns a `0` without extra digits.

Fixes #40929
Fixes #103633

Release note (bug fix): Fixed edge cases in decimal and float evaluation for division operators. `'NaN'::DECIMAL / 0` will now return `NaN` instead of a division-by-zero error, and `0 / 'inf'::DECIMAL` will return `0` instead of `0E-2019`.

106790: tree: fix statement tag for tree.ShowSequences r=knz,rafiss a=andyyang890

Epic: None

Release note (bug fix): The statement tag for `SHOW SEQUENCES` is now
corrected to be `SHOW SEQUENCES` instead of `SHOW SCHEMAS`.

Co-authored-by: Drew Kimball <drewk@cockroachlabs.com>
Co-authored-by: Andy Yang <yang@cockroachlabs.com>
@craig craig bot closed this as completed in a8b42c9 Jul 14, 2023
This was referenced Sep 9, 2023
Merged
Merged
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Sep 9, 2023
@DrewKimball
sql: fix decimal evaluation edge cases
10e587d 
Previously, the logic for decimal and float division, floor division
and mod operators was incorrect for a few edge cases involving `NaN`
or `Infinity` values. For example, `'NaN'::DECIMAL / 0` would throw
a division-by-zero error when it should evaluate to `NaN` and
`0/'inf'::DECIMAL` returned `0E-2019` instead of just `0`.

This patch updates the special-case logic to mirror that of postgres,
so division-by-zero errors always check the `NaN` case and the division
by infinity case returns a `0` without extra digits.

Fixes cockroachdb#40929
Fixes cockroachdb#103633

Release note (bug fix): Fixed edge cases in decimal and float evaluation
for division operators. `'NaN'::DECIMAL / 0` will now return `NaN` instead
of a division-by-zero error, and `0 / 'inf'::DECIMAL` will return `0`
instead of `0E-2019`.
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Sep 12, 2023
@DrewKimball
sql: fix decimal evaluation edge cases
0b89187 
Previously, the logic for decimal and float division, floor division
and mod operators was incorrect for a few edge cases involving `NaN`
or `Infinity` values. For example, `'NaN'::DECIMAL / 0` would throw
a division-by-zero error when it should evaluate to `NaN` and
`0/'inf'::DECIMAL` returned `0E-2019` instead of just `0`.

This patch updates the special-case logic to mirror that of postgres,
so division-by-zero errors always check the `NaN` case and the division
by infinity case returns a `0` without extra digits.

Fixes cockroachdb#40929
Fixes cockroachdb#103633

Release note (bug fix): Fixed edge cases in decimal and float evaluation
for division operators. `'NaN'::DECIMAL / 0` will now return `NaN` instead
of a division-by-zero error, and `0 / 'inf'::DECIMAL` will return `0`
instead of `0E-2019`.
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Sep 12, 2023
@DrewKimball
sql: fix decimal evaluation edge cases
51743a3 
Previously, the logic for decimal and float division, floor division
and mod operators was incorrect for a few edge cases involving `NaN`
or `Infinity` values. For example, `'NaN'::DECIMAL / 0` would throw
a division-by-zero error when it should evaluate to `NaN` and
`0/'inf'::DECIMAL` returned `0E-2019` instead of just `0`.

This patch updates the special-case logic to mirror that of postgres,
so division-by-zero errors always check the `NaN` case and the division
by infinity case returns a `0` without extra digits.

Fixes cockroachdb#40929
Fixes cockroachdb#103633

Release note (bug fix): Fixed edge cases in decimal and float evaluation
for division operators. `'NaN'::DECIMAL / 0` will now return `NaN` instead
of a division-by-zero error, and `0 / 'inf'::DECIMAL` will return `0`
instead of `0E-2019`.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: fix decimal evaluation edge cases DrewKimball/cockroach
2 participants
@otan @yuzefovich