# Data and databases rules

Follow these CodeWiki-derived rules when you work in this project.

- A window orders by a nonunique value, but `ROW_NUMBER() = 1` is treated as a stable choice.
  Why: Peers have no defined order, so a plan, index, or version change can select another row from the same data.
  Source: [Advanced SQL](https://codewiki.com/data/sql-advanced/)
- An aggregate window omits its frame, and the default is assumed to mean “from the first physical row to this physical row.” In SQLite, a window order gives you a default `RANGE ...
  Why: CURRENT ROW` frame, which includes the current row's peers.
  Source: [Advanced SQL](https://codewiki.com/data/sql-advanced/)
- A query refers to a window alias, or calls a window function, in the same query level's `WHERE`.
  Why: Filtering happens before window evaluation, so the alias doesn't exist yet and the expression isn't legal there.
  Source: [Advanced SQL](https://codewiki.com/data/sql-advanced/)
- A recursive query increments a depth counter but doesn't track visited nodes.
  Why: A depth cap stops unbounded expansion, but it can silently return a truncated tree and hide a cycle in the data.
  Source: [Advanced SQL](https://codewiki.com/data/sql-advanced/)
- Do not assume this is safe: `NOT IN (subquery)` excludes a nullable column.
  Why: One `NULL` on the right can turn every otherwise nonmatching outer row into `UNKNOWN` and remove all of them.
  Source: [Advanced SQL](https://codewiki.com/data/sql-advanced/)
- A CTE is treated as a cache that must run once, or as a barrier that must prevent optimizer rewrites.
  Why: Different databases and versions can inline, materialize, or otherwise implement an ordinary CTE.
  Source: [Advanced SQL](https://codewiki.com/data/sql-advanced/)