DeepSeek LLM excercises — SQL query analysis

I have always been not that good at hype. When everyone is on rush to somewhere, physically or mentally, I prefer to calm down, take a breath and look around. This strategy not necessarily always wins, but at least allows to save some energy and use some experience of other sooners.

Hype of today's topic is DeepSeek, China-made open source Large Language Model that has amused a lot of people, primarily for its speed and reasoning feature. When "thinking" on your prompt, it shows its "thoughts train" in special section of the interface – and by some reason this ability got high appreciation from people, as if they all were conducting technical interview with that LLM and need to understand its ability to rationalize questions. To me, spending time waiting for that reasoning to be provided on screen is something silly, as I just want to get answer, sooner the better. Is the answer is incorrect, I don't see a point to analyze model's reasoning, as it apparently lead to wrong answer; if the answer is correct, that is what I use it for – being not a LLM specialist, I don't care how the model was reasoning, I've got right answer and that's it.

In various videos people ask DeepSeek to write a game in Python, give a piece of advice for children's party or count 'r' in the word 'strawberry'. That is cool, but a little bit off side of my everyday's needs. I am a coder, so I need assistance in coding. That's why I went simple – I took some piece of SQL and asked DeepSeek to explain what is happening there.

I took three DeepSeek models: DeepSeek-Coder-V2-Lite-Instruct-Q8_0.gguf (15.56 GB), DeepSeek-R1-Distill-Llama-8B-Q8_0.gguf (7.95 GB) and Qwen2.5-14B-DeepSeek-R1-1M-Uncensored.Q8_0.gguf (15.7 GB).

Also I took two other models: qwen2.5-coder-14b-instruct-q4_0.gguf (8.52 GB) and qwen2.5-coder-32b-instruct-q4_0.gguf (18.64 GB).

As a reference I used answer from ChatGPT 4 in default "Great for everyday tasks" mode.

All the models were asked the same letter-to-letter question, and only one of them, namely qwen2.5-coder-32b-instruct-q4_0, provided correct answer.

So, the question was:

I have 2 t-sql queries which logically are the same but provide different results:

select id,
       case
           when p_id is null then 'Root'
           when id in (select distinct p_id from Tree) then 'Inner'
           else 'Leaf'
           end as type
from Tree;

select id,
       case
           when p_id is null then 'Root'
           when id not in (select distinct p_id from Tree) then 'Leaf'
           else 'Inner'
           end as type
from Tree

Query 1 provides correct output whereas query 2 not. Why is that?

If you, like me, not a SQL guru yet, you may give it a try for youself here.

And here comes fun part.

1. DeepSeek-Coder-V2-Lite-Instruct-Q8_0.gguf – this model thinks that both SQL queries are equal in logic and behavior with conclusion:

Both queries should theoretically produce the same output. However, if there are any inconsistencies in your database or a mistake in data insertion, it might lead to different results. Given that both queries are logically equivalent in this context, you should check for potential issues with your data (e.g., missing values, incorrect insertions) and ensure that the schema is correctly defined.

2. DeepSeek-R1-Distill-Llama-8B-Q8_0.gguf – I gave up to this model after 4+ hours of awaiting (out of interest) where it will come. However I did not have that much of patience :) In the latest parts of its output it kept jumping between "So...", "Wait, ..." and "Maybe...", having generated 33008 useless tokens.

If interested, here is full output from the model.

3. Qwen2.5-14B-DeepSeek-R1-1M-Uncensored.Q8_0.gguf – this time it all finished much faster, however conclusion was still incorrect:

The two queries are logically equivalent and should produce the same result. If they differ, it might be due to an oversight in the data or conditions.

4. Qwen2.5-coder-14b-instruct-q4_0.gguf – worked for not so long, conclusion is incorrect, deviated into some unrelated matter of distinguishing between different types of nodes:

Query 1 correctly implements the logic to distinguish between root, inner, and leaf nodes based on their relationship with other nodes.

Query 2, due to its conditional logic, fails to properly classify nodes that are both parents and have children as 'Inner' nodes, leading to incorrect results.

5. Qwen2.5-coder-32b-instruct-q4_0.gguf – the winner in this competition. Having allocated 32 GB in memory, pacing as slow as I can afford on my computer, still the model came out with the correct explanation of what is wrong with those two SQL queries:

The difference in the results of your two queries stems from how the CASE statement handles the conditions, particularly when it comes to the presence of NULL values in the subquery.

Why Query 2 Provides Incorrect Results:
The issue with Query 2 arises when there are NULL values in the subquery result (select distinct p_id from Tree). In SQL, comparisons involving NULL return UNKNOWN, not TRUE or FALSE. Therefore:

Solution:

To ensure that Query 2 works correctly, you should explicitly handle NULL values in the subquery:

select id,
    case
        when p_id is null then 'Root'
        when id not in (select distinct p_id from Tree where p_id is not null) then 'Leaf'
        else 'Inner'
    end as type
from Tree;

This modified version of Query 2 excludes NULL values from the subquery, ensuring that the NOT IN condition behaves as expected.

If your table contains any rows where p_id is NULL, those NULL values will cause the id NOT IN (subquery) condition to evaluate to UNKNOWN for all rows. As a result, no row will be classified as 'Leaf' based on this condition.

---
As I mentioned, ChatGPT's answer, which I used for reference, was almost in the same in words, and 100% the same in suggested fixed code for the 2nd query.

So, not everything the model tells is truth :)