Error with dolt_commit_diff subquery

[lizkimble]

update rate set contracting_method = 'per diem' where description collate utf8mb4_general_ci like "%diem%" and hospital_id in (select to_id from dolt_commit_diff_hospital where from_commit = "2obf8kj989gt8kafn850licnt3p1s530" and to_commit = "lakafu1o577q2a3rksjn1rhv2dfnmguu")

gives the error

error querying table dolt_commit_diff_hospital: dolt_commit_diff_* tables must be filtered to a single 'to_commit'

Update
 └─ Update Join
     └─ UpdateSource(SET rate.contracting_method = 'per diem')
         └─ Project
             ├─ columns: [rate.hospital_id, rate.row_id, rate.line_type, rate.description, rate.rev_code, rate.local_code, rate.code, rate.ms_drg, rate.apr_drg, rate.eapg, rate.hcpcs_cpt, rate.modifiers, rate.alt_hcpcs_cpt, rate.thru, rate.apc, rate.icd, rate.ndc, rate.drug_hcpcs_multiplier, rate.drug_quantity, rate.drug_unit_of_measurement, rate.drug_type_of_measurement, rate.billing_class, rate.setting, rate.rate_category, rate.payer_name, rate.plan_name, rate.standard_charge, rate.standard_charge_percent, rate.contracting_method, rate.additional_generic_notes, rate.additional_payer_specific_notes]
             └─ LookupJoin
                 ├─ (rate.hospital_id = applySubq0.to_id)
                 ├─ Distinct
                 │   └─ Project
                 │       ├─ columns: [applySubq0.to_id]
                 │       └─ Filter
                 │           ├─ ((applySubq0.from_commit = '2obf8kj989gt8kafn850licnt3p1s530') AND (applySubq0.to_commit = 'lakafu1o577q2a3rksjn1rhv2dfnmguu'))
                 │           └─ TableAlias(applySubq0)
                 │               └─ Table
                 │                   └─ name: dolt_commit_diff_hospital
                 └─ Filter
                     ├─ rate.description COLLATE utf8mb4_general_ci LIKE '%diem%'
                     └─ IndexedTableAccess(rate)
                         └─ index: [rate.hospital_id,rate.row_id]

[fulghum]

Thanks for reporting this one! The dolt_commit_diff_<tablename> system tables rely on the from_commit and to_commit filters to be "pushed down" into the table, but for some reason that isn't happening here, so we get the error message you shared. My first guess is that we can't push them down across the TableAlias node that separates the Table node from the Filter node. The fix might be to let the analyzer jump over the TableAlias node and push those filters to that nested Table node, but @max-hoffman or @jcor11599 may have better ideas.

[max-hoffman]

Thank you for the detailed report with the EXPLAIN plan @lizkimble! I agree with Jason that our analyzer is failing to convert the regular table scan of the diff table into an indexed table scan, which is the responsibility of the generateIndexScans rule. I am blanking on how to give you a short-term workaround for UPDATE JOIN. For a SELECT we would normally recommend a join hint to side-step this error, but UPDATE don't support hinting. We might bump this in priority because we don't have a good workaround right now. If you can wait until next week we should be able to fix in a day or two.

[lizkimble]

since the select results are fairly small, I've just been copy/pasting the results into the update query

thus, don't prioritize this for my needs

[jycor]

Hey @lizkimble, I've been trying to repro this issue without much luck.
What version of dolt are you on?
$ dolt version

[timsehn]

Gonna resolve as user was on 0.75.6.