Recursive query unexpectedly returning a blank value for one field

Question

I have the following tables:

persons:
- personid: int not null
- personname: varchar(200) not null

parents:
- parentid: int not null
- personid: int not null

One person may have children, in which case there are rows in the parents table, where each row identifies a parent/child relationship.

I now have the following recursive query:

        WITH RECURSIVE cte (personid, parentid) as (
            -- Anchor query
            SELECT
                personid AS personid,
                NULL AS parentid
            FROM
                persons
            WHERE
                personid = ?
            UNION ALL
            -- Recursive query
            SELECT
                p.personid,
                p.parentid
            FROM
                parents p
            INNER JOIN
                cte ON p.parentid = cte.personid
        )
        SELECT
            parentid,
            personid
        FROM
            cte

The anchor row is given the ID of the person to start with. I want this query to select that person as well as all his children, recursively.

But it doesn't work: the query returns the expected number of rows, however the parentid field is always blank (!), except for the first row where the value is NULL (which is what I want). The value of the personid column seems correct for each row.

How is this possible that the value of parentid be empty? What am I doing wrong?

I'm using MariaDB 10.5.19. MySQL 8.0.27 exhibits the same behavior.

Answer 1

It seems the problem is in the first part of your recursive query:

        SELECT
            personid AS personid,
            NULL AS parentid
        ...

The data type for parentid is determined by this. The type becomes NULL instead of the required INT. In a UNION, a column may have only one name and one data type, which apply to all subsequent rows appended by other queries in the UNION.

When the second part of the recursive query tries to put integers into that column, it fails.

When I tested the query given I run in strict mode, I get this error:

ERROR 1406 (22001): Data too long for column 'parentid' at row 1

If one were to run the query without strict mode, the value is truncated to some zero-ish value. I tested in MySQL 8.0 and got 0x in the result.

MariaDB may have different behavior. We should all stop thinking of MariaDB and MySQL as compatible products. They have an increasing number of incompatible behaviors. They are effectively totally different products by now.

The solution is to make sure the base case defines that column with the right data type:

        SELECT
            personid AS personid,
            CAST(NULL AS UNSIGNED) AS parentid
        ...