spider2-dbt: duckdb_match with ignore_order=true accepts row-scrambled tables (cross-column alignment never checked)

[teilomillet]

Summary

In evaluation_suite/eval_utils.py, compare_pandas_table → vectors_match sorts the two vectors independently when ignore_order is set (L116-117), and gold columns are matched one at a time against any column of the predicted table (L141-142). The combined effect: the scorer verifies that each required column contains the right multiset of values, but never verifies that values line up into the right rows.

A submission with every column correct but the rows scrambled — i.e., every row-level fact wrong — scores 1.

Reproduction

Run from spider2-dbt/evaluation_suite/ (the stubs neutralize unrelated cloud imports; only duckdb and pandas are needed):

import duckdb, tempfile, os, types, sys, importlib
for m in ['google','google.cloud','google.cloud.bigquery','google.oauth2',
          'snowflake','snowflake.connector','psycopg2','mysql','mysql.connector']:
    sys.modules.setdefault(m, types.ModuleType(m))
sys.path.insert(0, '.')
eval_utils = importlib.import_module('eval_utils')

d = tempfile.mkdtemp()
gold_db, pred_db = os.path.join(d,'gold.duckdb'), os.path.join(d,'pred.duckdb')

con = duckdb.connect(gold_db)
con.execute("CREATE TABLE report AS SELECT * FROM (VALUES ('alice', 100.0), ('bob', 250.0), ('carol', 375.0)) t(customer, revenue)")
con.close()
# same values, every pairing wrong
con = duckdb.connect(pred_db)
con.execute("CREATE TABLE report AS SELECT * FROM (VALUES ('alice', 375.0), ('bob', 100.0), ('carol', 250.0)) t(customer, revenue)")
con.close()

print(eval_utils.duckdb_match(pred_db, gold_db, condition_tabs=['report'], condition_cols=[[0,1]], ignore_orders=[True]))   # -> 1
print(eval_utils.duckdb_match(pred_db, gold_db, condition_tabs=['report'], condition_cols=[[0,1]], ignore_orders=[False]))  # -> 0

Scope

All 119 table comparisons across all 68 instances in evaluation_suite/gold/spider2_eval.jsonl set ignore_order: true, so the strict path is never exercised in the dbt suite. The lax path is the only path.

Related: the inner for j, pred ... break loop (asked about in #71) also never sets aside matched predicted columns, so a single predicted column can satisfy several gold columns.

Suggestion

With ignore_order=true, sort row tuples (restricted to condition_cols) rather than sorting each column independently — this keeps order-insensitivity while preserving row structure. If the current semantics are intentional, documenting them would help; agents can currently pass with tables whose row-level facts are wrong.

Found while reproducing the evaluation for a training project; fuller notes: https://teilo.xyz/posts/quaero-spider2-negative-result/#2-reproducing-the-spider-20-dbt-evaluation