[jira] [Created] (CARBONDATA-650) Columns switching error in performing the string functions

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[jira] [Created] (CARBONDATA-650) Columns switching error in performing the string functions

Akash R Nilugal (Jira)
Harsh Sharma created CARBONDATA-650:
---------------------------------------

             Summary: Columns switching error in performing the string functions
                 Key: CARBONDATA-650
                 URL: https://issues.apache.org/jira/browse/CARBONDATA-650
             Project: CarbonData
          Issue Type: Bug
          Components: data-load
    Affects Versions: 1.1.0
         Environment: Spark 2.1 Cluster mode
            Reporter: Harsh Sharma
            Priority: Minor


We tried to execute the string function ASCII(COLUMN_NAME) on the Carbon Table in insert query with the following scenario,

- Create carbon tables as :
CREATE TABLE target_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");

CREATE TABLE source_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");

 - Load records into source carbon table as :
LOAD DATA inpath 'hdfs://hadoop-master:54311/data/uniqdata/2000_UniqData.csv' INTO table source_uniqdata options('DELIMITER'=',', 'FILEHEADER'='CUST_ID, CUST_NAME, ACTIVE_EMUI_VERSION, DOB, DOJ, BIGINT_COLUMN1, BIGINT_COLUMN2, DECIMAL_COLUMN1, DECIMAL_COLUMN2, Double_COLUMN1, Double_COLUMN2, INTEGER_COLUMN1');

 - Insert the records of source table into target table and apply the string function

insert into table target_uniqdata select CUST_ID, ASCII(CUST_NAME), ACTIVE_EMUI_VERSION, DOB, DOJ, BIGINT_COLUMN1, BIGINT_COLUMN2, DECIMAL_COLUMN1, DECIMAL_COLUMN2, Double_COLUMN1, Double_COLUMN2, INTEGER_COLUMN1 from source_uniqdata;


Below is the order of columns in the source_uniqdata table,

--+-------------------------+-------------------------+----------------------+-----------------------+------------------+--+
|    CUST_NAME     |    ACTIVE_EMUI_VERSION     |          DOB           |          DOJ           | CUST_ID  | BIGINT_COLUMN1  | BIGINT_COLUMN2  |     DECIMAL_COLUMN1     |     DECIMAL_COLUMN2     |    Double_COLUMN1    |    Double_COLUMN2     | INTEGER_COLUMN1  |
+------------------+----------------------------+------------------------+------------------------+----------+-----------------+-----------------+-------------------------+-------------------------+----------------------+-----------------------+------------------+--+
| CUST_NAME_00987  | ACTIVE_EMUI_VERSION_00987  | 1972-09-14 01:00:03.0  | 1972-09-14 02:00:03.0  | 9987     | 123372037841    | -223372035867   | 12345679888.1234000000  | 22345679888.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 988              |
| CUST_NAME_00988  | ACTIVE_EMUI_VERSION_00988  | 1972-09-15 01:00:03.0  | 1972-09-15 02:00:03.0  | 9988     | 123372037842    | -223372035866   | 12345679889.1234000000  | 22345679889.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 989              |
| CUST_NAME_00989  | ACTIVE_EMUI_VERSION_00989  | 1972-09-16 01:00:03.0  | 1972-09-16 02:00:03.0  | 9989     | 123372037843    | -223372035865   | 12345679890.1234000000  | 22345679890.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 990              |
| CUST_NAME_00990  | ACTIVE_EMUI_VERSION_00990  | 1972-09-17 01:00:03.0  | 1972-09-17 02:00:03.0  | 9990     | 123372037844    | -223372035864   | 12345679891.1234000000  | 22345679891.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 991              |
| CUST_NAME_00991  | ACTIVE_EMUI_VERSION_00991  | 1972-09-18 01:00:03.0  | 1972-09-18 02:00:03.0  | 9991     | 123372037845    | -223372035863   | 12345679892.1234000000  | 22345679892.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 992              |
| CUST_NAME_00992  | ACTIVE_EMUI_VERSION_00992  | 1972-09-19 01:00:03.0  | 1972-09-19 02:00:03.0  | 9992     | 123372037846    | -223372035862   | 12345679893.1234000000  | 22345679893.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 993              |
| CUST_NAME_00993  | ACTIVE_EMUI_VERSION_00993  | 1972-09-20 01:00:03.0  | 1972-09-20 02:00:03.0  | 9993     | 123372037847    | -223372035861   | 12345679894.1234000000  | 22345679894.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 994              |
| CUST_NAME_00994  | ACTIVE_EMUI_VERSION_00994  | 1972-09-21 01:00:03.0  | 1972-09-21 02:00:03.0  | 9994     | 123372037848    | -223372035860   | 12345679895.1234000000  | 22345679895.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 995              |
| CUST_NAME_00995  | ACTIVE_EMUI_VERSION_00995  | 1972-09-22 01:00:03.0  | 1972-09-22 02:00:03.0  | 9995     | 123372037849    | -223372035859   | 12345679896.1234000000  | 22345679896.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 996              |
| CUST_NAME_00996  | ACTIVE_EMUI_VERSION_00996  | 1972-09-23 01:00:03.0  | 1972-09-23 02:00:03.0  | 9996     | 123372037850    | -223372035858   | 12345679897.1234000000  | 22345679897.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 997              |
| CUST_NAME_00997  | ACTIVE_EMUI_VERSION_00997  | 1972-09-24 01:00:03.0  | 1972-09-24 02:00:03.0  | 9997     | 123372037851    | -223372035857   | 12345679898.1234000000  | 22345679898.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 998              |
| CUST_NAME_00998  | ACTIVE_EMUI_VERSION_00998  | 1972-09-25 01:00:03.0  | 1972-09-25 02:00:03.0  | 9998     | 123372037852    | -223372035856   | 12345679899.1234000000  | 22345679899.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 999              |
| CUST_NAME_00999  | ACTIVE_EMUI_VERSION_00999  | 1972-09-26 01:00:03.0  | 1972-09-26 02:00:03.0  | 9999     | 123372037853    | -223372035855   | 12345679900.1234000000  | 22345679900.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1000             |

::::: AFTER ASCII OPERATION ::::SPARK 2.1::::::

-+-------------------------+----------------------+-----------------------+------------------+--+
| CUST_NAME  | ACTIVE_EMUI_VERSION  |  DOB  |          DOJ           |  CUST_ID   | BIGINT_COLUMN1  | BIGINT_COLUMN2  |     DECIMAL_COLUMN1     |     DECIMAL_COLUMN2     |    Double_COLUMN1    |    Double_COLUMN2     | INTEGER_COLUMN1  |
+------------+----------------------+-------+------------------------+------------+-----------------+-----------------+-------------------------+-------------------------+----------------------+-----------------------+------------------+--+
| 9987       | 67                   | NULL  | 1972-09-14 01:00:03.0  | 85264203   | 123372037841    | -223372035867   | 12345679888.1234000000  | 22345679888.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 988              |
| 9988       | 67                   | NULL  | 1972-09-15 01:00:03.0  | 85350603   | 123372037842    | -223372035866   | 12345679889.1234000000  | 22345679889.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 989              |
| 9989       | 67                   | NULL  | 1972-09-16 01:00:03.0  | 85437003   | 123372037843    | -223372035865   | 12345679890.1234000000  | 22345679890.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 990              |
| 9990       | 67                   | NULL  | 1972-09-17 01:00:03.0  | 85523403   | 123372037844    | -223372035864   | 12345679891.1234000000  | 22345679891.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 991              |
| 9991       | 67                   | NULL  | 1972-09-18 01:00:03.0  | 85609803   | 123372037845    | -223372035863   | 12345679892.1234000000  | 22345679892.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 992              |
| 9992       | 67                   | NULL  | 1972-09-19 01:00:03.0  | 85696203   | 123372037846    | -223372035862   | 12345679893.1234000000  | 22345679893.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 993              |
| 9993       | 67                   | NULL  | 1972-09-20 01:00:03.0  | 85782603   | 123372037847    | -223372035861   | 12345679894.1234000000  | 22345679894.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 994              |
| 9994       | 67                   | NULL  | 1972-09-21 01:00:03.0  | 85869003   | 123372037848    | -223372035860   | 12345679895.1234000000  | 22345679895.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 995              |
| 9995       | 67                   | NULL  | 1972-09-22 01:00:03.0  | 85955403   | 123372037849    | -223372035859   | 12345679896.1234000000  | 22345679896.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 996              |
| 9996       | 67                   | NULL  | 1972-09-23 01:00:03.0  | 86041803   | 123372037850    | -223372035858   | 12345679897.1234000000  | 22345679897.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 997              |
| 9997       | 67                   | NULL  | 1972-09-24 01:00:03.0  | 86128203   | 123372037851    | -223372035857   | 12345679898.1234000000  | 22345679898.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 998              |
| 9998       | 67                   | NULL  | 1972-09-25 01:00:03.0  | 86214603   | 123372037852    | -223372035856   | 12345679899.1234000000  | 22345679899.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 999              |
| 9999       | 67                   | NULL  | 1972-09-26 01:00:03.0  | 86301003   | 123372037853    | -223372035855   | 12345679900.1234000000  | 22345679900.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1000             |
+------------+----------------------+-------+------------------------+------------+-----------------+-----------------+-------------------------+-------------------------+----------------------+-----------------------+------------------+--+

As you can see the values of the columns have been switched.


::::: AFTER ASCII OPERATION ::::SPARK 1.6::::::

+----------+------------+----------------------------+------------------------+------------------------+-----------------+-----------------+-------------------------+-------------------------+----------------------+-----------------------+------------------+--+
| cust_id  | cust_name  |    active_emui_version     |          dob           |          doj           | bigint_column1  | bigint_column2  |     decimal_column1     |     decimal_column2     |    double_column1    |    double_column2     | integer_column1  |
+----------+------------+----------------------------+------------------------+------------------------+-----------------+-----------------+-------------------------+-------------------------+----------------------+-----------------------+------------------+--+
| 10801    | 67         | ACTIVE_EMUI_VERSION_01801  | 1974-12-07 01:00:03.0  | 1974-12-07 02:00:03.0  | 123372038655    | -223372035053   | 12345680702.1234000000  | 22345680702.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1802             |
| 10802    | 67         | ACTIVE_EMUI_VERSION_01802  | 1974-12-08 01:00:03.0  | 1974-12-08 02:00:03.0  | 123372038656    | -223372035052   | 12345680703.1234000000  | 22345680703.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1803             |
| 10803    | 67         | ACTIVE_EMUI_VERSION_01803  | 1974-12-09 01:00:03.0  | 1974-12-09 02:00:03.0  | 123372038657    | -223372035051   | 12345680704.1234000000  | 22345680704.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1804             |
| 10804    | 67         | ACTIVE_EMUI_VERSION_01804  | 1974-12-10 01:00:03.0  | 1974-12-10 02:00:03.0  | 123372038658    | -223372035050   | 12345680705.1234000000  | 22345680705.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1805             |
| 10805    | 67         | ACTIVE_EMUI_VERSION_01805  | 1974-12-11 01:00:03.0  | 1974-12-11 02:00:03.0  | 123372038659    | -223372035049   | 12345680706.1234000000  | 22345680706.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1806             |
| 10806    | 67         | ACTIVE_EMUI_VERSION_01806  | 1974-12-12 01:00:03.0  | 1974-12-12 02:00:03.0  | 123372038660    | -223372035048   | 12345680707.1234000000  | 22345680707.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1807             |
| 10807    | 67         | ACTIVE_EMUI_VERSION_01807  | 1974-12-13 01:00:03.0  | 1974-12-13 02:00:03.0  | 123372038661    | -223372035047   | 12345680708.1234000000  | 22345680708.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1808             |
| 10808    | 67         | ACTIVE_EMUI_VERSION_01808  | 1974-12-14 01:00:03.0  | 1974-12-14 02:00:03.0  | 123372038662    | -223372035046   | 12345680709.1234000000  | 22345680709.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1809             |
| 10809    | 67         | ACTIVE_EMUI_VERSION_01809  | 1974-12-15 01:00:03.0  | 1974-12-15 02:00:03.0  | 123372038663    | -223372035045   | 12345680710.1234000000  | 22345680710.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1810             |
| 10810    | 67         | ACTIVE_EMUI_VERSION_01810  | 1974-12-16 01:00:03.0  | 1974-12-16 02:00:03.0  | 123372038664    | -223372035044   | 12345680711.1234000000  | 22345680711.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1811             |
| 10811    | 67         | ACTIVE_EMUI_VERSION_01811  | 1974-12-17 01:00:03.0  | 1974-12-17 02:00:03.0  | 123372038665    | -223372035043   | 12345680712.1234000000  | 22345680712.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1812             |
| 10812    | 67         | ACTIVE_EMUI_VERSION_01812  | 1974-12-18 01:00:03.0  | 1974-12-18 02:00:03.0  | 123372038666    | -223372035042   | 12345680713.1234000000  | 22345680713.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1813             |
| 10813    | 67         | ACTIVE_EMUI_VERSION_01813  | 1974-12-19 01:00:03.0  | 1974-12-19 02:00:03.0  | 123372038667    | -223372035041   | 12345680714.1234000000  | 22345680714.1234000000  | 1.12345674897976E10  | -1.12345674897976E10  | 1814             |

Here, its working in Spark 1.6



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)