Query failing to return any results when filter is an equality check on strings (bad statistics in parquet)

[maxburke]

With the attached file, running the query:

CREATE EXTERNAL TABLE t STORED AS PARQUET LOCATION 'test.parquet';
SELECT "adt" FROM t  WHERE "direction" = 'Two Way';

returns 0 rows, but:

SELECT "adt" FROM t  WHERE "direction" LIKE 'Two Way';

returns the expected 101 rows.

I noticed that if I disable the FilterPushDown optimization pass in Datafusion I also get the expected result.

test.zip

[Dandandan]

This seems to be something in the pruning logic:

❯ explain analyze SELECT "adt" FROM t  WHERE "direction" = 'Two Way';
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type         | plan                                                                                                                                                                                                                       |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan with Metrics | CoalescePartitionsExec, metrics=[output_rows=0, elapsed_compute=32.249µs]                                                                                                                                                  |
|                   |   ProjectionExec: expr=[adt@1 as adt], metrics=[output_rows=0, elapsed_compute=NOT RECORDED]                                                                                                                               |
|                   |     CoalesceBatchesExec: target_batch_size=4096, metrics=[output_rows=0, elapsed_compute=26.051µs]                                                                                                                         |
|                   |       FilterExec: direction@0 = Two Way, metrics=[output_rows=0, elapsed_compute=NOT RECORDED]                                                                                                                             |
|                   |         RepartitionExec: partitioning=RoundRobinBatch(16), metrics=[repart_time{inputPartition=0}=NOT RECORDED, send_time{inputPartition=0}=NOT RECORDED, fetch_time{inputPartition=0}=509.572µs]                          |
|                   |           ParquetExec: batch_size=8192, limit=None, partitions=[test.parquet], metrics=[row_groups_pruned{filename=test.parquet}=1, num_predicate_creation_errors=0, predicate_evaluation_errors{filename=test.parquet}=0] |
|                   |                                                                                                                                                                                                                            |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set. Query took 0.002 seconds.

Already in ParquetExec there is no output (RepartitionExec doesn't hae to repartition any input row).

[alamb]

This sounds similar to something we hit in IOx (https://github.com/influxdata/influxdb_iox/issues/2153) which I ultimately tracked down to a bug in the parquet statistics generation: apache/arrow-rs#641

So in this case, the statistics embedded in the parquet file for the direction column are T:[min: Merged, max: Outgoing, num_nulls not defined], namely that the minimum value is "Merged" and the maximum value is "Outgoing" which I do not think is correct

$ parquet-tools meta test.parquet 
file:        file:/Users/alamb/Downloads/test.parquet 
creator:     UrbanLogiq 
extra:       ARROW:schema = /////+gAAAAQAAAAAAAKAA4ADAALAAQACgAAABQAAAAAAAABBAAKAAwAAAAIAAQACgAAAAgAAAAIAAAAAAAAAAMAAAB8AAAAPAAAAAQAAACg////GAAAACAAAAAAAAACHAAAAAgADAAEAAsACAAAACAAAAAAAAABAAAAAAMAAABhZHQA1P///xQAAAAMAAAAAAAABQwAAAAAAAAAxP///wkAAABkaXJlY3Rpb24AAAAQABQAEAAAAA8ABAAAAAgAEAAAABgAAAAMAAAAAAAABRAAAAAAAAAABAAEAAQAAAAKAAAAdWxfbm9kZV9pZAAA 

file schema: arrow_schema 
--------------------------------------------------------------------------------
ul_node_id:  REQUIRED BINARY L:STRING R:0 D:0
direction:   REQUIRED BINARY L:STRING R:0 D:0
adt:         REQUIRED INT32 R:0 D:0

row group 1: RC:301 TS:3384 OFFSET:4 
--------------------------------------------------------------------------------
ul_node_id:   BINARY ZSTD DO:4 FPO:1796 SZ:2143/3187/1.49 VC:301 ENC:RLE_DICTIONARY,PLAIN,RLE ST:[min: /ehIvdei+UGfkQ4Gy5fr1w==, max: zThqpswvY6fa3VHF4BKWfw==, num_nulls not defined]
direction:    BINARY ZSTD DO:2243 FPO:2311 SZ:195/177/0.91 VC:301 ENC:RLE_DICTIONARY,PLAIN,RLE ST:[min: Merged, max: Outgoing, num_nulls not defined]
adt:          INT32 ZSTD DO:2500 FPO:3159 SZ:1046/1503/1.44 VC:301 ENC:RLE_DICTIONARY,PLAIN,RLE ST:[min: 15, max: 23116, num_nulls not defined]

Which appears to be incorrect for the data in test.parquet:

❯ select distinct direction from t order by direction;
+-----------+
| direction |
+-----------+
| Incoming  |
| Merged    |
| Outgoing  |
| Two Way   |
+-----------+

[alamb]

If you can't rewrite your parquet files, we could potentially add a "don't do parquet pruning" flag to ExecContext

[maxburke]

That's really interesting; thanks for finding that @alamb !

We aren't doing anything special with our Parquet generation; we basically write them straight from Arrow (via the Rust libraries), so perhaps there's an underlying bug in the Rust Parquet statistics generation code that we can start investigating.

Being able to skip parquet pruning would be useful as well.

From a curiosity point of view, what should the min/max values be for string columns?

[alamb]

@maxburke -- I fixed at least one bug in the rust parquet statistics generation ( PR was apache/arrow-rs#643, which was first released in 5.2.0 it appears according to the changelog.

There may still be other bugs and if so I would love to help fix them

From a curiosity point of view, what should the min/max values be for string columns?

In this case, I would expect the min value to be "Incoming" and the max value to be "Two Way" (namely the min and max values if the data was sorted via unicode codepoint)

[alamb]

I think this one is resolved. Closing for now