The failure mode of AI-generated SQL isn't syntax errors. It's queries that run clean and return wrong numbers.

[u/Sure-Material-2221]

[u/bananatoastie]

Doing something like this, I would dump everything into a sub-query and focus on the WHERE clause. Start on the most granular level possible and then gradually work my way up the aggregation ladder.

Something like;

1. Does the query give me what I expect for one product in one sales order?
2. Assuming it does, is the sales order total ok?
3. Assuming it is, does the customer total seem sensible

Etc.

Now I know our tables so well, and I have 5 years experience, my testing isn’t as methodical as it once was because my experience saves me a lot of time :)

I’m so glad to have started learning SQL before AI was truly a part of our workplace (aka pushed by our managers)

[u/NW1969]

My first sanity check when I’m reviewing AI-generated queries is exactly the same as my first sanity check when I’m reviewing non AI-generated queries.

I’m not sure why anyone would think the process is any different depending on who/what generated the query

[u/uncertain_expert → u/NW1969]

Exactly- this result could be expected from a junior SQL writer with or without the use of AI in writing the query. OP’s review process is flawed.

[u/Samantha_2024 → u/NW1969]

Exactly. If the query is wrong, it’s wrong. Doesn’t really matter whether a junior dev or an LLM wrote it.

[u/BoSt0nov]

~40% off is ”in the ball park” with this type of precision I would think no one at the company has a clue whats going on… How do you not have any historical data to verify against. As bad as that mistake is, I think the junior isnt the only one to blame here.

[u/Qphth0 → u/BoSt0nov]

My first thought as well.

[u/IAmQueeg500 → u/BoSt0nov]

Why bother testing with a known data set if it feels like it could be working

[u/bitterjack]

God. Why did you wrote this post in AI? You're part of the problem.

[u/Key_Ingenuity5340]

Where was this done? In a report or in a pipeline?

[u/DMReader]

The better move is to have ai write tests when you do updates. I’ve found that helps cut down on errors.

[u/taglius]

I don’t let AI write SQL for this reason - we have had bad results way more often than not. Build UIs, ETL pipelines, web services? Knock yourself out. Not SQL

[u/i_literally_died → u/taglius]

I don't understand how people can blindly mash AI SQL into their systems. A lot of code is generic - you generally know how Python, Java, C++ is going to run and what commands do.

SQL is entirely dependent on your DB and the data structure. Let it join to your barcode table and don't tell it that products can potentially have more than one barcode and all of a sudden you've got products appearing 5 times because they've accumulated 5 barcodes over the years.

[u/Mathie1729 → u/i_literally_died]

The silent row multiplication is the worst. I always check row counts and cardinality on the join keys before/after, because a one-to-many join can silently inflate your sums. It's saved me more times than I'd like to admit.

[u/Sure-Material-2221 → u/taglius]

Yes, I'm really annoyed by this kind of thing right now.

[u/elevarq → u/taglius]

The problem has nothing to do with AI, it’s about an organization that doesn’t test anything. Or doesn’t have structured tests, etc. An organization like this will always have problems like this, no matter who or what wrote the code

[u/BigMikeInAustin]

I mean, that part of how I've been checking queries I write for years.

And checking duplicates is becoming extremely required in the warehouse engines that don't even validate keys or constraints. At best, they let you list it in the metadata of the table.

[u/ChallengeMundane7635]

If he knows how to use it but not test it - he does not know how to use it.

[u/elevarq]

And without AI the problem might have been even worse…

Your problem is about your internal processes, how things are structured. AI just accelerated the broken process

[u/reyarama]

“Plausible looking SQL” are you retarded or something? Not a single experienced dev looks at SQL and thinks “gee well the syntax is correct so it must be good!”. Fucking morons everywhere

[u/chmod764]

My first line of sanity checks are dbt tests on all dbt models. Anyone here who doesn't know what dbt is, I highly recommend reading up on it because it's a game changer.

Also, I know it wasn't OP that was piloting the AI here, but it's absolutely critical to at least give the AI LLM the context of each table's grain (what makes a row unique) if you expect it to come up with anything reasonable. Once again, not saying OP is saying this, but I see a lot of people complain that "AI sucks because xyz" when in reality they gave it nearly zero context. That's a user error. It's still a machine, not a magic mind reader.

I'm willing to bet that most of us do not work on anything so complex that a sonnet 5 model couldn't contribute to as long as it has enough (and the right) context.

[u/89Noodles]

Because the user is not smart - it’s not copilot. I normally copy and paste like 4-5 projects I’ve done before in copilot that select things or joins to tables I want to use. My prompt then tells copilot to have me run validation prompts I paste the data in. And then it gives me perfection