Error with partitioned dataset: Cannot infer common argument type for comparison operation Binary != Utf8 #7039

Activity

1. alamb

   added

   bugSomething isn't workingSomething isn't working

   on Jul 20, 2023
2. alamb

   changed the title [-]ClickBench error with partitioned dataset: Cannot infer common argument type for comparison operation Binary != Utf8[/-] [+]Error with partitioned dataset: Cannot infer common argument type for comparison operation Binary != Utf8[/+] on Jul 24, 2023
3. jonahgao commented on Jul 24, 2023

   

   jonahgao

   on Jul 24, 2023

   Last edited by jonahgao

   Member

   More actions

   The schemas of these two datasets in Parquet format are different.

   $ parquet-schema hits.parquet | grep MobilePhoneModel
     REQUIRED BYTE_ARRAY MobilePhoneModel (STRING);
   
   $ parquet-schema hits_partitioned/hits_0.parquet | grep MobilePhoneModel
     OPTIONAL BYTE_ARRAY MobilePhoneModel;
   

   The single file dataset has specified the logical type as String for MobilePhoneModel, but the partitioned one does not have that specification.

   In the partitioned dataset, the type inference for the MobilePhoneModel is Binary.

   I think we may need to support type coercion from Utf8 to Binary here.
4. alamb commented on Jul 24, 2023

   

   alamb

   on Jul 24, 2023

   ContributorAuthor

   More actions

   Thank you for the investigation @jonahgao -- think coercion for binary --> UTF8 would make sense. I will try and find some time to work on this if no one beats me to it.

   🤔 Or maybe I will file a ticket and see if someone else wants to work on it.
5. jonahgao commented on Jul 25, 2023

   

   jonahgao

   on Jul 25, 2023

   Last edited by jonahgao

   Member

   More actions

   #7080 should make the query execution successful.
   But the MobilePhoneModel displayed in binary format.

   +-----------------------------------+---------+
   | hits_partitioned.MobilePhoneModel | u       |
   +-----------------------------------+---------+
   | 69506164                          | 1090347 |
   | 6950686f6e65                      | 45758   |
   | 41353030                          | 16046   |
   | 4e382d3030                        | 5565    |
   | 6950686f                          | 3300    |
   | 4f4e4520544f554348203630333041    | 2759    |
   | 47542d503733303042                | 1907    |
   | 33313130303030                    | 1871    |
   | 47542d4939353030                  | 1598    |
   | 6561676c653735                    | 1492    |
   +-----------------------------------+---------+
   

   It would be better if we could manually specify the data type of the columns, which is not currently supported for Parquet.
   I think that we can do this safely when we are certain that the binary data is valid UTF-8.

   create external table hits_partitioned(
       "UserID" bigint,
       "MobilePhoneModel" text
   )
   STORED AS PARQUET
   LOCATION 'hits_partitioned';

   https://github.com/apache/arrow-datafusion/blob/1a0542acbc01e5243471ae0fc3586c2f1f40013b/datafusion/sql/src/statement.rs#L636-L640

   ❤️React with ❤️1Reacted by Andrew Lamb
6. alamb commented on Jul 25, 2023

   

   alamb

   on Jul 25, 2023

   ContributorAuthor

   More actions

   I think that we can do this safely when we are certain that the binary data is valid UTF-8.

   I agree -- I think it would be good to cast the column into UTF8 (erroring if it contains non UTF8 data)

   For this particular query we could add an explicit cast like ::varchar perhaps like the following

   SELECT 
     "MobilePhoneModel"::varchar, 
     COUNT(DISTINCT "UserID") AS u 
   FROM hits_partitioned 
   WHERE "MobilePhoneModel" <> '' 
   GROUP BY "MobilePhoneModel" 
   ORDER BY u DESC LIMIT 10;
7. alamb

   assigned

   jonahgao

   on Jul 25, 2023
8. jonahgao commented on Jul 25, 2023

   

   jonahgao

   on Jul 25, 2023

   Member

   More actions

   For this particular query we could add an explicit cast like ::varchar perhaps like the following

   This must be the best way 👍 .
9. alamb

   closed this as completedin #7080on Jul 25, 2023