ChatGPT, Copilot, and other AIs hallucinate and make stuff up.

I have been reading that AIs hallucinate.

So far my experience with ChatGPT and Copilot has been average. They know a lot of stuff and can help you get unstuck, but when they get confused, they make stuff up.

They perform poorly when you ask them questions about the UI in software tools. For example, looking for something in VScode. ChatGPT gave me bad directions. When I complained, it apologized, but it kept answering incorrectly, even making stuff up.

I ask a lot of SQL questions and I explictly mention the platform. Like “In Oracle SQL…” or “In TSQL…” or “Snowflake SQL…” Sometimes it produces code that doesn’t fit into the platform. When I complain about it, it does the same thing, apologizes and says “you are right, this function doesn’t work there”. When it corrects itself and produces a better answer, it’s all good. However, often it seems to get stuck in erroneous land.

There is no magic to ChatGPT. The system is as good or bad as who created it. My experience with AI and ML, and I am no expert, is that you need to feed a dataset to an algorithm. You input the dataset (or some of it) into the algorithm so that it “learns” to detect patterns. The algorithm takes the shape of some fancy keyword like Deep Learning or LLM or “whatever-fancy” learning algorithm. Then when it sees new data it hasn’t seen before it knows what to output.

The dataset that the algorithm is trained with is the key to all of this. As in the dog/cat dataset. Feed an algorithm a dataset of labeled dog images so that it learns to identify if a new image is a dog or or not a dog

Why would the AIs get confused with SQL questions?

Here is a simple query to ChatGPT 4o. What is supposed to be the latest and greatest model:

In PL/SQL write a CTE example that first selects columns from a table, in the second CTE then select all the columns from the first CTE, assume there are many of them, about 10 columns, plus combine two of the columns to create another one. For example

Table employee has these columns: first_name, last_name, employer, hire_date, salary, work_hours, bonus, birth_date, city, phone, state, address

The first CTE selects from this table all columns

The second CTE selects all the columns and adds a column to concatenate the first and last name

It goes ahead with a correct answer

WITH
    employee_data AS (
        SELECT
            first_name
            , last_name
            -- it selects the rest of the columns
        FROM
            employee
    ),

    employee_fullname AS (
        SELECT
            first_name
            , last_name
            -- it selects all the columns again
            , first_name || ' ' || last_name AS full_name
        FROM
            employee_data
    )
    -- continues

Then I reply back with this:

In the second CTE, why select all names again. That's wasteful. Why don't you use star and select the additional column

It apologizes and rewrites the second CTE like this:

employee_fullname AS (
    SELECT 
        *,
        first_name || ' ' || last_name AS full_name
    FROM 
        employee_data
)

I reply that this doesn’t work in Oracle. It doesn’t like when you select star and another column. ChatGPT replies:

You're correct that in Oracle SQL, you cannot use * together with additional columns in the SELECT statement when using Common Table Expressions (CTEs).

Then it goes back to the first answer. But I say that it could work if you prefix the star with the table name like:

SELECT
    employee_data.*
    , first_name || ' ' || last_name AS full_name

Then it says I am correct and changes the code to my answer.

What’s going on here? Why is ChatGPT correcting itself to a wrong answer and then to a right answer? Is it guessing?

My conclusion.

AIs do not hallucinate, they are the product of dataset preparation. If the dataset contains a giant corpus of SQL data without proper identification of which SQL codes are correct for which platform, then when it sees new data, regardless if you explicitly mention the platform, it won’t answer correctly, because it wasn’t trained that way.

As DJ Patil described in his paper “Data Scientist: The Sexiest Job of the 21st Century”. Data engineering is 80% of the work in an AI/ML project.

I’ve met many data scientists and machine learning engineers mention that most of their job is data preparation.