Support nullability checks on SQL Views

[fingeromer]

SafeQl recognizes fields as nullable when inner joining with a materialized view:

The View:

CREATE MATERIALIZED VIEW issues_base AS
    SELECT issue.id AS issue_id,
        issue.cve_code,
        image.name AS image_name,
        image.id AS image_id,
        jira_issue_key,
        array_agg(issue_instance.id) AS issue_instance_ids,
        client_id,
        issue.ignored,
        bool_or(
            CASE
                WHEN (is_native)
                    THEN state = '2'
                ELSE
                    last_executed_at IS NOT NULL
            END
        ) AS is_executed
  FROM issue,
      image,
      issue_instance,
      client_library_instance,
      live_k8s_image_builds
  WHERE image.id = issue.image_id
  AND image.id = live_k8s_image_builds.image_id
  AND issue_instance.issue_id = issue.id
  AND issue_instance.image_build_id = live_k8s_image_builds.id
  AND issue_instance.client_library_instance_id = client_library_instance.id
  AND issue.resolved IS NOT TRUE
  GROUP BY issue.id, image.id;

The query:

          SELECT issue_id AS "issueId",
              cve_code AS "cveCode",
              image_name AS "imageName",
              image_id AS "imageId",
              jira_issue_key AS "jiraIssueKey",
              issue_instance_ids AS "issueInstanceIds",
              ignored
          FROM issues_base
          WHERE client_id = ${clientId};

[Newbie012]

TL;DR - SafeQL (currently) doesn't support nullability checks for SQL views.

SafeQL's approach is "everything is nullable until proven otherwise" (for safety reasons). It infers whether a target result (e.g., selected column) is nullable or not, by first analyzing what it is. If it's a computation, it's not null for sure. If it's a table column, it's not null as long as that table column is NOT NULL as well, and so on.

Unlike querying columns from tables, PostgresSQL doesn't know that the column cve_code that was called from view issues_base is from table issue. SafeQL already traverses through the queries for more precise checks, but currently, it doesn't traverse over the views to determine nullability checks.

As a workaround for now, SafeQL is smart enough to know that a column is not nullable if you do one of the following options:

• add WHERE issue_id IS NOT NULL
• add a COALESCE with a fallback value.

[fingeromer]

Thank you!