Add support for MySQL procedures/functions

[Tiross]

Hello,

I just try SQLFluff and use it over my MySQL scheme and I was a little sad that it does not parse MySQL routines.

Example :

DELIMITER $$

CREATE OR REPLACE DEFINER = 'internal-user'@'localhost' PROCEDURE `GetStatus` ()
LANGUAGE SQL
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
  SELECT * FROM data.status WHERE active = 1;
END $$

DELIMITER ;

SQLFluff reports 2 PSR and 3 LXR.

L:   1 | P:   1 |  PRS | Found unparsable section: 'DELIMITER $$\n\nCREATE OR
                       | REPLACE DEFINER ...'
L:   1 | P:  11 |  LXR | Unable to lex characters: ''$$\n\nCREATE'...'
L:   3 | P:  44 |  LXR | Unable to lex characters: '"@'localhos"...'
L:   9 | P:   1 |  PRS | Found unparsable section: 'END $$\n\nDELIMITER'
L:   9 | P:   5 |  LXR | Unable to lex characters: ''$$\n\nDELIMI'...'
L:  11 | P:  11 | L003 | Indentation not consistent with line #1

Online Fluff

Is it possible to support routines parsing (and lint them) ?

PS: I also have some trouble with USE my_db.

[barrywhart]

SQLFluff does have support for BigQuery and Postgres functions, so it should be possible to support them for MySQL as well.

Can you please create a separate issue for USE my_db? It's easier to track and address issues if each one is a distinct bug or enhancement. Thanks!

[Tiross]

Yep, no prob.

Thanks

[ghost]

Hi, @barrywhart I tested with PostgreSQL functions but I got errors similar to those reported above. Please find below

CREATE OR REPLACE FUNCTION a() RETURNS VOID
AS 
$_$
    SELECT 1;
$_$
LANGUAGE SQL;

➜  ~ sqlfluff lint --dialect postgres t.sql
== [t.sql] FAIL
L:   1 | P:   1 |  PRS | Found unparsable section: 'CREATE OR REPLACE FUNCTION
                       | a() RETURNS V...'
L:   2 | P:   3 | L001 | Unnecessary trailing whitespace.
L:   3 | P:   1 |  LXR | Unable to lex characters: ''$_$\n SE'...'
L:   5 | P:   1 |  LXR | Unable to lex characters: ''$_$\nLANGUA'...'
L:   5 | P:   1 |  PRS | Found unparsable section: '$_$\nLANGUAGE SQL'

[barrywhart]

@db-drive: IIRC, the Postgres dialect does not currently support all the possible delimiters for defining functions. If you see the postgres_create_function.sql test file, it uses single quotes (') as the delimiter. Can you create a separate issue for the above, mentioning the need to support other deliiters?

[silverbullettruck2001]

@barrywhart Do you know if this issue will be addressed in the near future?

[barrywhart]

My guess would be no. The dialect-specific issues tend to be addressed by someone personally interested in that database. Snowflake and BigQuery have pretty good coverage. Postgres gets some attention, MySQL not so much. Interested in volunteering?

[silverbullettruck2001]

@barrywhart I would be interested! I read through the contribution guide lines, but I would appreciate it if you would be up for talking about some specifics once I get it running locally via Slack this week. Is that possible?

[barrywhart]

Sure! Feel free to chat in Slack. I would encourage just using the #help channel and tagging me if you like. There's a number of friendly, knowledgeable people involved with the project.

[barrywhart]

This could be a great first issue. Most dialect changes are fairly straightforward, with a bit of trial and error, maybe a question or two.

[jivanpal]

As of version 0.13.0, CREATE PROCEDURE is recognised, but variations are not; and CREATE FUNCTION is not recognised at all. Syntax documentation available here.

Examples of valid but unparsable code:

CREATE OR REPLACE
DEFINER=`alice`@`localhost`
PROCEDURE p()

SELECT 1;

CREATE OR REPLACE
DEFINER=`alice`@`localhost`
FUNCTION f()

RETURNS INT
DETERMINISTIC

RETURN 1;

[NoraJama]

There is same issue in snowflake, where $$ dilimited functions and procedures are not parsed. @jivanpal @barrywhart @tunetheweb

CREATE OR REPLACE PROCEDURE SQLFLUFF_TEST(
v_test_param INT
)
RETURNS TEXT
LANGUAGE SQL
EXECUTE AS OWNER
AS
$$
DECLARE
daily_incr int := 1;
BEGIN
SELECT * FROM TEST ;
END;

$$;

Everything between '$$' is not properly parsed, no errors detected. However , when remove dollar signs it is. Should I raise this as a new issue?

[tunetheweb]

@NoraJama that's a separate issue. The query parses (so doesn't error out like it does for the issue being discussed here) but it does treat everything between $$ as a comment.

This is intentional for now for a few reason:

• procedures are not always SQL (BigQuery supports JavaScript functions/procedures for example)
• control blocks (BEGIN...END, IF..THEN...ELSE) only have very basic support in SQLFluff as not really part of "SQL". See Easier ways to define nested grammar #3345 for more discussion on that.

So yes, procedure/function bodies are currently not linted. But getting them to parse (as it does for Snowflake, but not yet for MySQL) at least allows the surrounding SQL to be linted. So it's a first step.

[Xananax]

For anyone looking for the file linked earlier in this thread, it's https://github.com/sqlfluff/sqlfluff/blob/main/test/fixtures/dialects/postgres/create_function.sql now.

It demonstrates usage of $$, which seems to work in most cases, but doesn't for example in mine:

CREATE OR REPLACE FUNCTION public.some_func(
  date_end   TIMESTAMP(3) DEFAULT current_timestamp + interval '3' MONTH,
) RETURNS <stuff> $$

If I remove the + interval '3' month, it works fine.