[X] I searched the issues and found no similar issues.
What Happened
I am attempting to clean up a large MySQL application which uses triggers for compatibility with an older application. This obviously runs hard into https://github.com/sqlfluff/sqlfluff/issues/3599 but I noticed another oddity with SET statements in those triggers - here's a highly-reduced example:
CREATE TRIGGER tr_downloads_i_copy_new_fields BEFORE INSERT
ON downloads
FOR EACH ROW BEGIN
SET NEW.createdate = UNIX_TIMESTAMP(NEW.createdate_m);
END
A little code golfing suggests that the problem is using the SET NEW.<column> form since SQLFluff works fine the more common SET @variable form. As far as I know, that's the only way to do that in MySQL and it's what they use in the examples:
== [test.sql] FAIL
L: 1 | P: 1 | PRS | Line 1, Position 1: Found unparsable section: 'CREATE
| TRIGGER tr_downloadao_i_copy_new_...'
WARNING: Parsing errors found and dialect is set to 'mysql'. Have you configured your dialect correctly?
How to reproduce
sqlfluff lint
Dialect
mysql
Version
3.0.6
Configuration
[sqlfluff]
dialect = mysql
max_line_length = 120
exclude_rules = L013
Search before asking
What Happened
I am attempting to clean up a large MySQL application which uses triggers for compatibility with an older application. This obviously runs hard into https://github.com/sqlfluff/sqlfluff/issues/3599 but I noticed another oddity with
SET
statements in those triggers - here's a highly-reduced example:A little code golfing suggests that the problem is using the
SET NEW.<column>
form since SQLFluff works fine the more commonSET @variable
form. As far as I know, that's the only way to do that in MySQL and it's what they use in the examples:https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
Expected Behaviour
The body of the trigger would be formatted
Observed Behaviour
How to reproduce
sqlfluff lint
Dialect
mysql
Version
3.0.6
Configuration
[sqlfluff] dialect = mysql max_line_length = 120 exclude_rules = L013
[sqlfluff:indentation] tab_space_size = 4
[sqlfluff:rules:capitalisation.keywords] capitalisation_policy = upper [sqlfluff:rules:capitalisation.functions] extended_capitalisation_policy = upper [sqlfluff:rules:capitalisation.types] extended_capitalisation_policy = upper
Are you willing to work on and submit a PR to address the issue?
Code of Conduct