[jira] [Commented] (CARBONDATA-1424) Delete Operation working incorrectly when subquery returns bad-record

[Akash R Nilugal (Jira)]

    [ https://issues.apache.org/jira/browse/CARBONDATA-1424?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16156924#comment-16156924 ]

Sangeeta Gulia commented on CARBONDATA-1424:
--------------------------------------------

Thanks for the information [~ravi.pesala]. I have verified the above query, its working as u told.

But, I have found one thing which is a bit confusing. I tried to break down the first query into two queries, Ideally the result of both should come similar but this is not the case. However it is working the same way in hive as in carbondata.

Below are my queries and its result, first query display only 1 record whereas third query gives 13 records. However both should give same output.

QUERY1 ::::::::::: select * from uniqdata1 where cust_id in (select cust_id from uniqdata1 limit 10);
+----------+------------+----------------------+-------+-------+-----------------+-----------------+------------------+------------------+-----------------+-----------------+------------------+--+
| CUST_ID  | CUST_NAME  | ACTIVE_EMUI_VERSION  |  DOB  |  DOJ  | BIGINT_COLUMN1  | BIGINT_COLUMN2  | DECIMAL_COLUMN1  | DECIMAL_COLUMN2  | Double_COLUMN1  | Double_COLUMN2  | INTEGER_COLUMN1  |
+----------+------------+----------------------+-------+-------+-----------------+-----------------+------------------+------------------+-----------------+-----------------+------------------+--+
| 8999     |            |                      | NULL  | NULL  | NULL            | NULL            | NULL             | NULL             | NULL            | NULL            | NULL             |
+----------+------------+----------------------+-------+-------+-----------------+-----------------+------------------+------------------+-----------------+-----------------+------------------+--+
1 row selected (10.485 seconds)
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000>
QUERY2 > select cust_id from uniqdata1 limit 10;
+----------+--+
| cust_id  |
+----------+--+
| NULL     |
| 8999     |
| NULL     |
| NULL     |
| NULL     |
| NULL     |
| NULL     |
| NULL     |
| NULL     |
| NULL     |
+----------+--+
10 rows selected (0.225 seconds)
QUERY3> select * from uniqdata1 where cust_id in (NULL,8999);
+----------+------------------+----------------------------+------------------------+------------------------+-----------------+-----------------+-------------------------+-------------------------+----------------------+-----------------------+------------------+--+
| CUST_ID  |    CUST_NAME     |    ACTIVE_EMUI_VERSION     |          DOB           |          DOJ           | BIGINT_COLUMN1  | BIGINT_COLUMN2  |     DECIMAL_COLUMN1     |     DECIMAL_COLUMN2     |    Double_COLUMN1    |    Double_COLUMN2     | INTEGER_COLUMN1  |
+----------+------------------+----------------------------+------------------------+------------------------+-----------------+-----------------+-------------------------+-------------------------+----------------------+-----------------------+------------------+--+
| NULL     |                  |                            | NULL                   | NULL                   | NULL            | NULL            | NULL                    | NULL                    | NULL                 | NULL                  | NULL             |
| 8999     |                  |                            | NULL                   | NULL                   | NULL            | NULL            | NULL                    | NULL                    | NULL                 | NULL                  | NULL             |
| NULL     |                  |                            | NULL                   | NULL                   | 1233720368578   | NULL            | NULL                    | NULL                    | NULL                 | NULL                  | NULL             |
| NULL     |                  |                            | NULL                   | NULL                   | NULL            | -223372036854   | NULL                    | NULL                    | NULL                 | NULL                  | NULL             |
| NULL     |                  |                            | NULL                   | NULL                   | NULL            | NULL            | 12345678901.1234000058  | NULL                    | NULL                 | NULL                  | NULL             |
| NULL     |                  |                            | NULL                   | NULL                   | NULL            | NULL            | NULL                    | 22345678901.1234000059  | NULL                 | NULL                  | NULL             |
| NULL     |                  |                            | NULL                   | NULL                   | NULL            | NULL            | NULL                    | NULL                    | 1.12345674897976E10  | NULL                  | NULL             |
| NULL     |                  |                            | NULL                   | NULL                   | NULL            | NULL            | NULL                    | NULL                    | NULL                 | -1.12345674897976E10  | NULL             |
| NULL     |                  |                            | NULL                   | NULL                   | NULL            | NULL            | NULL                    | NULL                    | NULL                 | NULL                  | 0                |
| NULL     |                  |                            | NULL                   | 1970-01-01 12:00:03.0  | NULL            | NULL            | NULL                    | NULL                    | NULL                 | NULL                  | NULL             |
| NULL     |                  |                            | 1970-01-01 11:00:03.0  | NULL                   | NULL            | NULL            | NULL                    | NULL                    | NULL                 | NULL                  | NULL             |
| NULL     |                  | ACTIVE_EMUI_VERSION_00000  | NULL                   | NULL                   | NULL            | NULL            | NULL                    | NULL                    | NULL                 | NULL                  | NULL             |
| NULL     | CUST_NAME_00000  |                            | NULL                   | NULL                   | NULL            | NULL            | NULL                    | NULL                    | NULL                 | NULL                  | NULL             |
+----------+------------------+----------------------------+------------------------+------------------------+-----------------+-----------------+-------------------------+-------------------------+----------------------+-----------------------+------------------+--+
13 rows selected (0.369 seconds)
0: jdbc:hive2://localhost:10000>

> Delete Operation working incorrectly when subquery returns bad-record
> ---------------------------------------------------------------------
>
>                 Key: CARBONDATA-1424
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-1424
>             Project: CarbonData
>          Issue Type: Bug
>          Components: sql, test
>    Affects Versions: 1.2.0
>            Reporter: Sangeeta Gulia
>            Assignee: Ravindra Pesala
>            Priority: Minor
>         Attachments: 3000_UniqData.csv
>
>
> Delete Operation is working incorrectly when subquery returns bad-record for a particular table.
> For the given query,
> delete from uniqdata_delete where cust_id in (select cust_id from uniqdata_delete limit 10);
> As an Example, if "select cust_id from uniqdata_delete limit 10" returns  
> +----------+--+
> | cust_id  |
> +----------+--+
> | NULL     |
> | NULL     |
> | NULL     |
> | NULL     |
> | 11000    |
> | 11001     |
> | 11002     |
> | 11003     |
> | 11004     |
> | 11005    |
> +----------+--+
> then the query should delete all rows where cust_id is Null or matches any values from the returned values(11000-11005) whereas it deletes only those records where customer id is from (11000-11005).
> I have attached the sample csv file which i have used for reference.
> To Regenerate the issue, you can use below commands :
> CREATE TABLE uniqdata_delete (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");
> LOAD DATA INPATH 'hdfs://localhost:54310/user/hduser/input-files/3000_UniqData.csv' into table uniqdata_delete OPTIONS('FILEHEADER'='CUST_ID,CUST_NAME,ACTIVE_EMUI_VERSION,DOB,DOJ,BIGINT_COLUMN1,BIGINT_COLUMN2,DECIMAL_COLUMN1,DECIMAL_COLUMN2,Double_COLUMN1,Double_COLUMN2,INTEGER_COLUMN1');
> NOTE : Load should be such that starting rows of data should have null stored for cust_id field.
> delete from uniqdata_delete where cust_id in (select cust_id from uniqdata_delete limit 10);

--
This message was sent by Atlassian JIRA
(v6.4.14#64029)