{"action":"created","issue":{"url":"https://api.github.com/repos/facebook/presto/issues/4769","repository_url":"https://api.github.com/repos/facebook/presto","labels_url":"https://api.github.com/repos/facebook/presto/issues/4769/labels{/name}","comments_url":"https://api.github.com/repos/facebook/presto/issues/4769/comments","events_url":"https://api.github.com/repos/facebook/presto/issues/4769/events","html_url":"https://github.com/facebook/presto/issues/4769","id":140706880,"number":4769,"title":"BIGINT literal to DECIMAL implicit coercion","user":{"login":"arhimondr","id":5570988,"avatar_url":"https://avatars.githubusercontent.com/u/5570988?v=3","gravatar_id":"","url":"https://api.github.com/users/arhimondr","html_url":"https://github.com/arhimondr","followers_url":"https://api.github.com/users/arhimondr/followers","following_url":"https://api.github.com/users/arhimondr/following{/other_user}","gists_url":"https://api.github.com/users/arhimondr/gists{/gist_id}","starred_url":"https://api.github.com/users/arhimondr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/arhimondr/subscriptions","organizations_url":"https://api.github.com/users/arhimondr/orgs","repos_url":"https://api.github.com/users/arhimondr/repos","events_url":"https://api.github.com/users/arhimondr/events{/privacy}","received_events_url":"https://api.github.com/users/arhimondr/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2016-03-14T15:25:52Z","updated_at":"2016-03-14T15:37:00Z","closed_at":null,"body":"In the current decimal implementation we have 2 different binary representation for the `DECIMAL` type. One of them is fast `short_decimal` for the decimals with the precision `<=18` which in implemented on top of long. And the second one is the `long_decimal` which is stored in 16 bits `Slices` and all the arithmetic operations for `long_decimal` are implemented with using \"slow\" `BigInteger`. In order to represent `BIGINT` value as `DECIMAL` the slow `DECIMAL(19,0)` type is required as the max `BIGINT` value takes 19 digits. \r\n\r\nAlthought we can't optimize `BIGINT` column coercions (`short_decimal = bigint_column`), cause we don't know what actual values is stored in that column, we still can optimize coercions of the `BIGINT` literal (`short_decimal = 1`), because we know the actual value for that literal. So ideally  `BIGINT '1'` literal should be casted to the `DECIMAL(1,0)` and all the arithmetic operations should be performed on `long`.\r\n\r\nHow does this problem is solved in other popular DBMS's:\r\n\r\n- **MySQL** has parametric integer type. So, it might be `int(1)`, `int(10)`, etc. Parameterized `INT` type does not introduce any bounds and does not change the arithmetic semantics. It is still possible to insert \"123456789\" value to the column with the int(1) type with no auto trimming/rounding. It looks like the parametrized INT was introduced just to resolve the issues with integer literals coercion to parametrized types such as DECIMAL or VARCHAR. Making BIGINT parametrized in Presto require reimplementation of all the BIGINT arithmetic operators/functions to make them calculate the output type.\r\n- **PostgresSQL** has unbounded `NUMERIC`(`DECIMAL`) type. Any arithmetic operation with `DECIMAL` and `INTEGER` type produces unbounded DECIMAL as a result. In order to implement something similar in Presto we should make Decimal performance precision-agnostic. @sopel39 did some investigation in that direction, and it seems that it is possible. But it can take time. So we would rather want to have some intermidiate solution before the fast decimals is implemeted.\r\n- In **Teradata** integer literals are treated as `INTEGER` type instead of `BIGINT`, and the `INTEGER` to `DECIMAL` coercion is implemented in a similar way to Presto. So `INTEGER` literals are casted to `DECIMAL(10,0)`. Treating integer values smaller than `Integer.MAX_VALUE` as `INTEGER` would partialy solve our problem.\r\n- **Oracle** does not have INTEGER/BIGINT types at all. Oracle have only `NUMERIC`(`DECIMAL`) types which are parametrized.\r\n\r\nWe have tried several approaches in order to achive casting BIGINT literals to the lowest required precision, but none of them seems to be good enough:\r\n\r\n- **Rewrite BIGINT to DECIMAL cast in Analyzer**:\r\nDuring the operator/function selection we can check whether the selected function require the BIGINT to DECIMAL cast. If it does, and the BIGINT parameter is the LongLiteral expression, than try to decrease result precision and re-select function. This solution works pretty well for the simple cases, like `SELECT 1 + 1.0`. However it doesn't work for more complicated cases like `SELECT (1+1)+1.0`, where bigint expressions are no longer LongLiteral's. We can try to interpret expressions recursively in the analyzer, but the using of the expression interpreter in the expression analyzer seems to be unnatural. Conceptually Analyze step should be made before any calculations/interpretation/compilation. Although currently it is possible to run interpreter in Analyzer, we can't be sure that it is not going to be changed in the future. https://github.com/Teradata/presto/commit/a406704a8af10dc31cb0885c7c755cbb086feb79\r\n- **Rewrite BIGINT to DECIMAL casts in a separate plan optimizer**: We tried to simply replace the `BIGINT` to `DECIMAL` cast with decreasing precision when the input is the `LongLiteral` after interpreting. The problem is that high output precision in BIGINT to DECIMAL casts does not always means that it is not optimal BIGINT to DECIMAL coercion. Lets consider following query `SELECT * FROM table WHERE a in (DECIMAL '1234567890123456789', 1)`.  In such case BIGINT literal `1` must be casted to DECIMAL(19,0) regardless the fact that it can be casted to the decimal with the less precision. Determining such cases for preventing optimizer from decreasing precision might be tricky and not reliable. https://github.com/Teradata/presto/commit/1ed455a918aee2d1494bdb271ec1986354f4453c\r\n\r\nAlso we found 2 other solutions, which don't require any tricky rewrites, but they have their own consequences:\r\n\r\n- **Introduce int(x) type** similary to MySQL. It should work pretty well, but such approach require significant modifications and from my perspective it is counter-intuitive.\r\n- **Introduce byte, short, integer types**. Seems to be the easy and clean solution. Literal `1` will be treated as \"byte\" and coerced to DECIMAL(3,0). Integer type is already implemented by someone #4711. With this patch values up to 2147483647 are treated as INT, and can be coerced to DECIMAL(10,0) which still has the short-decimal representation and partially solves our problem."},"comment":{"url":"https://api.github.com/repos/facebook/presto/issues/comments/196371574","html_url":"https://github.com/facebook/presto/issues/4769#issuecomment-196371574","issue_url":"https://api.github.com/repos/facebook/presto/issues/4769","id":196371574,"user":{"login":"arhimondr","id":5570988,"avatar_url":"https://avatars.githubusercontent.com/u/5570988?v=3","gravatar_id":"","url":"https://api.github.com/users/arhimondr","html_url":"https://github.com/arhimondr","followers_url":"https://api.github.com/users/arhimondr/followers","following_url":"https://api.github.com/users/arhimondr/following{/other_user}","gists_url":"https://api.github.com/users/arhimondr/gists{/gist_id}","starred_url":"https://api.github.com/users/arhimondr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/arhimondr/subscriptions","organizations_url":"https://api.github.com/users/arhimondr/orgs","repos_url":"https://api.github.com/users/arhimondr/repos","events_url":"https://api.github.com/users/arhimondr/events{/privacy}","received_events_url":"https://api.github.com/users/arhimondr/received_events","type":"User","site_admin":false},"created_at":"2016-03-14T15:36:59Z","updated_at":"2016-03-14T15:36:59Z","body":"@dain @martint @electrum \r\nAny suggestions are highly appreciated."}}