1Z0-909 common mistakes
1Z0-909 · Understand the traps and how to avoid them.
Using implicit comma-style joins and relying on the WHERE clause to correlate tables, then being unable to predict which rows survive when an outer join is mixed with inner-join conditions.
Candidates carry habits from older SQL dialects and from tutorials that predate ANSI join syntax; the exam deliberately mixes comma joins, USING, ON, and LEFT JOIN in one statement to test whether the candidate understands precedence between join evaluation and WHERE filtering.
What to do instead
Rewrite every multi-table query with explicit JOIN ... ON clauses before answering. Practise predicting results for LEFT JOIN followed by a WHERE predicate on the right table (which silently converts it to an inner join) versus placing that predicate in the ON clause. Build a small two-table fixture and run both variants to see the row-count difference.
Assuming a subquery in the SELECT list or WHERE clause is always executed once, and misjudging when the optimizer materialises a derived table versus merging it into the outer query.
The exam tests semantic equivalence and performance reasoning around derived tables, IN/EXISTS, and correlated subqueries. Candidates memorise 'subqueries are slow' or 'derived tables are always materialised' as absolutes and then pick the wrong option when the optimizer can merge or when a LIMIT in the derived table forces materialisation.
What to do instead
Learn the specific rules: derived tables with no aggregation, DISTINCT, GROUP BY, LIMIT, or UNION are mergeable; a LIMIT or window function blocks merging. Use EXPLAIN and EXPLAIN FORMAT=TREE on your own MySQL 8.0 instance to confirm which subqueries became semi-joins and which were materialised, and reason from the plan rather than from slogans.
Treating a WITH (CTE) clause as a guaranteed single evaluation, and being surprised when a CTE referenced twice is materialised twice or when a recursive CTE needs an explicit column list and termination condition.
Common table expressions arrived in MySQL 8.0 and candidates assume they behave like temporary tables. In MySQL 8.0 a CTE is materialised by default in many cases but can be merged, and recursive CTEs have strict syntax requirements (RECURSIVE keyword, UNION ALL or UNION DISTINCT, cte_max_recursion_depth) that the exam probes.
What to do instead
Write recursive CTEs from scratch for hierarchies and number series, always including the column list, the anchor SELECT, UNION ALL, and a WHERE guard on the recursive member. Test what happens when you omit RECURSIVE, and check the effect of SET SESSION cte_max_recursion_depth. Compare EXPLAIN output for a CTE used once versus twice.
Writing window functions that omit an explicit frame clause and then predicting the wrong result for running totals, moving averages, or LAST_VALUE, and mixing up RANK, DENSE_RANK, and ROW_NUMBER on ties.
The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which behaves differently from ROWS when there are duplicate ORDER BY values, and LAST_VALUE with the default frame returns the current row rather than the partition's last row. Candidates also assume RANK and DENSE_RANK are interchangeable.
What to do instead
For every window question, state the partition, the ordering, and the frame explicitly. Memorise that LAST_VALUE and FIRST_VALUE need an explicit frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to return partition boundaries. Construct a table with duplicate sort keys and compare ROW_NUMBER, RANK, and DENSE_RANK side by side, and note that window functions cannot appear in WHERE and require a derived table or CTE to filter on.
Confusing GROUP BY with DISTINCT and with window partitioning, and writing queries that select non-aggregated columns not present in the GROUP BY list, expecting MySQL to pick an arbitrary value.
MySQL historically allowed non-aggregated columns under GROUP BY, and ONLY_FULL_GROUP_BY is enabled by default in 8.0. Candidates rely on the permissive behaviour or on functional-dependency exceptions without understanding when the server can prove the dependency.
What to do instead
Check the sql_mode in force and assume ONLY_FULL_GROUP_BY. Learn the functional-dependency rule: a column is allowed if it is functionally dependent on a grouped column through a unique NOT NULL key. Rewrite ambiguous queries with ANY_VALUE() only when the arbitrary pick is genuinely intended, and use a window function or a join to a pre-aggregated derived table when per-group detail is needed.
Assuming a statement is atomic and that an error rolls back all prior changes, and misunderstanding how transactions interact with DDL, with autocommit, and with implicit commits.
Candidates learn that InnoDB is transactional and generalise it to every statement. In MySQL an error inside a multi-row INSERT without IGNORE can leave earlier rows inserted in autocommit mode, DDL causes an implicit commit, and mixing transactional and non-transactional tables breaks rollback expectations.
What to do instead
Wrap multi-statement units in START TRANSACTION ... COMMIT and test rollback explicitly. Remember that CREATE, ALTER, DROP, TRUNCATE, and RENAME cause implicit commits, that TRUNCATE cannot be rolled back, and that SAVEPOINT with ROLLBACK TO SAVEPOINT gives partial rollback. Verify isolation-level effects with two sessions at REPEATABLE READ, observing consistent snapshot reads versus locking reads with FOR UPDATE.
Mixing up the privileges needed for routine and view execution, and assuming DEFINER routines run with the caller's privileges or that SQL SECURITY INVOKER is the default.
Stored programs default to SQL SECURITY DEFINER, so they execute with the definer's rights, and candidates answer from intuition about application-level security rather than the documented default. They also overlook that EXECUTE on a procedure and SELECT on underlying tables are separate grants.
What to do instead
Memorise the defaults: DEFINER for procedures, functions, views, and triggers, with the definer stored as user@host. Practise creating a procedure as one user, granting only EXECUTE to a second user, and observing success under DEFINER and failure under SQL SECURITY INVOKER. Also learn how triggers cannot be granted EXECUTE directly and that a missing definer account causes errors.
Writing stored program logic that mishandles NULL, cursor termination, or handler scope - for example looping forever because NOT FOUND is not declared, or expecting a CONTINUE handler to undo the failed statement.
Candidates treat stored procedures like application code and forget that a cursor fetch past the last row raises a NOT FOUND condition that must be handled, that handler scope is the block in which it is declared, and that a CONTINUE handler resumes after the statement rather than reversing it.
What to do instead
Always declare a NOT FOUND handler that sets a done flag and use a labelled loop with LEAVE. Declare handlers inside the smallest block that needs them and remember that EXIT handlers propagate outward while CONTINUE resumes. Test a cursor loop against an empty result set, and confirm that DECLARE order is variables, then conditions, then cursors, then handlers.
Choosing the wrong data type or index strategy - storing dates in VARCHAR, using FLOAT for money, or assuming a prefix of a composite index can serve any query - and then failing questions on index usage and covering indexes.
The exam tests practical schema design and index behaviour. Candidates pick types by habit and assume any index helps any predicate, ignoring the leftmost-prefix rule, the effect of functions on indexed columns, and implicit conversions that disable index use.
What to do instead
Learn the leftmost-prefix rule and the covering-index idea, and check with EXPLAIN whether type is ref, range, index, or ALL. Avoid wrapping indexed columns in functions; rewrite WHERE DATE(col) = ... as a range predicate. Choose DECIMAL for exact money, DATETIME or TIMESTAMP with awareness of time zone conversion, and appropriate integer widths. Test implicit string-to-number conversion and observe the index being bypassed.
Misreading JSON and document-store behaviour - assuming ->> and -> are interchangeable, that JSON columns are indexed directly, or that JSON_ARRAYAGG and JSON_TABLE behave like their SQL analogues.
MySQL 8.0 added a large JSON surface and candidates skim the operator semantics. The -> operator returns a JSON value with quotes, ->> returns an unquoted SQL string, and JSON columns need generated columns plus indexes to be searchable efficiently.
What to do instead
Memorise that col->'$.path' equals JSON_EXTRACT and col->>'$.path' equals JSON_UNQUOTE(JSON_EXTRACT(...)). Practise creating a stored generated column from a JSON path and indexing it. Use JSON_TABLE to shred an array into rows and JSON_ARRAYAGG to rebuild it, and check behaviour with JSON_VALID, JSON_CONTAINS_PATH, and NULL versus JSON null.