[jira] [Updated] (CARBONDATA-3403) MV is not working for like and filter AND and OR queries

[Akash R Nilugal (Jira)]

     [ https://issues.apache.org/jira/browse/CARBONDATA-3403?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Akash R Nilugal updated CARBONDATA-3403:
----------------------------------------
    Description:
MV is not working for like and filter AND and OR queries

 

Steps:

create table brinjal (imei string,AMSize string,channelsId string,ActiveCountry string, Activecity string,gamePointId double,deviceInformationId double,productionDate Timestamp,deliveryDate timestamp,deliverycharge double) STORED BY 'org.apache.carbondata.format' ;

 

create datamap brinjal_mv_tab_nlz_aa016 on table brinjal using 'mv' as select imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US' group by imei,AMSize,channelsId;


create datamap brinjal_mv_tab_nlz_aa018 on table brinjal using 'mv' as select imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry ='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;

 

then 

select imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US' group by imei,AMSize,channelsId; and 

  select imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry ='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;

are not hitting the datamap cretaed




CREATE TABLE IF NOT EXISTS tradeflow.tradeflow_all (
 m_month      smallint,
 hs_code      string  ,
 country      smallint,
 dollar_value double  ,      
 quantity     double  ,
 unit         smallint,
 b_country    smallint,
 imex         int,
 y_year       smallint)
STORED BY 'org.apache.carbondata.format'  TBLPROPERTIES('DICTIONARY_INCLUDE'='m_month,hs_code,b_country,unit,b_country,y_year,imex','table_blocksize'='256','sort_columns'='y_year,m_month,country,b_country,imex');

 


create datamap agr35 on table tradeflow_all using 'mv' as  select unit,y_year,m_month,country,b_country,sum(case when  imex=1 and (y_year=2017 and m_month=9  )then dollar_value else 0 end) ,sum(case when  imex=1 and (y_year=2017 and m_month=09  )then quantity else 0 end) ex   ,sum(case when  imex=1 and (y_year=2016 and ( m_month>=7 and m_month<=12)  )then quantity else 0 end)    from tradeflow_all group by unit,y_year,m_month,country,b_country;


create datamap agr35 on table tradeflow_all using 'mv' as  select hs_code,unit,y_year,m_month,country,b_country,sum(case when  imex=1 and (y_year=2017 and m_month=9  )then dollar_value else 0 end) ,sum(case when  imex=1 and (y_year=2017 and m_month=09  )then quantity else 0 end) ex   ,sum(case when  imex=1 and (y_year=2016 and ( m_month>=7 and m_month<=12)  )then quantity else 0 end)    from tradeflow_all group by hs_code,unit,y_year,m_month,country,b_country;


  was:
MV is not working for like and filter AND and OR queries

 

Steps:

create table brinjal (imei string,AMSize string,channelsId string,ActiveCountry string, Activecity string,gamePointId double,deviceInformationId double,productionDate Timestamp,deliveryDate timestamp,deliverycharge double) STORED BY 'org.apache.carbondata.format' ;

 

create datamap brinjal_mv_tab_nlz_aa016 on table brinjal using 'mv' as select imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US' group by imei,AMSize,channelsId;


create datamap brinjal_mv_tab_nlz_aa018 on table brinjal using 'mv' as select imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry ='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;

 

then 

select imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US' group by imei,AMSize,channelsId; and 

  select imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry ='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;

are not hitting the datamap cretaed

> MV is not working for like and filter AND and OR queries
> --------------------------------------------------------
>
>                 Key: CARBONDATA-3403
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-3403
>             Project: CarbonData
>          Issue Type: Bug
>            Reporter: Akash R Nilugal
>            Priority: Minor
>             Fix For: 1.6.0
>
>          Time Spent: 1h 40m
>  Remaining Estimate: 0h
>
> MV is not working for like and filter AND and OR queries
>  
> Steps:
> create table brinjal (imei string,AMSize string,channelsId string,ActiveCountry string, Activecity string,gamePointId double,deviceInformationId double,productionDate Timestamp,deliveryDate timestamp,deliverycharge double) STORED BY 'org.apache.carbondata.format' ;
>  
> create datamap brinjal_mv_tab_nlz_aa016 on table brinjal using 'mv' as select imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US' group by imei,AMSize,channelsId;
> create datamap brinjal_mv_tab_nlz_aa018 on table brinjal using 'mv' as select imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry ='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;
>  
> then 
> select imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US' group by imei,AMSize,channelsId; and 
>   select imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry ='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;
> are not hitting the datamap cretaed
> CREATE TABLE IF NOT EXISTS tradeflow.tradeflow_all (
>  m_month      smallint,
>  hs_code      string  ,
>  country      smallint,
>  dollar_value double  ,      
>  quantity     double  ,
>  unit         smallint,
>  b_country    smallint,
>  imex         int,
>  y_year       smallint)
> STORED BY 'org.apache.carbondata.format'  TBLPROPERTIES('DICTIONARY_INCLUDE'='m_month,hs_code,b_country,unit,b_country,y_year,imex','table_blocksize'='256','sort_columns'='y_year,m_month,country,b_country,imex');
>  
> create datamap agr35 on table tradeflow_all using 'mv' as  select unit,y_year,m_month,country,b_country,sum(case when  imex=1 and (y_year=2017 and m_month=9  )then dollar_value else 0 end) ,sum(case when  imex=1 and (y_year=2017 and m_month=09  )then quantity else 0 end) ex   ,sum(case when  imex=1 and (y_year=2016 and ( m_month>=7 and m_month<=12)  )then quantity else 0 end)    from tradeflow_all group by unit,y_year,m_month,country,b_country;
> create datamap agr35 on table tradeflow_all using 'mv' as  select hs_code,unit,y_year,m_month,country,b_country,sum(case when  imex=1 and (y_year=2017 and m_month=9  )then dollar_value else 0 end) ,sum(case when  imex=1 and (y_year=2017 and m_month=09  )then quantity else 0 end) ex   ,sum(case when  imex=1 and (y_year=2016 and ( m_month>=7 and m_month<=12)  )then quantity else 0 end)    from tradeflow_all group by hs_code,unit,y_year,m_month,country,b_country;

--
This message was sent by Atlassian JIRA
(v7.6.3#76005)