mySql - JSON_VALUE and CHARACTER SET utf8mb4 results in parsing error

[kzajaczkowski]

Search before asking

• I searched the issues and found no similar issues.
• There's one similar issue, but about CREATE DATABASE, not JSON_VALUE: mysql sql('CREATE DATABASE IF NOT EXISTS xxx CHARACTER SET "utf8mb4" COLLATE "utf8mb4_bin" COMMENT "xxx";') but parse,lint can't understand right and give error thart Found unparsable section: 'CHARACTER SET #5754

What Happened

Please have a look at the following code:

SELECT
    JSON_VALUE('{"a":"b"}', '$.a' RETURNING CHAR(255) CHARACTER SET utf8mb4) AS JSON_VALUE_CHARSET_EXAMPLE_UNPARSABLE,
    NULL AS JSON_VALUE_UNSIGNED_EXAMPLE_UNPARSABLE,
    NULL AS JSON_VALUE_SIGNED_EXAMPLE_PARSABLE
UNION ALL
SELECT
    NULL AS JSON_VALUE_CHARSET_EXAMPLE_UNPARSABLE,
    JSON_VALUE('{"a":"1"}', '$.a' RETURNING UNSIGNED) AS JSON_VALUE_EXAMPLE_UNPARSABLE,
    NULL AS JSON_VALUE_SIGNED_EXAMPLE_PARSABLE
UNION ALL
SELECT
    NULL AS JSON_VALUE_CHARSET_EXAMPLE_UNPARSABLE,
    NULL AS JSON_VALUE_EXAMPLE_UNPARSABLE,
    JSON_VALUE('{"a":"1"}', '$.a' RETURNING SIGNED) AS JSON_VALUE_SIGNED_EXAMPLE_PARSABLE

Expected Behaviour

There should be no PRS type linting errors as this is a valid mysql code.

Observed Behaviour

Both CHARACTER SET and UNSIGNED give "unparsable section" error.

=== [dbt templater] Sorting Nodes...
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
== [models/....sql] FAIL                                                                                                                                                                               
L:   2 | P:  55 |  PRS | Line 2, Position 55: Found unparsable section:
                       | 'CHARACTER SET utf8mb4'
L:   8 | P:  45 |  PRS | Line 8, Position 45: Found unparsable section:
                       | 'UNSIGNED'

How to reproduce

Lint the following code with dialect set to mysql:

SELECT
    JSON_VALUE('{"a":"b"}', '$.a' RETURNING CHAR(255) CHARACTER SET utf8mb4) AS JSON_VALUE_CHARSET_EXAMPLE_UNPARSABLE,
    NULL AS JSON_VALUE_UNSIGNED_EXAMPLE_UNPARSABLE,
    NULL AS JSON_VALUE_SIGNED_EXAMPLE_PARSABLE
UNION ALL
SELECT
    NULL AS JSON_VALUE_CHARSET_EXAMPLE_UNPARSABLE,
    JSON_VALUE('{"a":"1"}', '$.a' RETURNING UNSIGNED) AS JSON_VALUE_EXAMPLE_UNPARSABLE,
    NULL AS JSON_VALUE_SIGNED_EXAMPLE_PARSABLE
UNION ALL
SELECT
    NULL AS JSON_VALUE_CHARSET_EXAMPLE_UNPARSABLE,
    NULL AS JSON_VALUE_EXAMPLE_UNPARSABLE,
    JSON_VALUE('{"a":"1"}', '$.a' RETURNING SIGNED) AS JSON_VALUE_SIGNED_EXAMPLE_PARSABLE

Dialect

mysql

Version

Configuration

Custom configuration not needed for this to occur

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

[kzajaczkowski]

I am happy to submit a PR, but currently am struggling in identifying what needs to be fixed as I am not familiar with the project. So any help much appreciated.