ParserException: Parser Error: syntax error at or near "="

[sayedMbo]

Hi,

I am getting an error after making an inference with the model after I am done with estimating model parameters.

These are steps taken

settings = SettingsCreator(
    link_type="dedupe_only",
    comparisons=[
        cl.NameComparison("title"),
        cl.NameComparison("manufacturer"),
        cl.LevenshteinAtThresholds("description", 1),
        cl.ExactMatch("price").configure(term_frequency_adjustments=True),
    ],
    blocking_rules_to_generate_predictions=[
    block_on("levenshtein(l.manufacturer, r.manufacturer) <= 2"),
        block_on("price"),


    ],
    retain_intermediate_calculation_columns=True,
)

linker = Linker(df_concat, settings, db_api=DuckDBAPI())

Estimation of probability_two_random_records_match

deterministic_rules = [
    "l.title = r.title and levenshtein(l.manufacturer, r.manufacturer) <= 2",
    block_on("price")
]

linker.training.estimate_probability_two_random_records_match(deterministic_rules, recall=0.7)

Estimation of u probabilities

linker.training.estimate_u_using_random_sampling(max_pairs=1e6)

Estimation of m probabilities

training_blocking_rule = block_on("manufacturer")
training_session_fname_sname = (
    linker.training.estimate_parameters_using_expectation_maximisation(training_blocking_rule)
)

training_blocking_rule = block_on("title")
training_session_dob = linker.training.estimate_parameters_using_expectation_maximisation(
    training_blocking_rule
)

output INFO:splink.internals.settings:
Your model is fully trained. All comparisons have at least one estimate for their m and u values

Dosent work from here

linker = Linker(df_concat, settings, db_api=DuckDBAPI())
df_predictions = linker.inference.predict(threshold_match_probability=0.2)
df_predictions.as_pandas_dataframe(limit=5)

---------------------------------------------------------------------------
ParserException                           Traceback (most recent call last)
/usr/local/lib/python3.10/dist-packages/splink/internals/database_api.py in _log_and_run_sql_execution(self, final_sql, templated_name, physical_name)
     62         try:
---> 63             return self._execute_sql_against_backend(final_sql)
     64         except Exception as e:

6 frames
ParserException: Parser Error: syntax error at or near "="

The above exception was the direct cause of the following exception:

SplinkException                           Traceback (most recent call last)
/usr/local/lib/python3.10/dist-packages/splink/internals/database_api.py in _log_and_run_sql_execution(self, final_sql, templated_name, physical_name)
     73                 pass
     74 
---> 75             raise SplinkException(
     76                 f"Error executing the following sql for table "
     77                 f"`{templated_name}`({physical_name}):\n{final_sql}"

SplinkException: Error executing the following sql for table `__splink__blocked_id_pairs`(__splink__blocked_id_pairs_01b64447a):
CREATE TABLE __splink__blocked_id_pairs_01b64447a AS
WITH __splink__df_concat_with_tf AS (
  SELECT
    *
  FROM __splink__df_concat_with_tf_73968a136
)
SELECT
  '0' AS match_key,
  l."unique_id" AS join_key_l,
  r."unique_id" AS join_key_r
FROM __splink__df_concat_with_tf AS l
INNER JOIN __splink__df_concat_with_tf AS r
  ON (
    LEVENSHTEIN(l.manufacturer, l.manufacturer) <= 2 = LEVENSHTEIN(r.manufacturer, r.manufacturer) <= 2
  )
WHERE
  l."unique_id" < r."unique_id"
UNION ALL
SELECT
  '1' AS match_key,
  l."unique_id" AS join_key_l,
  r."unique_id" AS join_key_r
FROM __splink__df_concat_with_tf AS l
INNER JOIN __splink__df_concat_with_tf AS r
  ON (
    l."price" = r."price"
  )
WHERE
  l."unique_id" < r."unique_id"
  AND NOT (
    COALESCE(
      (
        LEVENSHTEIN(l.manufacturer, l.manufacturer) <= 2 = LEVENSHTEIN(r.manufacturer, r.manufacturer) <= 2
      ),
      FALSE
    )
  )

Error was: Parser Error: syntax error at or near "="

[ADBond]

Your problem is this blocking rule:

block_on("levenshtein(l.manufacturer, r.manufacturer) <= 2")

The function block_on accepts a column (or column expression such as substr(name, 1, 5)), and will generate the blocking rule from that (so e.g. block_on("price") will get converted to the expression l.price = r.price). It will not accept a full condition such as you pass - you can see in the error message that this gets converted to the (ill-formed) blocking rule LEVENSHTEIN(l.manufacturer, l.manufacturer) <= 2 = LEVENSHTEIN(r.manufacturer, r.manufacturer) <= 2.

To pass in a custom rule such as yours you can just pass it in as a string:

...
blocking_rules_to_generate_predictions=[
    "levenshtein(l.manufacturer, r.manufacturer) <= 2",
     block_on("price"),
],
...

However we would generally advise against using blocking rules with functions such as levenshtein, as they are not performant. In order to find the rows of record pairs that satisfy this rule this expression will need to be evaluated for all possible pairs of records in your input data, which (unless your data is particularly small) will probably not be feasible in a reasonable amount of time. You can read more about it in the docs page on efficient blocking rules.

[sayedMbo]

Thank you! It works now