tsql: delete/insert/update tbl output deleted.id unparseable when wrapped

[ReinierKop]

Search before asking

• I searched the issues and found no similar issues.

What Happened

I ran sqlfluff parse output.sql --dialect tsql on a file containing valid tsql with output segment logic and it did not parse.

Expected Behaviour

I expect the segments to parse using the tsql dialect

Observed Behaviour

The segments are not correctly parsed by sqlfluff

How to reproduce

The following pieces of tsql code are valid but unparseable.

Delete example:

insert into tbl1
select id from (
    delete tbl2
    output deleted.id
) as x

Update example:

insert into tbl1
select id from (
    update tbl2 
    set id=2
    output inserted.id
) as x

Insert example:

insert into tbl1
select id from (
    insert into tbl2 
    output inserted.id
    select 1 as id
) as x

After a bit of digging, I suspect the issue lies with this piece of code which distinguishes selects from inserts/updates/deletes. For tsql, that logic breaks down whenever you add an output segment to these i/u/d segments, since adding that optional segment effectively transforms the entire segment into something "selectable" in tsql.

I'm happy to work on a potential fix but hard-pressed for ideas on how to proceed from here; I suspect it may require some more advanced logic to get this to work that I'm not familiar with yet.

Dialect

tsql

Version

sqlfluff v1.0.0
python v3.10.5

Configuration

default config

Are you willing to work on and submit a PR to address the issue?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct