Our AI Query Planner Hallucinated a Cartesian Join

Our AI Query Planner Hallucinated a Cartesian Join

The promise of artificial intelligence in database management is seductive. We envision a world where complex queries are optimized in milliseconds, where natural language questions are seamlessly translated into efficient SQL, and where performance bottlenecks are predicted and fixed before they ever impact users. We were living that dream, leveraging a cutting-edge large language model to power our query optimization. Then, the dream turned into a minor nightmare. It was a stark reminder of the challenges we face when our AI query planner hallucinated a Cartesian join, a rookie mistake that sent our performance plummeting and our CPU usage into the stratosphere.

This isn't just a war story; it's a crucial lesson in the practical application of AI in high-stakes environments. This article breaks down how this AI hallucination happened, the catastrophic impact of a seemingly simple error, and the essential guardrails you must implement to prevent your own AI-driven systems from making similar, costly mistakes.

The Allure of the AI Query Planner: Promise vs. Reality

For decades, database systems have relied on cost-based optimizers (CBOs). These complex algorithms analyze dozens of factors—table statistics, indexes, data distribution—to estimate the "cost" of various execution plans and choose the cheapest one. They are marvels of engineering but can struggle with extremely complex queries or rapidly changing data patterns.

Enter the AI query planner. By using LLMs trained on vast datasets of code and database schemas, these next-generation optimizers promise to:

• Understand Intent: Translate ambiguous, human-language requests into precise, optimized SQL.
• Learn and Adapt: Continuously improve by observing query performance and data changes over time.
• Discover Novel Plans: Identify optimization strategies that rule-based CBOs might never consider.

Our goal was to empower our business analysts. We wanted them to ask questions like, "Show me the total sales for our top 10 customers in Q4," and have the AI generate a flawless, high-performance execution plan to fetch that data from our complex data warehouse. For weeks, it worked beautifully. Until it didn't.

When the AI Went Rogue: The Cartesian Join Catastrophe

The first sign of trouble was an alert. A query that typically completed in under five seconds was still running after ten minutes, consuming an alarming amount of system resources. At first, we suspected a data skew or a network issue. But a quick look at the query execution plan revealed a far more fundamental problem, a mistake so basic it was almost unbelievable.

The AI had generated a query that included a Cartesian join (or cross join). In the relational database world, this is a cardinal sin. A Cartesian join matches every single row from the first table with every single row from the second table. If you have a customers table with 100,000 rows and an orders table with 1 million rows, the resulting intermediate dataset isn't one million; it's 100 billion rows. This "join explosion" is a guaranteed way to bring a database to its knees.

Diagnosing the Hallucination

The root of the problem was a single missing line in the generated SQL. The AI failed to include the ON clause that specifies how the tables are related.

Here is a simplified example of what the AI produced versus what it should have produced:

-- AI Generated Plan (Incorrect and Dangerous)
SELECT
  c.customer_name,
  o.order_date,
  o.order_total
FROM
  customers c,
  orders o -- Missing the critical join condition!
WHERE
  c.signup_date > '2023-01-01';

-- Correct and Efficient Plan
SELECT
  c.customer_name,
  o.order_date,
  o.order_total
FROM
  customers c
JOIN
  orders o ON c.customer_id = o.customer_id -- The essential relationship
WHERE
  c.signup_date > '2023-01-01';

This wasn't a simple syntax error. It was a complete conceptual failure. The model understood the tables and the columns requested, but it failed to grasp the most critical piece of information: their semantic relationship. In effect, the AI query planner hallucinated a Cartesian join because it couldn't connect the dots between customers.customer_id and orders.customer_id.

The Root Cause: Why Did the AI Hallucinate?

The term "AI hallucination" perfectly describes this phenomenon. The LLM isn't being malicious; it's a statistical model generating what it calculates to be the most probable sequence of tokens based on its training. In our case, the probable output was disastrously wrong. We traced the failure back to a few key factors:

1. Schema Ambiguity: While our primary and foreign keys were well-defined, the training data fed to the model may not have sufficiently emphasized these relationships for this specific query pattern. The model saw two tables and a WHERE clause and statistically assembled them without enforcing the relational constraint.
2. Over-reliance on Syntax, Not Semantics: LLMs are masters of syntax. They can write beautiful, syntactically perfect SQL. However, true query optimization requires a deep semantic understanding of the underlying data model. The AI knew how to write a join but failed to understand why a specific join condition was non-negotiable.
3. The "Lost in Translation" Problem: The initial natural language prompt from the analyst, while clear to a human, may have contained a subtle ambiguity that led the model down an incorrect probabilistic path. Without explicit instructions on how to join, it defaulted to an invalid plan.

This incident with our LLM-based query planner served as a powerful reminder that these systems operate on probability, not true comprehension.

Mitigating AI Hallucinations in Database Operations

Abandoning AI is not the answer. The technology is too powerful. Instead, the solution is to build a robust system of checks and balances. You cannot blindly trust the output of an AI query optimizer.

Fortifying the Training Process

Prevention starts with better data. We are now focusing on schema-aware fine-tuning, a process where we explicitly bake knowledge of our database's structure into the model's training regimen. This involves generating training examples that repeatedly reinforce the importance of primary and foreign key relationships, ensuring the model learns that joining customers and orders always requires linking their respective ID columns.

Implementing Guardrails and Verification

The most critical defense is a verification layer that acts as a sanity check before any AI-generated query is executed. This is our new, non-negotiable workflow:

• Static Code Analysis: Before execution, a simple script scans the generated SQL for common anti-patterns, with "joins without an ON or USING clause" at the top of the list.
• Cost Estimation Sanity Check: The AI-generated plan is passed to the traditional cost-based optimizer for a cost estimate. If the CBO predicts an astronomical row count or execution time (as it would for a Cartesian join), the query is immediately flagged for human review.
• Strict Timeouts: We've implemented aggressive query timeouts. No ad-hoc query should run for more than a minute. This acts as a final backstop to kill any runaway process before it can impact the entire system.
• Continuous Auditing: We log every query generated by the AI, along with its performance metrics. This feedback loop is invaluable for identifying recurring issues and further refining the model.

Conclusion: Embracing AI with Cautious Optimism

The day our AI query planner hallucinated a Cartesian join was a stressful but incredibly valuable learning experience. It shattered the illusion of AI as an infallible black box and reinforced the importance of human oversight and well-designed safety nets.

AI-driven database tools hold immense potential to unlock new levels of performance and accessibility. But as we integrate these powerful models into our critical infrastructure, we must treat them as brilliant but sometimes naive assistants, not as omniscient oracles. By combining the creative power of AI with rigorous, rule-based verification, we can harness its benefits while protecting our systems from its inevitable flaws. The future of data management is not about choosing between human and machine; it's about building a partnership where each covers the other's weaknesses.

Are you experimenting with AI in your data stack? Share your own stories of unexpected behavior or clever solutions in the comments below. Let's learn from each other as we navigate this exciting new frontier.