dolthub / dolthub/dolt

Support simple dynamic expressions on DOLT_DIFF

Open
#11,187 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug customer issue diff enhancement known workaround sql system tables
Dominant language
Go
Stars
24.5k
Forks
873
Avg merge
1d 8h
Merged PRs (30d)
120

Description

I would like to look at the commit history of two branches, and then see if the same airspace (table row) was modified in one of the unique commits on branch X and one of the unique commits on branch Y. The idea is to see if someone has been working on the same entity on these two branches. I wrote the following query for this:

SELECT id, entity_type

FROM (
    -- Modified on Y directly.
    SELECT DISTINCT(COALESCE(to_id, from_id)) AS id, 'airspace' AS entity_type, 'y' AS change_origin

    FROM DOLT_DIFF('INT...branch-x', 'airspaces')

    UNION

    -- Modified on X directly.
    SELECT DISTINCT(COALESCE(to_id, from_id)) AS id, 'airspace' AS entity_type, 'x' AS change_origin

    FROM DOLT_DIFF('branch-x...INT', 'airspaces')
) modified_entity

GROUP BY id, entity_type

HAVING COUNT(change_origin) > 1

This query succeeds as expected. I would now like to expand this query to run for a dynamic set of branches on just one side: branch-x will always be fixed, but I would like to query the dolt_branches table and select a few branches where I can then do this same comparison with respect to branch-x.

In SQL terms this could look like this:

SELECT
    branch.name,
    (
        SELECT COUNT(CONCAT(id, entity_type))
        
        FROM (
            -- Modified on Y directly.
            SELECT DISTINCT(COALESCE(to_id, from_id)) AS id, 'airspace' AS entity_type, 'y' AS change_origin
        
            FROM DOLT_DIFF(CONCAT(branch.name, '...branch-x'), 'airblocks')
        
            UNION
        
            -- Modified on X directly.
            SELECT DISTINCT(COALESCE(to_id, from_id)) AS id, 'airspace' AS entity_type, 'x' AS change_origin
        
            FROM DOLT_DIFF(CONCAT('branch-x...', branch.name), 'airblocks')
        ) modified_entity
        
        GROUP BY id, entity_type
        
        HAVING COUNT(change_origin) > 1
    ) AS warning_count

FROM dolt_branches branch

WHERE branch.name != 'branch-x'

However, this yields the following error:

Invalid argument to dolt_diff: concat(branch.name,'...branch-x') – only literal values supported 

I need a three-dot diff in this case, but using the simplified syntax DOLT_DIFF(branch.name, 'branch-x', 'airspaces') for the two-dot diff yields another error:

unable to find field with index 1 in row of 0 columns. This is a bug. Please file an issue here: https://github.com/dolthub/dolt/issues 

Is this something that can be supported, even if in a simple form? I've explored some other avenues:

  • Scan the dolt_diff_* tables instead, but those depend on the active checked out branch and won't let me easily identify common commits.
  • Fetch branches up front, loop over them, and perform multiple queries, each with fixed values. This will work, but I would later on like to add a filter on branches WHERE warning_count > 0 and paginate with LIMIT, which becomes somewhat painful as I will just need to keep querying until I meet the expected page sizes (as I don't know which branches will be filtered out or not) and providing a total estimate of all results becomes difficult.

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the DOLT_DIFF calls with literal arguments, CONCAT(branch.name, '...branch-x'), and the simplified two-dot form against dolt_branches. Compare these results with the dolt_diff_* tables; done means a supported simple dynamic expression can run the requested three-dot comparison without the reported argument or row errors.

Written by the indexing model from the issue text.

Assessment

Tech stack
git, sql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.