Ambiguous reference to column name

[dshopin]

I am trying to estimate a number of comparisons using the following blocking rule:

"ABS(DATE_DIFF('hour', l.visit_date_time_utc, r.visit_date_time_utc)) <= 5"

but getting an error

RuntimeError: Binder Error: Ambiguous reference to column name "visit_date_time_utc" (use: "r.visit_date_time_utc" or "l.visit_date_time_utc")

Should I define rules that utilize some function differently?

[dshopin]

Here's a minimal example:

import pandas as pd
from splink.duckdb.duckdb_linker import DuckDBLinker
import splink.duckdb.duckdb_comparison_library as cl
import logging



df = pd.DataFrame({'visit_date_time_utc':['2022-07-30 18:05:52', '2022-07-30 15:31:43', '2022-07-30 16:59:20', '2022-07-30 16:26:42', '2022-07-30 16:47:45', '2022-07-30 18:05:52'],
                   'user_agent':['Mozilla/5.0 (Windows NT 10.0; Win64; x64)', 'Mozilla/5.0 (X11; Ubuntu; Linux x86_64)']*3,
                   'unique_id':[1,2,3,4,5,6]})
df.visit_date_time_utc = pd.to_datetime(df.visit_date_time_utc)



settings = {
    "probability_two_random_records_match": 1/1000,
    "link_type": "dedupe_only",
    "comparisons": [
        cl.levenshtein_at_thresholds("user_agent")
    ],
    "blocking_rules_to_generate_predictions": [
        "ABS(DATE_DIFF('hour', l.visit_date_time_utc, r.visit_date_time_utc)) <= 5"
    ],
    "retain_matching_columns": True,
    "retain_intermediate_calculation_columns": True,
    "additional_columns_to_retain": ['unique_id', 'visit_date_time_utc'],
}


linker = DuckDBLinker(df, settings, set_up_basic_logging=False)
logging.getLogger("splink").setLevel(1)

print(linker.count_num_comparisons_from_blocking_rule("l.visit_date_time_utc <> r.visit_date_time_utc")) #works fine

print(linker.count_num_comparisons_from_blocking_rule("ABS(DATE_DIFF('hour', l.visit_date_time_utc, r.visit_date_time_utc)) <= 5")) #gives error

and here is the output:

SQL pipeline was passed inputs [] and output dataset __splink__analyse_blocking_rule
    Pipeline part 1: Task reads tables [ _a ] and has output table name: __splink__df_concat
    Pipeline part 2: Task reads tables [ __splink__df_concat ] and has output table name: __splink__analyse_blocking_rule
Executing sql to create __splink__analyse_blocking_rule as physical name __splink__analyse_blocking_rule_da1021a

------Start SQL---------
WITH __splink__df_concat AS (
  SELECT
    visit_date_time_utc,
    user_agent,
    unique_id
  FROM _a
)
SELECT
  COUNT(*) AS count_of_pairwise_comparisons_generated
FROM __splink__df_concat AS l
INNER JOIN __splink__df_concat AS r
  ON l.visit_date_time_utc <> r.visit_date_time_utc
WHERE
  l.unique_id < r.unique_id
-------End SQL-----------

SQL pipeline was passed inputs [] and output dataset __splink__analyse_blocking_rule
    Pipeline part 1: Task reads tables [ _a ] and has output table name: __splink__df_concat
    Pipeline part 2: Task reads tables [ __splink__df_concat ] and has output table name: __splink__analyse_blocking_rule
Executing sql to create __splink__analyse_blocking_rule as physical name __splink__analyse_blocking_rule_5e7fbfe

------Start SQL---------
WITH __splink__df_concat AS (
  SELECT
    visit_date_time_utc,
    user_agent,
    unique_id
  FROM _a
)
SELECT
  COUNT(*) AS count_of_pairwise_comparisons_generated
FROM __splink__df_concat AS l
INNER JOIN __splink__df_concat AS r
  ON ABS(DATE_DIFF(visit_date_time_utc, l.visit_date_time_utc, 'hour')) <= 5
WHERE
  l.unique_id < r.unique_id
-------End SQL-----------

14
---------------------------------------------------------------------------
RuntimeError                              Traceback (most recent call last)
/tmp/ipykernel_58536/363200606.py in <module>
     33 print(linker.count_num_comparisons_from_blocking_rule("l.visit_date_time_utc <> r.visit_date_time_utc")) #works fine
     34 
---> 35 print(linker.count_num_comparisons_from_blocking_rule("ABS(DATE_DIFF('hour', l.visit_date_time_utc, r.visit_date_time_utc)) <= 5")) #gives error
     36 

~/.local/lib/python3.7/site-packages/splink/linker.py in count_num_comparisons_from_blocking_rule(self, blocking_rule, link_type, unique_id_column_name)
   1680         )
   1681         self._enqueue_sql(sql, "__splink__analyse_blocking_rule")
-> 1682         res = self._execute_sql_pipeline().as_record_dict()[0]
   1683         return res["count_of_pairwise_comparisons_generated"]
   1684 

~/.local/lib/python3.7/site-packages/splink/linker.py in _execute_sql_pipeline(self, input_dataframes, materialise_as_hash, use_cache, transpile)
    346                 materialise_as_hash,
    347                 use_cache,
--> 348                 transpile,
    349             )
    350             self._pipeline.reset()

~/.local/lib/python3.7/site-packages/splink/linker.py in _sql_to_splink_dataframe_checking_cache(self, sql, output_tablename_templated, materialise_as_hash, use_cache, transpile)
    420         if materialise_as_hash:
    421             splink_dataframe = self._execute_sql_against_backend(
--> 422                 sql, output_tablename_templated, table_name_hash, transpile=transpile
    423             )
    424         else:

~/.local/lib/python3.7/site-packages/splink/duckdb/duckdb_linker.py in _execute_sql_against_backend(self, sql, templated_name, physical_name, transpile)
    240         ({sql})
    241         """
--> 242         self._con.execute(sql).fetch_df()
    243 
    244         return DuckDBLinkerDataFrame(templated_name, physical_name, self)

RuntimeError: Binder Error: Ambiguous reference to column name "visit_date_time_utc" (use: "r.visit_date_time_utc" or "l.visit_date_time_utc")

Looks like SQL parser cuts prefix r. and changes the order of arguments in DATE_DIFF()?

[RobinL]

Super, thanks that's really helpful. We'll take a look at this properly in the next few days. In the meantime it's possible this is a sqlglot bug, you could try upgrading to latest and seeing if it helps

[RobinL]

Solution

There seems to be a problem with how the date_diff function is transpiled in sqlglot.

You can work around it using the following blocking rule:

on abs(cast(l.visit_date_time_utc as date) - cast(r.visit_date_time_utc as date)) <= 5 * 24

I'd be grateful if you'd let me know if this doesn't work

Other notes

I should also note that these kinds of 'within a range' blocking rules execute much less efficiently than anything with an equality condition.

To evaluate this blocking rules, Splink will have to generate ALL possible comparisons from the input records i.e. the cartesian product, and then evaluate this inequality condition. So if you have large input datasets, this will be extremely slow.

One possible way of avoiding this may be to round your dates, and use an equality condition on the rounded dates (using more than one blocking rule).

The equivalent for integers would be something like the following two blocking rules.

[
"ceil(l.value/5) = ceil(r.value/5)",
"ceil((l.value+5)/5) = ceil(r.value/5)"
]

You need both so that e.g. the values 9 and 11 match, and also 11 and 13

[dshopin]

Thank you, will test the workaround today.

[dshopin]

he equivalent for integers would be something like the following two blocking rules.

[
"ceil(l.value/5) = ceil(r.value/5)",
"ceil((l.value+5)/5) = ceil(r.value/5)"
]
You need both so that e.g. the values 9 and 11 match, and also 11 and 13

I'm not sure I understand that. Won't the second rule also match 6 and 14 (and we don't want that) ?

[RobinL]

Correct. But in most scenarios you'd want your model (I e. your trained match weights), as opposed to the blocking rules, to distinguish between matches and non matches.

So in most scenarios it's fine for a blocking rule to generate a few additional comparisons because these should be scored as non matches by the model anyway

If you really need your condition then you could add it as an and condition. I.e. the blocking rule would have the 'ceil' based equality conditions AND the range based condition. That will execute much faster than just the range based condition alone

[dshopin]

I successfully used another workaround instead of DATE_DIFF. Created two additional columns in a dataset:
visit_date_time_utc_from = visit_date_time_utc - 5 hours and visit_date_time_utc_to = visit_date_time_utc + 5 hours and then used blocking rule l.visit_date_time_utc BETWEEN r.visit_date_time_utc_from AND r.visit_date_time_utc_to