SQL Inspection false negatives with STRAIGHT_JOIN and Temporal Tables

Both MySQL nor MariaDB SQL dialects seem to have some odd quirks when you use STRAIGHT_JOIN in the JOIN part of a query.

The syntax of the STRAIGHT_JOIN is claimed to be exactly the same as JOIN, it effects the query optimization.

Mysql docs on STRAIGHT_JOIN

 

Example 1:

It incorrectly triggers a "Constant Expression" Warning based on a left joined column:

SELECT
    TABLES.*,
    IF(TABLE_PRIVILEGES.GRANTEE is null, 0, 1) has_grantee
FROM information_schema.TABLES
STRAIGHT_JOIN information_schema.COLUMNS USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
LEFT JOIN information_schema.TABLE_PRIVILEGES USING ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
LIMIT 10;

The inspections for this query returns a warning for line 3:

Condition 'TABLE_PRIVILEGES.GRANTEE is null' is always 'false'

That table is left joined, and may or may not be null.

 

Example 2:

If you use USING related to a STRAIGHT_JOIN in the JOIN, it fails to process, telling you that USING is not expected:

SELECT
    TABLES.*
FROM information_schema.TABLES
STRAIGHT_JOIN information_schema.COLUMNS USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
LIMIT 10;

The inspection for this query returns an error for line 4:

',', <index hint option>, AS, CROSS, EXCEPT, FOR, GROUP, HAVING, INNER, INTERSECT, INTO, JOIN, LEFT, LIMIT, LOCK, NATURAL, ON, ORDER, PARTITION, PROCEDURE, RIGHT, STRAIGHT_JOIN, UNION, WHERE or WINDOW expected, got 'USING'

 

Example 3:

Create a temporary table in MariaDB:

CREATE TABLE t (
    x INT
) WITH SYSTEM VERSIONING;

If you use any of the temporal table keywords in the FROM clause the query fails to parse:

SELECT *
FROM t FOR SYSTEM_TIME AS OF '2022-01-01 00:00:00'
JOIN users ON ( t.x = users.id );

SELECT *
FROM t FOR SYSTEM_TIME ALL
JOIN users ON ( t.x = users.id );

The SQL Inspection shows an error for line 3 in both queries:

EXCEPT, FOR, GROUP, HAVING, INTERSECT, INTO, LIMIT, LOCK, ORDER, PROCEDURE, UNION, WHERE or WINDOW expected, got 'JOIN'

MariaDB docs on TEMPORAL TABLES: System Versioned Tables

All of these example queries properly execute on my MariaDB server running 10.5+.

 

I have confirmed that my SQL Dialect is on Mariadb. I checked MySQL for the first 2 examples (but the 3rd is MariaDB syntax).

Is there anything I can do or check to assist in figuring this out? Thanks!

 

1 comment
Comment actions Permalink

It incorrectly triggers a "Constant Expression" Warning based on a left joined column

Feel free to follow DBE-15076.

If you use USING related to a STRAIGHT_JOIN in the JOIN, it fails to process, telling you that USING is not expected

I've filled DBE-15527.

If you use any of the temporal table keywords in the FROM clause the query fails to parse

You can also follow DBE-15529 for updates.

Thanks for sharing these problems. Much appreciated.

0

Please sign in to leave a comment.