1993
MariaDBERRORNotableQueryHIGH confidence
Reference to field not allowed in this context
Production Risk
Low — query is rejected; no data is modified.
What this means
A column reference was used in a position where it is not allowed — for example, referencing a SELECT list alias in a WHERE clause, or referencing an outer query column incorrectly in a subquery context.
Why it happens
- 1Using a SELECT alias in a WHERE clause (aliases are not available there in standard SQL).
- 2Referencing an aggregate alias outside an ORDER BY or HAVING clause.
- 3Illegal forward reference in GROUP BY or ORDER BY.
How to reproduce
trigger — this will error
trigger — this will error
SELECT price * 0.9 AS discounted FROM products WHERE discounted < 100;
expected output
ERROR 1993 (HY000): Illegal reference to field 'discounted'.
Fix 1
Repeat the expression in WHERE or use a subquery
Repeat the expression in WHERE or use a subquery
SELECT * FROM (SELECT price * 0.9 AS discounted FROM products) sub WHERE discounted < 100;
Why this works
Wrapping in a derived table makes the alias available to outer WHERE.
Fix 2
Use HAVING for post-aggregate filtering
Use HAVING for post-aggregate filtering
SELECT col, COUNT(*) AS cnt FROM t GROUP BY col HAVING cnt > 1;
Why this works
HAVING filters after aggregation and can reference SELECT aliases.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 1993 ER_ILLEGAL_REFERENCE
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev