This is Presto's functionality as the default null ordering in presto is Nulls LAST i.e null values are considered in last regardless of the ordering direction. If you want the null values to be considered first then you can use the following query:
select CUST_ID as a from uniqdata_with_null order by a asc nulls first limit 10;
> Incorrect result displays while using limit in presto integration
> -----------------------------------------------------------------
>
> Key: CARBONDATA-994
> URL:
https://issues.apache.org/jira/browse/CARBONDATA-994> Project: CarbonData
> Issue Type: Improvement
> Components: data-query, presto-integration
> Affects Versions: 1.1.0
> Environment: spark 2.1, presto 0.166
> Reporter: Vandana Yadav
> Priority: Minor
> Attachments: 2000_UniqData.csv
>
>
> Incorrect result displays while using limit in presto integration(in carbondata null values appear first so result contain null values and records but in presto null values appear at last so result contain only records so resultset is different in both)
> Steps to reproduce :
> 1. In CarbonData:
> a) Create table:
> CREATE TABLE uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES ("TABLE_BLOCKSIZE"= "256 MB");
> b) Load data :
> LOAD DATA INPATH 'hdfs://localhost:54310/2000_UniqData.csv' into table uniqdata OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','BAD_RECORDS_ACTION'='FORCE','FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1');
> 2. In presto
> a) Execute the query:
> select CUST_ID as a from uniqdata order by a asc limit 10
> Expected result: it should display cust_id as per limit
> Actual result :
> In CarbonData:
> "+-------+--+
> | a |
> +-------+--+
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> +-------+--+
> 10 rows selected (0.22 seconds)"
> In presto:
> " a
> ------
> 8999
> 9000
> 9001
> 9002
> 9003
> 9004
> 9005
> 9006
> 9007
> 9008
> (10 rows)
> Query 20170420_071958_00026_hd7jy, FINISHED, 1 node
> Splits: 18 total, 18 done (100.00%)
> 0:00 [2.01K rows, 1.97KB] [8.2K rows/s, 8.02KB/s]"
Locked
