Wrong result/data loss when using Filter Pushdown and Union_By_Name about duckdb

[szarnyasg]

Hi @hieunm89, thanks for opening this issue. While trying to reproduce this issue, I ran into the same problem as I found when trying to reproduce #11694. Back then, I didn't follow up on this (apologies for that) but let's try to find the root cause of this now. When I run any of your queries, I get an error:

select tech_type,count(*)
  from read_csv(['tech_type=[3-4]G/ems_key=*/day_key=*/*/NodeBLocalCell.csv'], AUTO_DETECT=TRUE, union_by_name = true)
  group by 1;

Invalid Input Error: Unknown hive_type: "tech_type" does not appear to be a partition

[hieunm89]

Hi @szarnyasg

What client of duckdb are you using? I'm using JDBC and Python and It works fine.
Could you try again with hive_partitioning = true and hive_types={'tech_type': string} option?

select tech_type,count(*)
  from read_csv('tech_type=[3-4]G/ems_key=*/day_key=*/*/NodeBLocalCell.csv', AUTO_DETECT=TRUE, union_by_name = true,hive_partitioning = true,hive_types={'tech_type': string})
  group by 1;

[szarnyasg]

Hi @hieunm89, thanks, I found the culprit – on macOS, the paths need to be prepended with ./ due to a different bug. I adjusted the queries in your original post to match this and I could reproduce the error now. We'll take a look.