Los modos de fallo típicos del SQL generado

Descripción

Ya construiste todas las piezas: la validación de forma (03), la de esquema (04), la ejecución segura (05) y el loop de auto-corrección (06). Esta cápsula da un paso atrás y arma el mapa completo: ¿cuáles son los modos de fallo típicos del SQL que genera un modelo, y qué capa de tu pipeline caza cada uno? Es una taxonomía, y su valor es doble: te ayuda a reconocer un fallo cuando lo veas, y —más importante— te enseña la frontera exacta de lo que la validación de M4 puede y no puede hacer.

Porque hay una división limpia. Algunos fallos son ruidosos: el motor los rechaza, tu validación los caza, el loop los corrige. Otros son silenciosos: el SQL corre sin un rasguño y responde otra cosa. Ninguna capa de este módulo detecta los silenciosos —para eso hace falta comparar el resultado contra una respuesta esperada, que es la evaluación del Módulo 7—. Salir de M4 sabiendo qué no cazamos es tan importante como saber qué sí.

Conexión con el módulo

Las cápsulas anteriores construyeron las herramientas; esta las ordena en un mapa. Cuando termines, tendrás una tabla mental de "modo de fallo → capa que lo caza" que usarás al depurar tu asistente, y una frontera clara con el Módulo 7 (evaluación) que la cápsula 08 —el mini-proyecto— consolida.

Analogía: los dos tipos de defecto en una fábrica

Vuelve a la fábrica con estaciones de control de calidad. Los defectos que encuentra caen en dos familias. Los evidentes: una pieza rota, dos piezas pegadas, una pieza del tipo equivocado. El control las detecta y las saca de la línea —hacen ruido, se ven—. Y los sutiles: una pieza que se ve perfecta, pasa todas las inspecciones visuales, y sin embargo mide un milímetro de menos. Ese defecto no lo caza la inspección de la entrada; solo lo caza medir la pieza contra la especificación.

El SQL generado tiene exactamente esas dos familias. Los fallos evidentes —columna que no existe, sintaxis rota, sentencia peligrosa— los caza la validación de M4. Los fallos sutiles —un número que se ve razonable y está mal— solo los caza medir el resultado contra lo esperado, que es M7. Esta cápsula te enseña a distinguirlos.

La taxonomía, ejecutada

Vamos a pasar ocho SQL "generados por el modelo" —cada uno con un modo de fallo distinto— por una función que reporta qué capa lo caza. Reutiliza el strip_sql y la lógica de validate de las cápsulas anteriores; aquí solo la instrumentamos para que diga la capa:

import sqlite3, re

con = sqlite3.connect("reservo.db")

def strip_sql(sql):
    sql = re.sub(r"--[^\n]*", "", sql)
    sql = re.sub(r"/\*.*?\*/", "", sql, flags=re.DOTALL)
    return sql.strip()

def classify(sql, con):
    """Devuelve (capa que caza el fallo, detalle)."""
    body = strip_sql(sql).rstrip(";").strip()
    if ";" in body:
        return "FORMA", "más de una sentencia"
    first = body.split(None, 1)[0].upper()
    if first not in ("SELECT", "WITH"):
        return "FORMA", f"no es lectura ({first})"
    try:
        con.execute("EXPLAIN " + body).fetchall()   # parseo + esquema
    except sqlite3.Error as e:
        return "PARSEO/ESQUEMA", f"{type(e).__name__}: {e}"
    return "NINGUNA (corre)", "—"

modos = [
    ("columna alucinada",              "SELECT SUM(price) FROM bookings"),
    ("tabla equivocada",               "SELECT * FROM reservations"),
    ("columna real, tabla equivocada", "SELECT SUM(amount_cents) FROM bookings"),
    ("comilla sin cerrar",             "SELECT name FROM rooms WHERE name = 'Focus"),
    ("ambigüedad en JOIN",             "SELECT id FROM bookings b JOIN rooms r ON r.id=b.room_id"),
    ("sentencia peligrosa",            "DELETE FROM bookings WHERE id=1"),
    ("filtro faltante (miente)",       "SELECT SUM(price_cents) FROM bookings"),
    ("JOIN cartesiano (miente)",       "SELECT COUNT(*) FROM bookings b JOIN rooms r ON 1=1"),
]
for label, sql in modos:
    layer, detail = classify(sql, con)
    print(f"{label:32} | {layer:16} | {detail}")

Qué esperar:

columna alucinada                | PARSEO/ESQUEMA   | OperationalError: no such column: price
tabla equivocada                 | PARSEO/ESQUEMA   | OperationalError: no such table: reservations
columna real, tabla equivocada   | PARSEO/ESQUEMA   | OperationalError: no such column: amount_cents
comilla sin cerrar               | PARSEO/ESQUEMA   | OperationalError: unrecognized token: "'Focus"
ambigüedad en JOIN               | PARSEO/ESQUEMA   | OperationalError: ambiguous column name: id
sentencia peligrosa              | FORMA            | no es lectura (DELETE)
filtro faltante (miente)         | NINGUNA (corre)  | —
JOIN cartesiano (miente)         | NINGUNA (corre)  | —

Léelo de arriba a abajo: los primeros seis fallos son cazados —por la forma o por parseo/esquema—; los dos últimos pasan la validación y corren. Esa línea divisoria es el corazón de la cápsula.

Los fallos que la validación caza

Columna alucinada

SELECT SUM(price) FROM bookings — la columna del dinero es price_cents, no price. El motor la rechaza con no such column: price. Caza: parseo/esquema (EXPLAIN resuelve el nombre contra el esquema y falla). Es el fallo más común del SQL generado, y el loop de auto-corrección (06) lo arregla casi siempre, porque el error incluso sugiere price_cents.

Tabla equivocada

SELECT * FROM reservations — la tabla es bookings. no such table: reservations. Caza: parseo/esquema. Mismo mecanismo que la columna alucinada, un nivel arriba. El error enriquecido lista las tablas reales, lo que hace la corrección directa.

Columna real, tabla equivocada

SELECT SUM(amount_cents) FROM bookings — amount_cents existe, pero en payments, no en bookings. El motor la rechaza igual (no such column: amount_cents), porque en el contexto FROM bookings esa columna no está. Caza: parseo/esquema. Es el caso sutil que un catálogo ingenuo ("¿existe en alguna tabla?") dejaría pasar y EXPLAIN no —una razón más para dejar que el motor resuelva los nombres—.

Comilla / sintaxis rota

SELECT name FROM rooms WHERE name = 'Focus — falta la comilla de cierre. unrecognized token: "'Focus". Caza: parseo/esquema (aquí, el parseo: ni siquiera es SQL válido). Un modelo que trunca su respuesta o se equivoca al escapar produce esto; EXPLAIN lo caza sin tocar los datos.

Ambigüedad de columnas en un JOIN

SELECT id FROM bookings b JOIN rooms r ON r.id = b.room_id — tanto bookings como rooms tienen una columna id, así que id a secas es ambiguo: el motor no sabe cuál quieres. ambiguous column name: id. Caza: parseo/esquema. La corrección es calificar la columna (b.id o r.id). Es un fallo típico cuando el modelo escribe un JOIN sin prefijar las columnas compartidas, y el error le dice exactamente qué desambiguar.

Sentencia peligrosa

DELETE FROM bookings WHERE id = 1 — no es una lectura. Caza: forma (la primera palabra no es SELECT/WITH), y se rechaza antes de tocar la base. Este es el único que caza la capa de forma en este ejemplo, y es también el más grave: correrlo borraría una fila. (El blindaje de seguridad con todas sus letras —forzar solo-lectura a nivel de conexión, allowlist estricta— es el Módulo 5; aquí la forma ya lo detiene.)

Los fallos que la validación NO caza: el SQL que corre y miente

Aquí está la frontera. Los dos últimos modos pasan toda la validación de M4 —parsean, referencian columnas reales, son lecturas de una sola sentencia— y aun así responden mal.

Filtro faltante

SELECT SUM(price_cents) FROM bookings — suma todas las reservas, incluidas las canceladas. Corre perfecto. Y da un número equivocado:

show = lambda sql: print(con.execute(sql).fetchone()[0])
show("SELECT SUM(price_cents) FROM bookings")                          # sin filtro
show("SELECT SUM(price_cents) FROM bookings WHERE status='confirmed'") # correcto

Qué esperar:

241400
211900

241400 incluye las tres canceladas; la respuesta de negocio correcta es 211900. Los dos SQL corren sin error. Ninguna capa de M4 puede distinguirlos —ambos son válidos—. La diferencia (29500) son justo las reservas canceladas.

JOIN mal formado (semánticamente)

SELECT COUNT(*) FROM bookings b JOIN rooms r ON 1=1 — el JOIN une cada reserva con cada sala (un producto cartesiano), en vez de con su sala por room_id. Compila y corre. Y cuenta de más:

show("SELECT COUNT(*) FROM bookings b JOIN rooms r ON r.id=b.room_id")  # correcto
show("SELECT COUNT(*) FROM bookings b JOIN rooms r ON 1=1")             # mal

Qué esperar:

23
115

El JOIN correcto cuenta 23 reservas; el mal formado cuenta 115 (= 23 × 5 salas). Otra vez: corre sin error, responde mal. El motor no tiene forma de saber que quisiste unir por room_id; hiciste un JOIN válido, solo que no el que la pregunta pedía.

Estos dos son la quinta falla silenciosa que planteamos en la cápsula 02, ahora ejecutada. Cazarlos exige comparar el resultado contra una respuesta esperada —el SQL "gold"—, y eso es exactitud de ejecución, el Módulo 7. Decirlo con todas sus letras: validar que el SQL corra no es validar que responda bien.

La tabla de referencia

Guárdala; es el resumen del módulo entero:

Las ocho primeras filas las cierra este módulo. Las dos últimas —las que corren y mienten— son la frontera con el Módulo 7.

Errores comunes

1. Creer que "pasó la validación" significa "está bien". La validación caza los fallos ruidosos; los silenciosos (filtro faltante, JOIN semánticamente mal) pasan. Un SQL validado puede responder cualquier disparate mientras sea sintáctica y semánticamente válido.

2. Intentar cazar el fallo silencioso con más validación. No se puede: SELECT SUM(price_cents) FROM bookings es indistinguible, como consulta, de la versión con filtro. La única forma de cazarlo es comparar su resultado contra el esperado —evaluación, M7—, o mejor prompting que induzca el filtro (M3).

3. Confundir el JOIN ON 1=1 con un error de sintaxis. Es SQL perfectamente válido —un JOIN sin condición real—. Compila, corre, y da un producto cartesiano. Su "error" es semántico, no sintáctico, y por eso ninguna capa de forma o esquema lo caza.

4. Olvidar prefijar columnas en JOINs. La ambigüedad (ambiguous column name: id) es un fallo real y común del SQL generado. Se caza en validación, pero se previene escribiendo (o promptear al modelo para que escriba) columnas calificadas (b.id) en cualquier consulta con más de una tabla.

Ejercicios

Ejercicio 1: Clasificar sin ejecutar (Fácil)

Para cada SQL, predice qué capa lo caza (forma, parseo/esquema, o ninguna) antes de correr classify. Luego confirma:

• (a) SELECT tier FROM members WHERE plan = 'pro'
• (b) SELECT AVG(price_cents) FROM bookings
• (c) DROP TABLE payments

for sql in ["SELECT tier FROM members WHERE plan = 'pro'",
            "SELECT AVG(price_cents) FROM bookings",
            "DROP TABLE payments"]:
    print(classify(sql, con), "<-", sql)

('PARSEO/ESQUEMA', 'OperationalError: no such column: plan') <- SELECT tier FROM members WHERE plan = 'pro'
('NINGUNA (corre)', '—') <- SELECT AVG(price_cents) FROM bookings
('FORMA', 'no es lectura (DROP)') <- DROP TABLE payments

Ejercicio 2: El JOIN ambiguo, corregido (Medio)

SELECT id, name FROM bookings b JOIN rooms r ON r.id = b.room_id falla por ambigüedad. Identifícala, corrígela calificando la columna para que traiga el id de la reserva y el name de la sala, y ejecútala con run_safely (o directo) mostrando las primeras filas.

# Falla: 'id' es ambiguo (existe en bookings y en rooms)
print(classify("SELECT id, name FROM bookings b JOIN rooms r ON r.id=b.room_id", con))

# Corregido: calificar id con el alias de la tabla que quieres
cur = con.execute("""
SELECT b.id AS booking_id, r.name AS room
FROM bookings b JOIN rooms r ON r.id = b.room_id
ORDER BY b.id LIMIT 3
""")
for row in cur.fetchall():
    print(row)

('PARSEO/ESQUEMA', 'OperationalError: ambiguous column name: id')
(1, 'Focus')
(2, 'Studio')
(3, 'Boardroom')

Ejercicio 3: Diseñar el par gold/generado (Difícil)

Para el fallo silencioso del filtro faltante, escribe el par que el Módulo 7 usaría para cazarlo: el SQL "gold" (correcto) y el SQL "generado" (con el bug), y una función que compare sus resultados y devuelva True si coinciden. Muéstrala reportando que no coinciden.

def same_result(gold_sql, gen_sql, con):
    gold = con.execute(gold_sql).fetchall()
    gen  = con.execute(gen_sql).fetchall()
    return gold == gen

gold = "SELECT SUM(price_cents) FROM bookings WHERE status='confirmed'"
gen  = "SELECT SUM(price_cents) FROM bookings"   # olvidó el filtro

print("gold:", con.execute(gold).fetchone()[0])
print("gen: ", con.execute(gen).fetchone()[0])
print("¿coinciden?:", same_result(gold, gen, con))

gold: 211900
gen:  241400
¿coinciden?: False

Resumen y siguiente paso

• Los modos de fallo del SQL generado caen en dos familias: los ruidosos (el motor los rechaza) y los silenciosos (corren y responden mal).
• Ruidosos, cazados por M4: sentencia peligrosa y dos sentencias (forma), sintaxis rota (parseo), columna alucinada / tabla equivocada / columna real en tabla equivocada / ambigüedad en JOIN (esquema), y el error que solo aparece al ejecutar (try/except).
• Silenciosos, NO cazados por M4: el filtro faltante (241400 en vez de 211900) y el JOIN semánticamente mal (115 en vez de 23). Corren perfecto y mienten.
• Cazar los silenciosos exige comparar el resultado contra una respuesta esperada (el SQL "gold"): eso es exactitud de ejecución, el Módulo 7. Validar que el SQL corra no es validar que responda bien.

Siguiente cápsula: Mini-proyecto — Juntas todo lo del módulo en un pipeline validate(sql) + run_safely(sql) que rechaza no-SELECT, caza una columna alucinada, ejecuta el válido con LIMIT, y simula el loop de auto-corrección de punta a punta, con salida real.

Recursos adicionales

1. SQLite — EXPLAIN — La compilación que caza sintaxis, columnas y tablas inexistentes, y ambigüedades.
2. SQLite — Query Language: SELECT (JOINs) — Por qué un JOIN ON 1=1 es válido y produce un producto cartesiano.
3. Spider: Yale Semantic Parsing and Text-to-SQL — El benchmark que cataloga los modos de fallo del SQL generado (JOIN equivocado, agregación equivocada, columna alucinada, filtro faltante).
4. BIRD: Big Bench for Large-Scale Database Grounded Text-to-SQL — Benchmark que mide la exactitud de ejecución, la métrica que caza los fallos silenciosos (Módulo 7).