Re: Division by floating number literal in hive 2.3.5 results in implicit conversion to integer
SELECT CAST(1.0 AS DOUBLE)/2.0 FROM EXAMPLE; SELECT DOUBLE(1.0)/2.0 FROM EXAMPLE; On Tue, Apr 28, 2020 at 5:31 AM Arup Malakar wrote: > > Would anyone know if it could be related to calcite versions and any > possible workarounds? > > On Thu, Apr 23, 2020 at 6:16 PM Arup Malakar wrote: > >> Hi Hive Users, >> >> I noticed that with cbo on the query: "SELECT 1.0/2.0 FROM example limit >> 1" returns 0 >> >> hive> SET hive.cbo.enable=true; SELECT 1.0/2.0 FROM example limit 1; >>> 0 >>> >> >> >> With cbo disabled it returns 0.5 as expected: >> >> hive> SET hive.cbo.enable=false; SELECT 1.0/2.0 FROM example limit 1; >>> 0.5 >>> >> >> Not referring to a table returns 0.5 as expected as well: >> >> hive> select 1.0/2.0; >>> 0.5 >>> >> >> >> This is how the explain plan looks like: >> >> hive> SET hive.cbo.enable=true; EXPLAIN extended SELECT 1.0/2.0 FROM >>> example limit 1; >>> OK >>> STAGE DEPENDENCIES: >>> Stage-0 is a root stage >>> STAGE PLANS: >>> Stage: Stage-0 >>> Fetch Operator >>> limit: 1 >>> Processor Tree: >>> TableScan >>> alias: example >>> GatherStats: false >>> Select Operator >>> expressions: 0 (type: int) >>> outputColumnNames: _col0 >>> Limit >>> Number of rows: 1 >>> ListSink >> >> >> I have tested that it is not an issue in 2.1.1 and 3.0, but only in >> 2.3.6. I couldn't find a corresponding JIRA that fixes it though. Does >> anyone know if there is a JIRA that fixes this (so that I can backport) or >> any other suggestion? >> >> -- >> Arup Malakar >> > > > -- > Arup Malakar >
Re: Division by floating number literal in hive 2.3.5 results in implicit conversion to integer
SELECT DOUBLE(1.0/2.0) FROM EXAMPLE; On Tue, Apr 28, 2020 at 2:36 PM Kwanghee Park wrote: > SELECT CAST(1.0 AS DOUBLE)/2.0 FROM EXAMPLE; > SELECT DOUBLE(1.0)/2.0 FROM EXAMPLE; > > On Tue, Apr 28, 2020 at 5:31 AM Arup Malakar wrote: > >> >> Would anyone know if it could be related to calcite versions and any >> possible workarounds? >> >> On Thu, Apr 23, 2020 at 6:16 PM Arup Malakar wrote: >> >>> Hi Hive Users, >>> >>> I noticed that with cbo on the query: "SELECT 1.0/2.0 FROM example limit >>> 1" returns 0 >>> >>> hive> SET hive.cbo.enable=true; SELECT 1.0/2.0 FROM example limit 1; 0 >>> >>> >>> With cbo disabled it returns 0.5 as expected: >>> >>> hive> SET hive.cbo.enable=false; SELECT 1.0/2.0 FROM example limit 1; 0.5 >>> >>> Not referring to a table returns 0.5 as expected as well: >>> >>> hive> select 1.0/2.0; 0.5 >>> >>> >>> This is how the explain plan looks like: >>> >>> hive> SET hive.cbo.enable=true; EXPLAIN extended SELECT 1.0/2.0 FROM example limit 1; OK STAGE DEPENDENCIES: Stage-0 is a root stage STAGE PLANS: Stage: Stage-0 Fetch Operator limit: 1 Processor Tree: TableScan alias: example GatherStats: false Select Operator expressions: 0 (type: int) outputColumnNames: _col0 Limit Number of rows: 1 ListSink >>> >>> >>> I have tested that it is not an issue in 2.1.1 and 3.0, but only in >>> 2.3.6. I couldn't find a corresponding JIRA that fixes it though. Does >>> anyone know if there is a JIRA that fixes this (so that I can backport) or >>> any other suggestion? >>> >>> -- >>> Arup Malakar >>> >> >> >> -- >> Arup Malakar >> >
Re: Division by floating number literal in hive 2.3.5 results in implicit conversion to integer
Would anyone know if it could be related to calcite versions and any possible workarounds? On Thu, Apr 23, 2020 at 6:16 PM Arup Malakar wrote: > Hi Hive Users, > > I noticed that with cbo on the query: "SELECT 1.0/2.0 FROM example limit > 1" returns 0 > > hive> SET hive.cbo.enable=true; SELECT 1.0/2.0 FROM example limit 1; >> 0 >> > > > With cbo disabled it returns 0.5 as expected: > > hive> SET hive.cbo.enable=false; SELECT 1.0/2.0 FROM example limit 1; >> 0.5 >> > > Not referring to a table returns 0.5 as expected as well: > > hive> select 1.0/2.0; >> 0.5 >> > > > This is how the explain plan looks like: > > hive> SET hive.cbo.enable=true; EXPLAIN extended SELECT 1.0/2.0 FROM >> example limit 1; >> OK >> STAGE DEPENDENCIES: >> Stage-0 is a root stage >> STAGE PLANS: >> Stage: Stage-0 >> Fetch Operator >> limit: 1 >> Processor Tree: >> TableScan >> alias: example >> GatherStats: false >> Select Operator >> expressions: 0 (type: int) >> outputColumnNames: _col0 >> Limit >> Number of rows: 1 >> ListSink > > > I have tested that it is not an issue in 2.1.1 and 3.0, but only in 2.3.6. > I couldn't find a corresponding JIRA that fixes it though. Does anyone know > if there is a JIRA that fixes this (so that I can backport) or any other > suggestion? > > -- > Arup Malakar > -- Arup Malakar
