the external file cannot be joined?

[l1t1]

I use this python script from #78

query the single file is ok.

> select count(*) from external('./t17.csv',COLUMNS => DESCRIPTOR(id int, owner int));
"count"
1499001

> select count(*) from external('./t17_2.csv',COLUMNS => DESCRIPTOR(id int, owner int));
"count"
5001

query the two files failed

> select a.owner, count(*) from external('./t17.csv',COLUMNS => DESCRIPTOR(id int, owner int)) as a,external('./t17_2.csv',COLUMNS => DESCRIPTOR(id int, owner i
nt)) as b where a.id=b.id group by a.owner ;
Error executing SQL: invalid input syntax for integer: file: './t17_2.csv' line:1 column:1

[l1t1]

btw, the csv file has the header.

I add the HEADER clause, it works now, I wonder why the 1st sql select count(*) from external('./t17.csv',COLUMNS => DESCRIPTOR(id int, owner int)) didn't raise an error?

select a.owner, count(*) from external('./t17.csv',COLUMNS => DESCRIPTOR(id int, owner int),HEADER => true ) as a,external('./t17_2.csv',COLUMNS => DESCRIPTOR(id int, owner int),HEADER => true) as b where a.id=b.id group by a.owner ;

[wolfroediger]

You are correct, the header is the problem. The single file queries did not fail because count(*) will just count the number of lines in the CSV file. It does not type-check the values for all the columns. So, in your example Hyper does not check that the values for id and owner are valid integers. Since you did not specify header the count should also be off by one.

The following query will also fail for a single file since now Hyper checks that the values for owner are valid integers:

select owner, count(*) from external('./t17.csv',COLUMNS => DESCRIPTOR(id int, owner int)) group by owner;

[l1t1]

thanks @wolfroediger