You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Sudheesh Katkam (JIRA)" <ji...@apache.org> on 2015/04/23 00:37:59 UTC
[jira] [Updated] (DRILL-2849) Difference in query results over CSV
file created by CTAS, compared to results over original CSV file
[ https://issues.apache.org/jira/browse/DRILL-2849?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sudheesh Katkam updated DRILL-2849:
-----------------------------------
Description:
Different results are seen for the same query over CSV data file and another CSV data file created by CTAS using the same CSV file.
Tests were executed on 4 node cluster on CentOS.
I got rid of the header information that is written by CTAS into the new CSV file that CTAS creates, and then ran my queries over CTAS' CSV file.
query over uncompressed CSV file, deletions/deletions-00000-of-00020.csv
{code}
> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions/deletions-00000-of-00020.csv` group by columns[7];
88 rows selected (6.893 seconds)
=================================================
{code}
query over CSV file that was created by CTAS. (input to CTAS was deletions/deletions-00000-of-00020.csv)
Notice there is one more record returned.
{code}
> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `csvToCSV_00000_of_00020/0_0_0.csv` group by columns[7];
89 rows selected (6.623 seconds)
==================================================
{code}
query over compressed CSV file
{code}
> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions-00000-of-00020.csv.gz` group by columns[7];
88 rows selected (10.526 seconds)
==================================================
{code}
In the below cases, the count and sum results are different when query is executed over CSV file that was created by CTAS. ( this may explain why we see the difference in results in the above queries ? )
{code}
0: jdbc:drill:> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions/deletions-00000-of-00020.csv` where columns[7] is null group by columns[7];
+------------+------------+------------+------------+------------+
| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| 252 | 1.362983396001E12 | 1.165768779027E12 | 1.293794515595635E12 | null |
+------------+------------+------------+------------+------------+
1 row selected (6.013 seconds)
0: jdbc:drill:> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions-00000-of-00020.csv.gz` where columns[7] is null group by columns[7];
+------------+------------+------------+------------+------------+
| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| 252 | 1.362983396001E12 | 1.165768779027E12 | 1.293794515595635E12 | null |
+------------+------------+------------+------------+------------+
1 row selected (8.899 seconds)
{code}
Notice that count and sum results are different (from those above) when query is executed over the CSV file created by CTAS.
{code}
0: jdbc:drill:> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `csvToCSV_00000_of_00020/0_0_0.csv` where columns[7] is null group by columns[7];
+------------+------------+------------+------------+------------+
| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| 245 | 1.349670663E12 | 1.165768779027E12 | 1.2930281335065144E12 | null |
+------------+------------+------------+------------+------------+
1 row selected (5.736 seconds)
{code}
was:
Different results are seen for the same query over CSV data file and another CSV data file created by CTAS using the same CSV file.
Tests were executed on 4 node cluster on CentOS.
I got rid of the header information that is written by CTAS into the new CSV file that CTAS creates, and then ran my queries over CTAS' CSV file.
{code}
query over uncompressed CSV file, deletions/deletions-00000-of-00020.csv
select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions/deletions-00000-of-00020.csv` group by columns[7];
88 rows selected (6.893 seconds)
=================================================
query over CSV file that was created by CTAS. (input to CTAS was deletions/deletions-00000-of-00020.csv)
Notice there is one more record returned.
select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `csvToCSV_00000_of_00020/0_0_0.csv` group by columns[7];
89 rows selected (6.623 seconds)
==================================================
query over compressed CSV file
select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions-00000-of-00020.csv.gz` group by columns[7];
88 rows selected (10.526 seconds)
==================================================
In the below cases, the count and sum results are different when query is executed over CSV file that was created by CTAS. ( this may explain why we see the difference in results in the above queries ? )
0: jdbc:drill:> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions/deletions-00000-of-00020.csv` where columns[7] is null group by columns[7];
+------------+------------+------------+------------+------------+
| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| 252 | 1.362983396001E12 | 1.165768779027E12 | 1.293794515595635E12 | null |
+------------+------------+------------+------------+------------+
1 row selected (6.013 seconds)
0: jdbc:drill:> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions-00000-of-00020.csv.gz` where columns[7] is null group by columns[7];
+------------+------------+------------+------------+------------+
| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| 252 | 1.362983396001E12 | 1.165768779027E12 | 1.293794515595635E12 | null |
+------------+------------+------------+------------+------------+
1 row selected (8.899 seconds)
Notice that count and sum results are different (from those above) when query is executed over the CSV file created by CTAS.
0: jdbc:drill:> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `csvToCSV_00000_of_00020/0_0_0.csv` where columns[7] is null group by columns[7];
+------------+------------+------------+------------+------------+
| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| 245 | 1.349670663E12 | 1.165768779027E12 | 1.2930281335065144E12 | null |
+------------+------------+------------+------------+------------+
1 row selected (5.736 seconds)
{code}
> Difference in query results over CSV file created by CTAS, compared to results over original CSV file
> ------------------------------------------------------------------------------------------------------
>
> Key: DRILL-2849
> URL: https://issues.apache.org/jira/browse/DRILL-2849
> Project: Apache Drill
> Issue Type: Bug
> Components: Storage - Text & CSV
> Affects Versions: 0.9.0
> Environment: 64e3ec52b93e9331aa5179e040eca19afece8317 | DRILL-2611: value vectors should report valid value count | 16.04.2015 @ 13:53:34 EDT
> Reporter: Khurram Faraaz
> Assignee: Steven Phillips
>
> Different results are seen for the same query over CSV data file and another CSV data file created by CTAS using the same CSV file.
> Tests were executed on 4 node cluster on CentOS.
> I got rid of the header information that is written by CTAS into the new CSV file that CTAS creates, and then ran my queries over CTAS' CSV file.
> query over uncompressed CSV file, deletions/deletions-00000-of-00020.csv
> {code}
> > select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions/deletions-00000-of-00020.csv` group by columns[7];
> 88 rows selected (6.893 seconds)
> =================================================
> {code}
> query over CSV file that was created by CTAS. (input to CTAS was deletions/deletions-00000-of-00020.csv)
> Notice there is one more record returned.
> {code}
> > select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `csvToCSV_00000_of_00020/0_0_0.csv` group by columns[7];
>
> 89 rows selected (6.623 seconds)
> ==================================================
> {code}
> query over compressed CSV file
> {code}
> > select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions-00000-of-00020.csv.gz` group by columns[7];
> 88 rows selected (10.526 seconds)
> ==================================================
> {code}
> In the below cases, the count and sum results are different when query is executed over CSV file that was created by CTAS. ( this may explain why we see the difference in results in the above queries ? )
> {code}
> 0: jdbc:drill:> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions/deletions-00000-of-00020.csv` where columns[7] is null group by columns[7];
> +------------+------------+------------+------------+------------+
> | EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
> +------------+------------+------------+------------+------------+
> | 252 | 1.362983396001E12 | 1.165768779027E12 | 1.293794515595635E12 | null |
> +------------+------------+------------+------------+------------+
> 1 row selected (6.013 seconds)
> 0: jdbc:drill:> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `deletions-00000-of-00020.csv.gz` where columns[7] is null group by columns[7];
> +------------+------------+------------+------------+------------+
> | EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
> +------------+------------+------------+------------+------------+
> | 252 | 1.362983396001E12 | 1.165768779027E12 | 1.293794515595635E12 | null |
> +------------+------------+------------+------------+------------+
> 1 row selected (8.899 seconds)
> {code}
> Notice that count and sum results are different (from those above) when query is executed over the CSV file created by CTAS.
> {code}
> 0: jdbc:drill:> select count(cast(columns[0] as double)),max(cast(columns[0] as double)),min(cast(columns[0] as double)),avg(cast(columns[0] as double)), columns[7] from `csvToCSV_00000_of_00020/0_0_0.csv` where columns[7] is null group by columns[7];
> +------------+------------+------------+------------+------------+
> | EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
> +------------+------------+------------+------------+------------+
> | 245 | 1.349670663E12 | 1.165768779027E12 | 1.2930281335065144E12 | null |
> +------------+------------+------------+------------+------------+
> 1 row selected (5.736 seconds)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)