[Minor Issue] BETWEEN AND does work as expected

[aaron]

Hi Community,

The BETWEEN AND work as >= AND <, I guess is should be  >= AND <=. My env is
spark2.2.2 + carbondata1.4.1

%Carbondata

scala> carbon.time(carbon.sql(
     |       s"""SELECT timeseries(date, 'DAY') as day, market_code,
device_code, country_code, category_id,
     |          |sum(est_free_app_download), sum(est_paid_app_download),
sum(est_revenue)
     |          |FROM store WHERE date BETWEEN '2016-09-01' AND '2016-09-06'
AND device_code='ios-phone' AND country_code='EE' AND category_id=100021
     |          |GROUP BY timeseries(date, 'DAY'), market_code, device_code,
country_code, category_id"""
     |         .stripMargin).show(truncate=false)
     |     )
+-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
|day              
|market_code|device_code|country_code|category_id|sum(est_free_app_download)|sum(est_paid_app_download)|sum(est_revenue)|
+-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
|2016-09-02 00:00:00|apple-store|ios-phone  |EE          |100021     |30807                    
|14092                     |648             |
|2016-09-04 00:00:00|apple-store|ios-phone  |EE          |100021     |32137                    
|14088                     |875             |
|2016-09-05 00:00:00|apple-store|ios-phone  |EE          |100021     |30774                    
|14083                     |930             |
|2016-09-01 00:00:00|apple-store|ios-phone  |EE          |100021     |30408                    
|14096                     |932             |
|2016-09-03 00:00:00|apple-store|ios-phone  |EE          |100021     |32476                    
|14101                     |818             |
+-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+




%pyspark

(
  spark.read
 
.parquet("s3a://b2b-prod-int-data-pipeline-unified/unified/app-ss.storeint.v1/metric")
  .where("date BETWEEN '2016-09-01' AND '2016-09-06' AND
device_code='ios-phone' AND country_code='EE' AND category_id=100021")
  .groupBy("date", "market_code", "device_code", "country_code",
"category_id")
  .agg({"est_free_app_download": "sum", "est_paid_app_download": "sum",
"est_revenue": "sum"})
  .show()
)

+----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
|    
date|market_code|device_code|country_code|category_id|sum(est_free_app_download)|sum(est_paid_app_download)|sum(est_revenue)|
+----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
|2016-09-04|apple-store|  ios-phone|          EE|     100021|                    
32137|                     14088|             875|
|2016-09-06|apple-store|  ios-phone|          EE|     100021|                    
31425|                     14103|             893|
|2016-09-01|apple-store|  ios-phone|          EE|     100021|                    
30408|                     14096|             932|
|2016-09-05|apple-store|  ios-phone|          EE|     100021|                    
30774|                     14083|             930|
|2016-09-03|apple-store|  ios-phone|          EE|     100021|                    
32476|                     14101|             818|
|2016-09-02|apple-store|  ios-phone|          EE|     100021|                    
30807|                     14092|             648|
+----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+




--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/

[kumarvishal09]

Hi Aaron,
I am able to reproduce this issue. I have raised PR:
https://github.com/apache/carbondata/pull/2787

-Regards
Kumar Vishal

On Thu, Sep 27, 2018 at 2:24 PM aaron <[hidden email]> wrote:

> Hi Community,
>
> The BETWEEN AND work as >= AND <, I guess is should be  >= AND <=. My env
> is
> spark2.2.2 + carbondata1.4.1
>
> %Carbondata
>
> scala> carbon.time(carbon.sql(
>      |       s"""SELECT timeseries(date, 'DAY') as day, market_code,
> device_code, country_code, category_id,
>      |          |sum(est_free_app_download), sum(est_paid_app_download),
> sum(est_revenue)
>      |          |FROM store WHERE date BETWEEN '2016-09-01' AND
> '2016-09-06'
> AND device_code='ios-phone' AND country_code='EE' AND category_id=100021
>      |          |GROUP BY timeseries(date, 'DAY'), market_code,
> device_code,
> country_code, category_id"""
>      |         .stripMargin).show(truncate=false)
>      |     )
>
> +-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
> |day
>
> |market_code|device_code|country_code|category_id|sum(est_free_app_download)|sum(est_paid_app_download)|sum(est_revenue)|
>
> +-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
> |2016-09-02 00:00:00|apple-store|ios-phone  |EE          |100021
>  |30807
> |14092                     |648             |
> |2016-09-04 00:00:00|apple-store|ios-phone  |EE          |100021
>  |32137
> |14088                     |875             |
> |2016-09-05 00:00:00|apple-store|ios-phone  |EE          |100021
>  |30774
> |14083                     |930             |
> |2016-09-01 00:00:00|apple-store|ios-phone  |EE          |100021
>  |30408
> |14096                     |932             |
> |2016-09-03 00:00:00|apple-store|ios-phone  |EE          |100021
>  |32476
> |14101                     |818             |
>
> +-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
>
>
>
>
> %pyspark
>
> (
>   spark.read
>
>
> .parquet("s3a://b2b-prod-int-data-pipeline-unified/unified/app-ss.storeint.v1/metric")
>   .where("date BETWEEN '2016-09-01' AND '2016-09-06' AND
> device_code='ios-phone' AND country_code='EE' AND category_id=100021")
>   .groupBy("date", "market_code", "device_code", "country_code",
> "category_id")
>   .agg({"est_free_app_download": "sum", "est_paid_app_download": "sum",
> "est_revenue": "sum"})
>   .show()
> )
>
>
> +----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
> |
>
> date|market_code|device_code|country_code|category_id|sum(est_free_app_download)|sum(est_paid_app_download)|sum(est_revenue)|
>
> +----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
> |2016-09-04|apple-store|  ios-phone|          EE|     100021|
>
> 32137|                     14088|             875|
> |2016-09-06|apple-store|  ios-phone|          EE|     100021|
>
> 31425|                     14103|             893|
> |2016-09-01|apple-store|  ios-phone|          EE|     100021|
>
> 30408|                     14096|             932|
> |2016-09-05|apple-store|  ios-phone|          EE|     100021|
>
> 30774|                     14083|             930|
> |2016-09-03|apple-store|  ios-phone|          EE|     100021|
>
> 32476|                     14101|             818|
> |2016-09-02|apple-store|  ios-phone|          EE|     100021|
>
> 30807|                     14092|             648|
>
> +----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
>
>
>
>
> --
> Sent from:
> http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
>

[aaron]

Cool! Thanks a lot for your effort!



--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/