[GitHub] [druid] gianm commented on issue #9417: SQL: improve division behaviour

[GitBox <gi...@apache.org>]

gianm commented on issue #9417: SQL: improve division behaviour
URL: https://github.com/apache/druid/issues/9417#issuecomment-591697433
 
 
   I recall looking into this a while back and finding that the behavior of `SELECT 1 / 2` varied across popular databases, and we went with the current behavior (integer division) since it's type-preserving, which seemed like a nice property (if inputs are ints, output will be an int).
   
   I looked into a few systems and here's what I found for integer division behavior. I didn't look at Vertica but I added what you found.
   
   |System|1/2|1/0|1/NULL|
   |------|----|----|----|
   |Druid|0|error|error|
   |Vertica|0.5|error|0|
   |PostgreSQL|0|error|NULL|
   |BigQuery|0.5|error|NULL|
   
   Really, a paragon of consistency 😄 
   
   For the 1/0 case, every system I looked at throws an error. But BigQuery has a SAFE_DIVIDE function that looks interesting. It returns NULL in the 1/0 case and is the same as `/` otherwise. Would adding that function solve your problem?
   
   For the 1/2 case, f you want floating point division for `x / y`, you can do `CAST(x AS DOUBLE) / y`.
   
   For the 1/NULL case, I think we should probably return NULL instead of throwing an error.
   
   Btw, I've never seen a one-arg `NVL` function. Did you mean to use a different function in your example `SUM(nominator) / NVL(SUM(denominator))`?

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org