MySQL STDDEV and VARIANCE are transpiled as sample instead of population #8626

Activity

1. thisismairaj commented on Oct 14, 2026

   

   thisismairaj

   on Oct 14, 2026

   More actions

   I looked into this — it reproduces, and it's actually a bit broader than the title suggests (both directions are affected, not just the one in the report).

   Root cause: dialects/mysql.py has no explicit mapping for VARIANCE/STDDEV, so parsing falls through to the base parser.Parser.FUNCTIONS, which maps those literal names to the generic exp.Variance/exp.Stddev nodes. Those generic nodes are treated as sample semantics elsewhere in the codebase (e.g. postgres.py renders exp.Variance as VAR_SAMP). But per the MySQL docs, bare VARIANCE()/STDDEV() are documented synonyms for VAR_POP()/STDDEV_POP() — population, not sample:

   >>> import sqlglot
   >>> sqlglot.transpile("SELECT VARIANCE(x), STDDEV(x) FROM t", read="mysql", write="postgres")
   ['SELECT VAR_SAMP(x), STDDEV(x) FROM t']

   VAR_SAMP is just wrong (should be VAR_POP), and bare STDDEV in Postgres is also sample per the SQL standard, so that one's silently wrong too — same bug, no error raised either time.

   The mirror-image bug exists too: writing a sample-semantics node to MySQL falls back to the bare name, which MySQL interprets as population:

   >>> sqlglot.transpile("SELECT VAR_SAMP(x), STDDEV_SAMP(x) FROM t", read="mysql", write="mysql")
   ['SELECT VARIANCE(x), STDDEV_SAMP(x) FROM t']

   VAR_SAMP(x) round-trips through MySQL as VARIANCE(x) — population. STDDEV_SAMP happens to survive because it has its own AST class with a default name MySQL also supports natively.

   Fix (verified locally against the installed package, both directions, across mysql/postgres/duckdb/snowflake/bigquery):

   In MySQL.Parser.FUNCTIONS:

   "STDDEV": exp.StddevPop.from_arg_list,
   ...
   "VARIANCE": exp.VariancePop.from_arg_list,

   In MySQL.Generator.TRANSFORMS (mirrors the pattern already used by postgres/bigquery/duckdb/presto/spark2):

   exp.Stddev: rename_func("STDDEV_SAMP"),
   ...
   exp.Variance: rename_func("VAR_SAMP"),
   exp.VariancePop: rename_func("VAR_POP"),

   After this, mysql -> mysql round-trips VARIANCE/STDDEV/VAR_SAMP/STDDEV_SAMP all correctly, and mysql -> {postgres,duckdb,snowflake,bigquery} all produce the right pop/sample function.

   Happy to open a PR with this plus a test if that's useful — let me know.
2. treysp

   closed this as completedin #8632on Oct 14, 2026
3. deepyaman

   added a commit that references this issue on Oct 18, 2026

   fix(mssql,oracle): render bare Stddev as sample standard deviation (#…

   ...

   d6e6811