Skip to content

:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.

31.2 KB
Raw Blame
import type { MetricsAttributeScope, MetricsQueryFilter, MetricsQueryGroupBy } from '~/queries/schema/schema-general'

import { PER_SERIES_COLUMN } from './builderToSql'
import {
    type BuilderClause,
    type BuilderQuery,
    type ConversionResult,
    ENGINE_QUANTILE,
    MAX_CONVERTED_CLAUSES,
    clauseAlias,
} from './types'

/**
 * SQL → builder, best effort.
 *
 * This is not a SQL parser. It splits the statement into its SELECT parts and recognizes the
 * shapes builderToSql writes, plus the simple queries people write by hand
 * (`SELECT toStartOfInterval(timestamp, {interval}) AS time, avg(value) AS value FROM posthog.metrics …`).
 * Every part it does not recognize becomes an issue, so the caller can warn before it drops it.
 */

type TokenType = 'word' | 'quoted' | 'string' | 'number' | 'placeholder' | 'punct'

interface Token {
    type: TokenType
    value: string
}

export class SqlReadError extends Error {}

const PUNCTUATION = [
    '!=',
    '>=',
    '<=',
    '<>',
    '==',
    '->',
    '(',
    ')',
    ',',
    '.',
    '=',
    '<',
    '>',
    '+',
    '-',
    '*',
    '/',
    '[',
    ']',
    '%',
]

function tokenize(sql: string): Token[] {
    const tokens: Token[] = []
    let i = 0
    while (i < sql.length) {
        const ch = sql[i]
        const rest = sql.slice(i)
        if (/\s/.test(ch)) {
            i++
        } else if (rest.startsWith('--')) {
            const end = sql.indexOf('\n', i)
            i = end === -1 ? sql.length : end
        } else if (rest.startsWith('/*')) {
            const end = sql.indexOf('*/', i + 2)
            i = end === -1 ? sql.length : end + 2
        } else if (ch === "'" || ch === '`' || ch === '"') {
            let value = ''
            let j = i + 1
            while (j < sql.length && sql[j] !== ch) {
                if (sql[j] === '\\' && j + 1 < sql.length) {
                    value += sql[j + 1]
                    j += 2
                    continue
                }
                value += sql[j]
                j++
            }
            if (j >= sql.length) {
                throw new SqlReadError('The SQL has a quote that is not closed.')
            }
            tokens.push({ type: ch === "'" ? 'string' : 'quoted', value })
            i = j + 1
        } else if (ch === '{') {
            const end = sql.indexOf('}', i)
            if (end === -1) {
                throw new SqlReadError('The SQL has a placeholder that is not closed.')
            }
            tokens.push({ type: 'placeholder', value: sql.slice(i + 1, end).trim() })
            i = end + 1
        } else if (/[0-9]/.test(ch) || (ch === '.' && /[0-9]/.test(sql[i + 1] ?? ''))) {
            const match = /^(\d+\.?\d*|\.\d+)([eE][+-]?\d+)?/.exec(rest)!
            tokens.push({ type: 'number', value: match[0] })
            i += match[0].length
        } else if (/[a-zA-Z_]/.test(ch)) {
            const match = /^[a-zA-Z_][a-zA-Z0-9_$]*/.exec(rest)!
            tokens.push({ type: 'word', value: match[0] })
            i += match[0].length
        } else {
            const op = PUNCTUATION.find((candidate) => rest.startsWith(candidate))
            if (!op) {
                throw new SqlReadError(`The SQL has a character that cannot be read: "${ch}".`)
            }
            tokens.push({ type: 'punct', value: op })
            i += op.length
        }
    }
    return tokens
}

const isWord = (token: Token | undefined, ...words: string[]): boolean =>
    token?.type === 'word' && words.includes(token.value.toUpperCase())
const isPunct = (token: Token | undefined, value: string): boolean => token?.type === 'punct' && token.value === value

/** Canonical text of a token run, for comparing expressions without caring about spacing or quoting style. */
function canon(tokens: Token[]): string {
    return tokens
        .map((token, i) => {
            const text =
                token.type === 'string'
                    ? JSON.stringify(token.value)
                    : token.type === 'quoted'
                      ? `\`${token.value}\``
                      : token.type === 'placeholder'
                        ? `{${token.value}}`
                        : token.value
            const previous = tokens[i - 1]
            const needsSpace =
                previous && /^[a-zA-Z0-9_]/.test(text) && previous.type !== 'punct' && previous.type !== 'placeholder'
            return needsSpace ? ` ${text}` : text
        })
        .join('')
}

/** Splits a token run at depth-0 tokens that `isSeparator` accepts, dropping the separators. */
function splitTopLevel(tokens: Token[], isSeparator: (tokens: Token[], index: number) => number): Token[][] {
    const parts: Token[][] = []
    let current: Token[] = []
    let depth = 0
    for (let i = 0; i < tokens.length; i++) {
        const token = tokens[i]
        if (isPunct(token, '(') || isPunct(token, '[')) {
            depth++
        } else if (isPunct(token, ')') || isPunct(token, ']')) {
            depth--
        }
        const width = depth === 0 ? isSeparator(tokens, i) : 0
        if (width > 0) {
            parts.push(current)
            current = []
            i += width - 1
            continue
        }
        current.push(token)
    }
    parts.push(current)
    return parts
}

const commaSeparator = (tokens: Token[], i: number): number => (isPunct(tokens[i], ',') ? 1 : 0)
const andSeparator = (tokens: Token[], i: number): number => (isWord(tokens[i], 'AND') ? 1 : 0)
const unionSeparator = (tokens: Token[], i: number): number =>
    isWord(tokens[i], 'UNION') && isWord(tokens[i + 1], 'ALL') ? 2 : 0

/** Removes parentheses that wrap the whole run. */
function unwrap(tokens: Token[]): Token[] {
    while (tokens.length >= 2 && isPunct(tokens[0], '(') && isPunct(tokens[tokens.length - 1], ')')) {
        let depth = 0
        let closesAtEnd = true
        for (let i = 0; i < tokens.length; i++) {
            if (isPunct(tokens[i], '(')) {
                depth++
            } else if (isPunct(tokens[i], ')')) {
                depth--
                if (depth === 0 && i !== tokens.length - 1) {
                    closesAtEnd = false
                    break
                }
            }
        }
        if (!closesAtEnd) {
            break
        }
        tokens = tokens.slice(1, -1)
    }
    return tokens
}

interface Column {
    expr: Token[]
    alias: string | null
}

interface Join {
    source: Source
    alias: string | null
}

type Source = { kind: 'table'; name: string } | { kind: 'subquery'; tokens: Token[] }

interface SelectParts {
    columns: Column[]
    source: Source | null
    sourceAlias: string | null
    joins: Join[]
    where: Token[][]
    groupBy: Token[][]
    having: Token[]
    limit: Token[]
}

const CLAUSE_KEYWORDS = ['FROM', 'WHERE', 'GROUP', 'HAVING', 'ORDER', 'LIMIT', 'SETTINGS']
const JOIN_WORDS = ['LEFT', 'RIGHT', 'INNER', 'FULL', 'CROSS', 'JOIN', 'OUTER', 'ANY', 'ALL', 'ASOF', 'SEMI', 'ANTI']

function parseColumn(tokens: Token[]): Column {
    const last = tokens[tokens.length - 1]
    if (
        tokens.length >= 3 &&
        isWord(tokens[tokens.length - 2], 'AS') &&
        (last.type === 'word' || last.type === 'quoted')
    ) {
        return { expr: tokens.slice(0, -2), alias: last.value }
    }
    if (tokens.length === 1 && tokens[0].type === 'word') {
        return { expr: tokens, alias: tokens[0].value }
    }
    if (tokens.length === 3 && isPunct(tokens[1], '.') && (tokens[2].type === 'word' || tokens[2].type === 'quoted')) {
        return { expr: tokens, alias: tokens[2].value }
    }
    return { expr: tokens, alias: null }
}

/** Reads `<table or (subquery)> [AS] alias`, returning the source and the tokens after it. */
function parseSource(tokens: Token[]): { source: Source; alias: string | null; rest: Token[] } {
    let index = 0
    let source: Source
    if (isPunct(tokens[0], '(')) {
        let depth = 0
        for (; index < tokens.length; index++) {
            if (isPunct(tokens[index], '(')) {
                depth++
            } else if (isPunct(tokens[index], ')') && --depth === 0) {
                break
            }
        }
        source = { kind: 'subquery', tokens: tokens.slice(1, index) }
        index++
    } else {
        const name: string[] = []
        while (index < tokens.length && (tokens[index].type === 'word' || tokens[index].type === 'quoted')) {
            if (isWord(tokens[index], ...JOIN_WORDS, 'AS', 'ON') && name.length) {
                break
            }
            name.push(tokens[index].value)
            index++
            if (!isPunct(tokens[index], '.')) {
                break
            }
            index++
        }
        if (!name.length) {
            throw new SqlReadError(
                isPunct(tokens[0], ',')
                    ? 'Tables joined with a comma are not supported. Use JOIN.'
                    : 'The FROM part of the SQL cannot be read.'
            )
        }
        source = { kind: 'table', name: name.join('.') }
    }
    let alias: string | null = null
    if (isWord(tokens[index], 'AS')) {
        index++
    }
    if (
        tokens[index] &&
        (tokens[index].type === 'word' || tokens[index].type === 'quoted') &&
        !isWord(tokens[index], ...JOIN_WORDS, 'ON')
    ) {
        alias = tokens[index].value
        index++
    }
    return { source, alias, rest: tokens.slice(index) }
}

function parseSelect(tokens: Token[]): SelectParts {
    tokens = unwrap(tokens)
    if (isWord(tokens[0], 'WITH')) {
        throw new SqlReadError('Common table expressions (WITH) are not supported.')
    }
    if (!isWord(tokens[0], 'SELECT')) {
        throw new SqlReadError('The SQL must start with SELECT.')
    }
    const sections: Record<string, Token[]> = {}
    let current = 'SELECT'
    let depth = 0
    sections[current] = []
    for (let i = 1; i < tokens.length; i++) {
        const token = tokens[i]
        if (isPunct(token, '(')) {
            depth++
        } else if (isPunct(token, ')')) {
            depth--
        }
        if (depth === 0 && isWord(token, ...CLAUSE_KEYWORDS)) {
            current = token.value.toUpperCase()
            if ((current === 'GROUP' || current === 'ORDER') && isWord(tokens[i + 1], 'BY')) {
                i++
            }
            sections[current] = []
            continue
        }
        sections[current].push(token)
    }
    if (isWord(sections.SELECT[0], 'DISTINCT')) {
        throw new SqlReadError('SELECT DISTINCT is not supported.')
    }

    let source: Source | null = null
    let sourceAlias: string | null = null
    const joins: Join[] = []
    if (sections.FROM?.length) {
        const parsed = parseSource(sections.FROM)
        source = parsed.source
        sourceAlias = parsed.alias
        let rest = parsed.rest
        while (rest.length) {
            while (rest.length && isWord(rest[0], ...JOIN_WORDS)) {
                rest = rest.slice(1)
            }
            const join = parseSource(rest)
            joins.push({ source: join.source, alias: join.alias })
            rest = join.rest
            if (isWord(rest[0], 'ON', 'USING')) {
                const next = rest.findIndex(
                    (token, i) => i > 0 && isWord(token, 'LEFT', 'RIGHT', 'INNER', 'FULL', 'CROSS', 'JOIN')
                )
                rest = next === -1 ? [] : rest.slice(next)
            }
        }
    }
    return {
        columns: splitTopLevel(sections.SELECT, commaSeparator).map(parseColumn),
        source,
        sourceAlias,
        joins,
        where: sections.WHERE ? splitTopLevel(sections.WHERE, andSeparator) : [],
        groupBy: sections.GROUP ? splitTopLevel(sections.GROUP, commaSeparator) : [],
        having: sections.HAVING ?? [],
        limit: sections.LIMIT ?? [],
    }
}

const STRING = `"(?:[^"\\\\]|\\\\.)*"`
const FIELD_PATTERNS: [RegExp, (match: RegExpExecArray) => { key: string; scope?: MetricsAttributeScope }][] = [
    [/^(?:\w+\.)?service_name$/i, () => ({ key: 'service_name' })],
    [
        new RegExp(`^arrayElement\\((resource_attributes|attributes),(${STRING})\\)$`, 'i'),
        (m) => ({ key: JSON.parse(m[2]), scope: m[1].toLowerCase() === 'attributes' ? 'attribute' : 'resource' }),
    ],
    [
        new RegExp(`^(resource_attributes|attributes)\\[(${STRING})\\]$`, 'i'),
        (m) => ({ key: JSON.parse(m[2]), scope: m[1].toLowerCase() === 'attributes' ? 'attribute' : 'resource' }),
    ],
    [
        new RegExp(
            `^if\\(arrayElement\\(resource_attributes,(${STRING})\\)!="",arrayElement\\(resource_attributes,\\1\\),arrayElement\\(attributes,\\1\\)\\)$`,
            'i'
        ),
        (m) => ({ key: JSON.parse(m[1]) }),
    ],
    [/^toString\((.*)\)$/i, (m) => readField(m[1]) ?? { key: '' }],
]

function readField(text: string): { key: string; scope?: MetricsAttributeScope } | null {
    for (const [pattern, read] of FIELD_PATTERNS) {
        const match = pattern.exec(text)
        if (match) {
            const field = read(match)
            return field.key ? field : null
        }
    }
    return null
}

const withScope = <T extends { key: string }>(item: T, scope: MetricsAttributeScope | undefined): T =>
    scope && scope !== 'auto' ? { ...item, scope } : item

const toBuilderRegex = (values: string[]): string =>
    `^(?:${values.map((value) => value.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')).join('|')})$`

class ClauseSqlReader {
    clause: Partial<BuilderClause> & { filters: MetricsQueryFilter[] } = { filters: [] }

    constructor(private issues: string[]) {}

    private note(issue: string): void {
        if (!this.issues.includes(issue)) {
            this.issues.push(issue)
        }
    }

    /** One WHERE condition of the points query or the series subquery. */
    private readCondition(tokens: Token[]): void {
        const text = canon(unwrap(tokens))
        const nameMatch = new RegExp(`^metric_name=(${STRING})$`, 'i').exec(text)
        if (nameMatch) {
            const name = JSON.parse(nameMatch[1])
            if (this.clause.metricName && this.clause.metricName !== name) {
                this.note(
                    `The SQL reads more than one metric (${this.clause.metricName}, ${name}); only the first is kept.`
                )
            } else {
                this.clause.metricName = name
            }
            return
        }
        const typeMatch = new RegExp(`^metric_type=(${STRING})$`, 'i').exec(text)
        if (typeMatch) {
            this.clause.metricType = JSON.parse(typeMatch[1])
            return
        }
        // Time bounds come from the chart's date range and interval.
        if (
            /^(timestamp|last_seen|time_bucket)(>=|<|<=|>)/i.test(text) ||
            /^notEmpty\(histogram_counts\)$/i.test(text)
        ) {
            if (!/\{(date_from|date_to)\}/.test(text) && !/histogram_counts/.test(text)) {
                this.note(`The fixed time bound "${text}" is replaced by the chart date range.`)
            }
            return
        }
        const scoped = /^series_fingerprint IN\((.*)\)$/i.exec(text)
        if (scoped) {
            this.readSeriesSubquery(unwrap(tokens).slice(2))
            return
        }
        if (this.readFilter(text)) {
            return
        }
        this.note(`The condition "${text}" is not supported and is dropped.`)
    }

    private readFilter(text: string): boolean {
        const negated = /^NOT\s*/i.exec(text)
        const body = negated ? text.slice(negated[0].length) : text
        const match = new RegExp(`^match\\((.*),(${STRING})\\)$`, 'i').exec(body)
        if (match) {
            const field = readField(match[1].trim())
            if (field) {
                this.clause.filters.push(
                    withScope(
                        { key: field.key, op: negated ? 'not_regex' : 'regex', value: JSON.parse(match[2]) },
                        field.scope
                    )
                )
                return true
            }
        }
        if (negated) {
            return false
        }
        const comparison = new RegExp(`^(.*?)(!=|<>|==|=)(${STRING})$`).exec(text)
        if (comparison) {
            const field = readField(comparison[1].trim())
            if (field) {
                this.clause.filters.push(
                    withScope(
                        {
                            key: field.key,
                            op: comparison[2] === '=' || comparison[2] === '==' ? 'eq' : 'neq',
                            value: JSON.parse(comparison[3]),
                        },
                        field.scope
                    )
                )
                return true
            }
        }
        const inList = new RegExp(`^(.*?)(NOT IN|IN)\\(((?:${STRING},?)+)\\)$`, 'i').exec(text)
        if (inList) {
            const field = readField(inList[1].trim())
            const values = [...inList[3].matchAll(new RegExp(STRING, 'g'))].map((m) => JSON.parse(m[0]) as string)
            if (field && values.length) {
                const op = inList[2].toUpperCase() === 'IN' ? 'regex' : 'not_regex'
                this.clause.filters.push(withScope({ key: field.key, op, value: toBuilderRegex(values) }, field.scope))
                return true
            }
        }
        return false
    }

    private readSeriesSubquery(tokens: Token[]): void {
        const select = parseSelect(unwrap(tokens))
        if (select.source?.kind !== 'table' || !/(^|\.)metric_series$/i.test(select.source.name)) {
            this.note('A series filter that does not read posthog.metric_series is dropped.')
            return
        }
        select.where.forEach((condition) => this.readCondition(condition))
    }

    readPoints(select: SelectParts): void {
        select.where.forEach((condition) => this.readCondition(condition))
    }
}

const isMetricsTable = (source: Source | null): boolean =>
    source?.kind === 'table' && /(^|\.)(metrics|metric_samples)$/i.test(source.name)

const SIMPLE_AGGREGATIONS: Record<string, BuilderClause['aggregation']> = {
    sum: 'sum',
    avg: 'avg',
    min: 'min',
    max: 'max',
    count: 'count',
}

// Result columns, and the histogram columns builderToSql passes between its selects.
const NON_LABEL_COLUMNS = new Set([
    'time',
    'value',
    'clause',
    'bounds',
    'layouts',
    'counts',
    'series_fingerprints',
    PER_SERIES_COLUMN,
])

interface ReadClause {
    clause: BuilderClause
    /** Alias from a `'a' AS clause` column. */
    label: string | null
}

/** Reads one clause from a single SELECT and the subqueries in its FROM. */
function readClauseSelect(tokens: Token[], issues: string[]): ReadClause | null {
    const note = (issue: string): void => {
        if (!issues.includes(issue)) {
            issues.push(issue)
        }
    }
    const chain: SelectParts[] = [parseSelect(tokens)]
    while (chain[chain.length - 1].source?.kind === 'subquery') {
        const source = chain[chain.length - 1].source as Extract<Source, { kind: 'subquery' }>
        if (splitTopLevel(source.tokens, unionSeparator).length > 1) {
            note('A UNION inside a series is not supported.')
            return null
        }
        chain.push(parseSelect(source.tokens))
    }
    const points = chain[chain.length - 1]
    if (!isMetricsTable(points.source)) {
        note('The SQL does not read posthog.metrics.')
        return null
    }
    const reader = new ClauseSqlReader(issues)
    reader.readPoints(points)
    if (!reader.clause.metricName) {
        note('The SQL has no metric_name condition, so there is no metric to chart.')
        return null
    }

    const allText = chain.flatMap((select) => select.columns.map((column) => canon(column.expr))).join(' ')
    const outer = chain[0]
    const valueColumn = outer.columns.find((column) => column.alias?.toLowerCase() === 'value')
    if (!valueColumn) {
        note('The SQL has no "value" column.')
        return null
    }
    const valueText = canon(valueColumn.expr).replace(/^toFloat(?:64)?\((.*)\)$/i, '$1')
    const timeColumn = outer.columns.find((column) => column.alias?.toLowerCase() === 'time')
    if (
        !timeColumn ||
        (!/\{interval\}/.test(canon(timeColumn.expr)) && !/^(\w+\.)?time$/i.test(canon(timeColumn.expr)))
    ) {
        note('The time buckets are replaced by the chart interval.')
    }
    if (chain.some((select) => select.limit.length)) {
        note('LIMIT is dropped.')
    }

    let aggregation: BuilderClause['aggregation']
    let rangeFunction: BuilderClause['rangeFunction']
    let quantile: number | undefined
    if (/histogram_counts|sumForEach/i.test(allText)) {
        aggregation = 'histogram_quantile'
        const rank = /(\d*\.?\d+)\*arraySum\(counts\)/i.exec(allText)
        quantile = rank ? Number(rank[1]) : 0.95
        if (!rank) {
            note('The histogram quantile cannot be found; 0.95 is used.')
        }
    } else {
        const counter = /lagInFrame/i.test(allText)
        if (counter) {
            rangeFunction = /\/\{interval_seconds\}/.test(allText) ? 'rate' : 'increase'
        }
        const quantileMatch = /^quantile\((\d*\.?\d+)\)\((.*)\)$/i.exec(valueText)
        const simpleMatch = /^(\w+)\((.*)\)$/.exec(valueText)
        if (/^(\w+\.)?series_value$/i.test(valueText)) {
            // Each series stays its own line.
            aggregation = undefined
        } else if (counter && /^sum\((\w+\.)?contribution\)/i.test(valueText)) {
            // Summing every sample's contribution sums the per-series values.
            aggregation = 'sum'
        } else if (quantileMatch) {
            aggregation = 'quantile'
            quantile = ENGINE_QUANTILE
            if (Number(quantileMatch[1]) !== ENGINE_QUANTILE) {
                note(`Metrics support only quantile ${ENGINE_QUANTILE}; quantile(${quantileMatch[1]}) uses it.`)
            }
        } else if (simpleMatch && SIMPLE_AGGREGATIONS[simpleMatch[1].toLowerCase()]) {
            aggregation = SIMPLE_AGGREGATIONS[simpleMatch[1].toLowerCase()]
        } else {
            note(`The value "${valueText}" is not a supported aggregation; sum is used.`)
            aggregation = 'sum'
        }
        // The builder reduces each series to one value per bucket before it aggregates.
        if (chain.length === 1 && !counter) {
            note(
                aggregation === 'count'
                    ? 'The SQL counts samples; the builder counts series.'
                    : 'The SQL aggregates every sample; the builder aggregates the last value of each series in each interval.'
            )
        }
    }

    // Group-by labels: the select that joins posthog.metric_series names them group_0, group_1, …
    // Without a join, the outer select reads label columns directly.
    const groupBy: MetricsQueryGroupBy[] = []
    let label: string | null = null
    const labelSelect = chain.find((select) => select.joins.some((join) => join.source.kind === 'subquery')) ?? outer
    const groupFields = new Map<string, { key: string; scope?: MetricsAttributeScope }>()
    for (const join of labelSelect.joins) {
        if (join.source.kind !== 'subquery') {
            continue
        }
        for (const column of parseSelect(join.source.tokens).columns) {
            const field = /^any\((.*)\)$/i.exec(canon(column.expr))
            const read = field ? readField(field[1]) : null
            if (column.alias && read) {
                groupFields.set(column.alias, read)
            }
        }
    }
    for (const column of labelSelect.columns) {
        const alias = column.alias?.toLowerCase()
        if (alias === 'clause' && column.expr.length === 1 && column.expr[0].type === 'string') {
            label = column.expr[0].value
            continue
        }
        if (!alias || NON_LABEL_COLUMNS.has(alias)) {
            continue
        }
        const text = canon(column.expr)
        // A column that passes on a label an inner select sets to '' is not a group-by.
        const emptyInside = chain.some((select) =>
            select.columns.some((inner) => inner.alias?.toLowerCase() === alias && canon(inner.expr) === '""')
        )
        if (emptyInside && /^(?:\w+\.)?(?:\w+|`[^`]*`)$/.test(text)) {
            continue
        }
        const groupMatch = /^(?:\w+\.)?(group_\d+)$/.exec(text)
        const field = groupMatch ? groupFields.get(groupMatch[1]) : readField(text)
        if (field) {
            groupBy.push(withScope({ key: field.key }, field.scope))
        } else if (text !== '""') {
            note(`The column "${column.alias}" is not a label the builder can group by and is dropped.`)
        }
    }

    const { metricName, metricType, filters } = reader.clause
    return {
        clause: {
            name: '',
            metricName: metricName!,
            ...(aggregation ? { aggregation } : {}),
            ...(rangeFunction ? { rangeFunction } : {}),
            ...(metricType ? { metricType } : {}),
            ...(filters.length ? { filters } : {}),
            ...(groupBy.length ? { groupBy } : {}),
            ...(quantile !== undefined ? { quantile } : {}),
        } as BuilderClause,
        label,
    }
}

const isZero = (token: Token | undefined): boolean => token?.type === 'number' && Number(token.value) === 0

/**
 * The builder formula of the value expression builderToSql writes: `+ - * /` over clause aliases and
 * numbers, where `if(r = 0, 0, l / r)` is the division `l / r`. Null for any other SQL.
 */
function formulaFromSql(tokens: Token[]): string | null {
    let i = 0
    const expr = (): string | null => {
        let lhs = term()
        while (lhs !== null && (isPunct(tokens[i], '+') || isPunct(tokens[i], '-'))) {
            const op = tokens[i++].value
            const rhs = term()
            lhs = rhs === null ? null : `${lhs} ${op} ${rhs}`
        }
        return lhs
    }
    const term = (): string | null => {
        let lhs = factor()
        while (lhs !== null && (isPunct(tokens[i], '*') || isPunct(tokens[i], '/'))) {
            const op = tokens[i++].value
            const rhs = factor()
            lhs = rhs === null ? null : `${lhs} ${op} ${rhs}`
        }
        return lhs
    }
    const factor = (): string | null => {
        const token = tokens[i]
        if (isPunct(token, '-') || isPunct(token, '+')) {
            i++
            const inner = factor()
            return inner === null ? null : `${token.value}${inner}`
        }
        // The builder formula has no exponent notation.
        if (token?.type === 'number' && !/e/i.test(token.value)) {
            i++
            return token.value
        }
        if (isPunct(token, '(')) {
            i++
            const inner = expr()
            if (inner === null || !isPunct(tokens[i], ')')) {
                return null
            }
            i++
            return `(${inner})`
        }
        if (isWord(token, 'IF') && isPunct(tokens[i + 1], '(')) {
            i += 2
            const divisor = expr()
            if (
                divisor === null ||
                !isPunct(tokens[i], '=') ||
                !isZero(tokens[i + 1]) ||
                !isPunct(tokens[i + 2], ',') ||
                !isZero(tokens[i + 3]) ||
                !isPunct(tokens[i + 4], ',')
            ) {
                return null
            }
            i += 5
            const division = expr()
            if (division === null || !isPunct(tokens[i], ')') || !division.endsWith(` / ${divisor}`)) {
                return null
            }
            i++
            return division
        }
        if (token?.type === 'word') {
            i++
            return token.value.toLowerCase()
        }
        return null
    }
    const formula = expr()
    return formula !== null && i === tokens.length ? formula : null
}

export function sqlToBuilder(sql: string): ConversionResult<BuilderQuery> {
    if (!sql.trim()) {
        return { value: { clauses: [] }, issues: [] }
    }
    const issues: string[] = []
    const tooCustom = (): ConversionResult<BuilderQuery> => ({
        value: null,
        issues: [...issues, 'This SQL cannot be converted.'],
    })
    try {
        const tokens = tokenize(sql.trim().replace(/;\s*$/, ''))
        const branches = splitTopLevel(tokens, unionSeparator)

        if (branches.length > 1) {
            const clauses: BuilderClause[] = []
            for (const branch of branches.slice(0, MAX_CONVERTED_CLAUSES)) {
                const read = readClauseSelect(branch, issues)
                if (!read) {
                    return tooCustom()
                }
                clauses.push({ ...read.clause, name: read.label ?? clauseAlias(clauses.length) })
            }
            if (branches.length > MAX_CONVERTED_CLAUSES) {
                issues.push(`A query can have at most ${MAX_CONVERTED_CLAUSES} series; the others are dropped.`)
            }
            return { value: { clauses: dedupeNames(clauses) }, issues }
        }

        const formula = readFormula(tokens, issues)
        if (formula) {
            return formula.value ? formula : tooCustom()
        }
        const read = readClauseSelect(tokens, issues)
        return read ? { value: { clauses: [{ ...read.clause, name: 'a' }] }, issues } : tooCustom()
    } catch (error) {
        if (error instanceof SqlReadError) {
            issues.push(error.message)
            return tooCustom()
        }
        throw error
    }
}

const dedupeNames = (clauses: BuilderClause[]): BuilderClause[] => {
    const seen = new Set<string>()
    return clauses.map((clause, index) => {
        const name = /^[a-z_][a-z0-9_]*$/.test(clause.name) && !seen.has(clause.name) ? clause.name : clauseAlias(index)
        seen.add(name)
        return { ...clause, name }
    })
}

/**
 * The formula shape builderToSql writes: an outer formula over per-clause sums of a UNION ALL, which
 * keeps the label sets every series has. Null when it is not that shape.
 */
function readFormula(tokens: Token[], issues: string[]): ConversionResult<BuilderQuery> | null {
    const outer = parseSelect(tokens)
    if (outer.source?.kind !== 'subquery') {
        return null
    }
    const middle = parseSelect(outer.source.tokens)
    if (middle.source?.kind !== 'subquery') {
        return null
    }
    const branches = splitTopLevel(middle.source.tokens, unionSeparator)
    // `sum(sum(x)) OVER (PARTITION BY time)` spreads a series without labels over every label set.
    const sums = middle.columns.filter((column) =>
        /^sum\((?:[a-z_][a-z0-9_]*|sum\([a-z_][a-z0-9_]*\)\)OVER\(PARTITION BY time)\)$/i.test(canon(column.expr))
    )
    if (branches.length < 1 || sums.length === 0 || sums.length !== branches.length) {
        return null
    }
    const valueColumn = outer.columns.find((column) => column.alias?.toLowerCase() === 'value')
    if (!valueColumn) {
        return null
    }
    const clauses: BuilderClause[] = []
    for (const branch of branches) {
        const select = parseSelect(branch)
        const own = select.columns.find((column) => canon(column.expr).toLowerCase() === 'value' && column.alias)
        if (!own || select.source?.kind !== 'subquery') {
            return null
        }
        const read = readClauseSelect(select.source.tokens, issues)
        if (!read) {
            return { value: null, issues }
        }
        clauses.push({ ...read.clause, name: own.alias!.toLowerCase() })
    }
    const formula = formulaFromSql(valueColumn.expr)
    if (formula === null) {
        issues.push(`The formula "${canon(valueColumn.expr)}" uses SQL the builder formula does not support.`)
        return { value: null, issues }
    }
    return { value: { clauses, formula }, issues }
}