[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





  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



CREATE DATAMAP sc_mvdm_nlz_003 USING 'MV' AS
SELECT CAST(floor((STARTTIME + 28800) / 900) * 900 - 28800 AS INT) AS a900
    ,S1MME_ATTACH_SUCC_TIMES AS S1MME_S1MME_ATTACH_SUCC_TIMES_C
    ,S1MME_EPS_ATTACH_SUCC_TIMES AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C
    ,S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL AS S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C
    ,S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES AS S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C
    ,S1MME_ATTACH_DELAY_MSEL AS S1MME_S1MME_ATTACH_DELAY_MSEL_C
    ,S1MME_EPS_ATTACH_REQ_TIMES AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C
    ,S1MME_EPS_ATTACH_DELAY_MSEL AS S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C
    ,S1MME_ATTACH_REQ_TIMES AS S1MME_S1MME_ATTACH_REQ_TIMES_C
    ,S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES AS S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C
    ,STARTTIME
FROM sdr_signal_s1mme_cgisai_15min_17938_1kw;


CREATE DATAMAP sc_mvdm_nlz_001 USING 'MV' AS
SELECT CAST(floor((STARTTIME + 28800) / 900) * 900 - 28800 AS INT) AS a900
    ,S1MME_ATTACH_SUCC_TIMES AS S1MME_S1MME_ATTACH_SUCC_TIMES_C
    ,S1MME_EPS_ATTACH_SUCC_TIMES AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C
    ,S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL AS S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C
    ,S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES AS S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C
    ,S1MME_ATTACH_DELAY_MSEL AS S1MME_S1MME_ATTACH_DELAY_MSEL_C
    ,S1MME_EPS_ATTACH_REQ_TIMES AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C
    ,S1MME_EPS_ATTACH_DELAY_MSEL AS S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C
    ,S1MME_ATTACH_REQ_TIMES AS S1MME_S1MME_ATTACH_REQ_TIMES_C
    ,S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES AS S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C
    ,DIM_LOC_4G_CGI_ENODEB.a202080101 AS a202080101
        ,STARTTIME
  FROM sdr_signal_s1mme_cgisai_15min_17938_1kw
  LEFT JOIN (
    SELECT CGISAI AS a202050101
      ,CASE
        WHEN ACCESS_TYPE_ID = 2
          THEN CGISAI
        END AS a202080101
      ,CASE
        WHEN ACCESS_TYPE_ID = 2
          THEN CELL_NAME
        END AS NAME_202080101
    FROM DIM_LOC_CGISAI
    GROUP BY CGISAI
      ,CASE
        WHEN ACCESS_TYPE_ID = 2
          THEN CGISAI
        END
      ,CASE
        WHEN ACCESS_TYPE_ID = 2
          THEN CELL_NAME
        END
    ) DIM_LOC_4G_CGI_ENODEB ON CGISAI = DIM_LOC_4G_CGI_ENODEB.a202050101;
       
 
create datamap sc_mvdm_lz_001 using 'mv' with deferred rebuild as select cdr_id,max(record_id) from tbl_data_event_410000_carbon group by cdr_id;



CREATE DATAMAP sc_mvdm_nlz_002 USING 'MV' AS
SELECT MT.a900 AS a900
  ,CASE
    WHEN COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) = 0
      THEN NULL
    ELSE CASE
        WHEN CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) AS decimal) = 0
          THEN 0
        ELSE CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS decimal) / CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) AS decimal)
        END * 100
    END AS EPC_KPI_S1_MME_ATTACH_SUCCESS_RATE
  ,COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) AS S1MME_S1MME_ATTACH_REQ_TIMES
  ,COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS S1MME_S1MME_ATTACH_SUCC_TIMES
  ,COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) - COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS EPC_KPI_S1MME_ATTACH_FAIL_TIMES
  ,CASE
    WHEN COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) = 0
      THEN NULL
    ELSE CASE
        WHEN CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS decimal) = 0
          THEN 0
        ELSE CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_DELAY_MSEL_C), 0) AS decimal) / CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS decimal)
        END
    END AS EPC_KPI_S1_MME_ATTACH_DELAY
  ,CASE
    WHEN COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) = 0
      THEN NULL
    ELSE CASE
        WHEN CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) AS decimal) = 0
          THEN 0
        ELSE CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS decimal) / CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) AS decimal)
        END * 100
    END AS EPC_KPI_S1_MME_EPS_ATTACH_SUCCESS_RATE
  ,COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES
  ,COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES
  ,COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) - COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS EPC_KPI_S1MME_EPS_ATTACH_FAIL_TIMES
  ,CASE
    WHEN COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) = 0
      THEN NULL
    ELSE CASE
        WHEN CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS decimal) = 0
          THEN 0
        ELSE CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C), 0) AS decimal) / CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS decimal)
        END
    END AS EPC_KPI_S1_MME_EPS_ATTACH_DELAY
  ,CASE
    WHEN COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) = 0
      THEN NULL
    ELSE CASE
        WHEN CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) AS decimal) = 0
          THEN 0
        ELSE CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS decimal) / CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) AS decimal)
        END * 100
    END AS EPC_KPI_S1_MME_COMBINED_EPS_IMSI_ATTACH_SUCCESS_RATE
  ,COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) AS S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES
  ,COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES
  ,COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) - COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS EPC_KPI_S1MME_COMBINED_EPS_IMSI_ATTACH_FAIL_TIMES
  ,CASE
    WHEN COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) = 0
      THEN NULL
    ELSE CASE
        WHEN CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS decimal) = 0
          THEN 0
        ELSE CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C), 0) AS decimal) / CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS decimal)
        END
    END AS EPC_KPI_S1_MME_COMBINED_EPS_IMSI_ATTACH_DELAY
  ,MT.a202080101 AS a202080101
  ,COUNT(1) OVER () AS countNum
FROM (
  SELECT CAST(floor((STARTTIME + 28800) / 900) * 900 - 28800 AS INT) AS a900
    ,S1MME_ATTACH_SUCC_TIMES AS S1MME_S1MME_ATTACH_SUCC_TIMES_C
    ,S1MME_EPS_ATTACH_SUCC_TIMES AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C
    ,S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL AS S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C
    ,S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES AS S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C
    ,S1MME_ATTACH_DELAY_MSEL AS S1MME_S1MME_ATTACH_DELAY_MSEL_C
    ,S1MME_EPS_ATTACH_REQ_TIMES AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C
    ,S1MME_EPS_ATTACH_DELAY_MSEL AS S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C
    ,S1MME_ATTACH_REQ_TIMES AS S1MME_S1MME_ATTACH_REQ_TIMES_C
    ,S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES AS S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C
    ,DIM_LOC_4G_CGI_ENODEB.a202080101 AS a202080101
  FROM sdr_signal_s1mme_cgisai_15min_17938_1kw
  LEFT JOIN (
    SELECT CGISAI AS a202050101
      ,CASE
        WHEN ACCESS_TYPE_ID = 2
          THEN CGISAI
        END AS a202080101
      ,CASE
        WHEN ACCESS_TYPE_ID = 2
          THEN CELL_NAME
        END AS NAME_202080101
    FROM DIM_LOC_CGISAI
    GROUP BY CGISAI
      ,CASE
        WHEN ACCESS_TYPE_ID = 2
          THEN CGISAI
        END
      ,CASE
        WHEN ACCESS_TYPE_ID = 2
          THEN CELL_NAME
        END
    ) DIM_LOC_4G_CGI_ENODEB ON CGISAI = DIM_LOC_4G_CGI_ENODEB.a202050101
  ) MT
GROUP BY MT.a900
  ,MT.a202080101
ORDER BY a900 ASC;

> 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

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