Skip to content

Detect mismatched SQL set operator column types

Alex Pooley requested to merge 412768-extend-set-operator-select-mismatch-warning into master

What does this MR do and why?

This merge request adds a new query analyzer to GitLab. The analyzer detects mismatched column types in SQL set operators (UNION, INTERSECT, EXCEPT). This can help prevent SQL errors after database schema changes.

A mismatched query is when we mix SELECT * and explicit columns queries together such as:

SELECT id, ...
FROM namespaces
UNION
SELECT *
FROM namespaces

These queries can become complicated through indirect queries:

WITH cte AS (
  SELECT id, ...
  FROM namespaces
)
SELECT *
FROM cte
UNION
SELECT *
FROM namespaces

During system upgrades Rails may cache column names. When the database schema changes this can create a mismatch in the set operator columns because the SELECT with explicit columns will match the old schema, while the SELECT with * will use the new schema.

In this MR we name queries that select explicit columns as static, and a queries that select * as dynamic. When we find that the parts of the set operator select both static and dynamic we end in an error state.

Complexity is created by the use of CTEs, sub-queries, sub-selects, and more. As demonstrated in the CTE example above, the query needs to be traversed before query states can be determined.

This work will only run in dev and test environments.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #412768 (closed)

Edited by Alex Pooley

Merge request reports