yashmayya commented on PR #14089: URL: https://github.com/apache/pinot/pull/14089#issuecomment-2378874424
> Even if we cast after getting the result, it might already lose precision and give inaccurate result. Yes, this is a valid point. Also, an expression like `5 / 3 + 1.5` will return `3.166666666666667` in the v2 engine (since the cast is not done after each function call), as opposed to `2.5` in Postgres. > Currently, when adding 2 ints, will v2 engine return int Yes, it does, which seems somewhat problematic because something like `2000000000 + 2000000000` results in an integer overflow occurring and a negative integer value being returned. > I was thinking adding a general query option to turn v1 engine into standard sql compatible mode, so that v2 engine can enable it and get standard sql behavior > Basically we want a general way so that we can add standard SQL support without breaking existing v1 queries I wasn't able to find any common standard SQL definition for such arithmetic operations. For instance, in MySQL `1 / 2` returns `0.5` and `2000000000 + 2000000000` returns `4000000000` (similar to the Pinot v1 engine, unlike Postgres or Pinot v2 engine). Other databases seem to be doing this their own way as well. If there's a consensus that we always want to do things the Postgres way going forward, then the query option should be something like `usePostgresSemantics`? And coming to this PR itself, I presume we'd want the improvements here with or without such a query option right? And only put some of the compatibility breaking changes (like enforcing `int` for arithmetic operations on `int` and the type specific division) behind the new query option? -- 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. To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org For additional commands, e-mail: commits-h...@pinot.apache.org
