Skip to main content

MY.L9.VIEW_SELECT_STAR — `SELECT *` in a view definition

  • Category: idiom
  • Level: 9
  • Confidence: deterministic
  • Downtime class: online
  • Stability: stable
  • Suites: lint, audit
  • Applies to: MySQL 8.4 and newer

What it checks​

A * in the outer select list of a CREATE VIEW — bare or qualified.

Reported:

DB::statement(
    'CREATE VIEW confirmed_subscribers AS SELECT * FROM subscribers WHERE confirmed_at IS NOT NULL'
);

Not reported:

DB::statement(
    'CREATE VIEW confirmed_subscribers AS SELECT id, email, confirmed_at FROM subscribers WHERE confirmed_at IS NOT NULL'
);

The view does not grow, and nothing tells you​

A view's column list is decided when the view is created. Measured against MySQL 8.4.10:

CREATE TABLE t (a int, b int);
CREATE VIEW v AS SELECT * FROM t;

SELECT VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_NAME = 'v';
  ->  select `t`.`a` AS `a`,`t`.`b` AS `b` from `t`

Then ALTER TABLE subscribers ADD COLUMN plan — and it reads back exactly the same. The view still exposes the original columns. CREATE OR REPLACE VIEW cannot add them either; the view has to be dropped and recreated.

That delay is the whole reason this is a rule rather than a style note. The migration that adds the column is green. The deploy is green. The report that quietly lacks the new column is noticed weeks later, by somebody who does not know a view is involved.

Four stars, one problem​

* means four different things in SQL, and only one of them is this trap. The rule scans the outer select list at parenthesis depth zero, which separates them without a special case for each:

shapeexamplereported
select-list starSELECT * FROM tyes
qualified starSELECT u.*, o.total FROM …yes
aggregate starSELECT count(*) FROM tno — inside the function's parentheses
multiplicationSELECT price * quantity FROM tno — an operand stands before it

A subquery's star is a fifth and is also silent: WHERE EXISTS (SELECT * FROM orders) never becomes part of the view's column list, so reporting it would be a false positive on a correct view. A star inside a string literal is silent for the same reason — it is not a column reference.

The two suites answer different questions, and only one of them can​

  • lint reads the migration, where the star is still written down. This is the real check.
  • audit reads the catalog, where the star is gone — MySQL expanded it on the way in, and the original text was never stored.

So the audit half reports undetermined for every view, with the reason server_expanded_definition, and points at the suite that can answer.

That is deliberate rather than a gap. A rule that simply left the audit suite would let a run over a schema full of views report nothing about them at all, and a reader would take the silence for a clean result. A check that cannot run says so.

What the rule cannot see​

A view whose DDL is assembled at runtime rather than written in the migration. The rule reads the statement the migration emitted, so anything built after that point is outside its reach.

Suppressing it​

Level 9 is the strictest band and is not reached unless a project asks for it. Within it, the ordinary routes apply: a baseline entry, an ignore rule in config/sqlens.php, or lowering the level below 9. A view that deliberately mirrors its base table — a rename shim during an expand/contract migration, meant to be dropped — is the case worth a baseline entry rather than a rewrite.

Sources​

Not transferable to MariaDB. Its view handling and its information_schema shapes are its own, and this package targets MySQL 8.4 semantics — advice derived from them and applied elsewhere would be confident, specific and about another product.