MySQL Bugs: #116140: stored procedure is producing different results in different attempts

Bug #116140	stored procedure is producing different results in different attempts
Submitted:	18 Sep 10:28	Modified:	19 Sep 12:11
Reporter:	Zafar Malik	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cte, MySQL, query, stored procedure

Description:
When we are executing some CTE sql statement via stored procedure then first time it is giving different results and 2nd time onwards it is producing different results.

Note: When we are executing same CTE sql statement directly i.e. not via stored procedure, then it is producing same results each and every time.

How to repeat:
Step1: Copy below sql statements in a file like test.sql

--------------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS test;

DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS temp.cte_bug()
BEGIN

	WITH RECURSIVE 
	tran(batch_uid, guid, type, trantime, refguid) AS (
		SELECT 549285, 'W5PT9A5F3D2WF57', 13, '2023-04-07 09:19:04', NULL
		UNION ALL
		SELECT 549286, 'W5PTVYU84715VJ7', 13, '2023-04-07 09:31:20', 'W5PT9A5F3D2WF57'
	)
	,t(init_dataset, batch_uid, guid, type, trantime) AS
	(
	SELECT TRUE AS init_dataset,
		batch_uid,
		guid,
		type,
		trantime		
	FROM tran
	WHERE batch_uid = 549285
	UNION ALL
	SELECT FALSE,
		wt.batch_uid,
		wt.guid,
		wt.type,
		wt.trantime
	FROM tran wt
		JOIN t ts ON wt.refguid = ts.guid
	WHERE wt.batch_uid != 549285
		AND init_dataset
	)
	SELECT t2.count
	FROM t
		LEFT JOIN
			(
				SELECT type,
					COUNT(*) AS count
				FROM t t2
				WHERE batch_uid = 549285
				GROUP BY type
			) t2 ON t2.type = t.type
;
END $$
DELIMITER ;
--------------------------------------------------------------

Step2: Now import this file on mysql server (we tested it on mysql8.0.39, but you can on any which support CTE).

mysql test < test.sql

Step3: Connect mysql and call this SP multiple times-
mysql
mysql> CALL test.cte_bug;

Problem: First time it will providing results 1 but 2nd time onwards this is providing results as NULL value .

Below is the test case executed by us-

--------------------------------------------------------------
mysql> CREATE SCHEMA IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE IF NOT EXISTS test.cte_bug()
    -> BEGIN
    -> 
    -> WITH RECURSIVE 
    -> tran(batch_uid, guid, type, trantime, refguid) AS (
    -> SELECT 549285, 'W5PT9A5F3D2WF57', 13, '2023-04-07 09:19:04', NULL
    -> UNION ALL
    -> SELECT 549286, 'W5PTVYU84715VJ7', 13, '2023-04-07 09:31:20', 'W5PT9A5F3D2WF57'
    -> )
    -> ,t(init_dataset, batch_uid, guid, type, trantime) AS
    -> (
    -> SELECT TRUE AS init_dataset,
    -> batch_uid,
    -> guid,
    -> type,
    -> trantime
    -> FROM tran
    -> WHERE batch_uid = 549285
    -> UNION ALL
    -> SELECT FALSE,
    -> wt.batch_uid,
    -> wt.guid,
    -> wt.type,
    -> wt.trantime
    -> FROM tran wt
    -> JOIN t ts ON wt.refguid = ts.guid
    -> WHERE wt.batch_uid != 549285
    -> AND init_dataset
    -> )
    -> SELECT t2.count
    -> FROM t
    -> LEFT JOIN
    -> (
    -> SELECT type,
    -> COUNT(*) AS count
    -> FROM t t2
    -> WHERE batch_uid = 549285
    -> GROUP BY type
    -> ) t2 ON t2.type = t.type
    -> ;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL test.cte_bug;
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL test.cte_bug;
+-------+
| count |
+-------+
|  NULL |
|  NULL |
+-------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.0.39    |
+-----------+
1 row in set (0.00 sec)

mysql> 
--------------------------------------------------------------

Kindly change database name from temp to test in step1 in main code like below-

Change "CREATE PROCEDURE IF NOT EXISTS temp.cte_bug()" to "CREATE PROCEDURE IF NOT EXISTS test.cte_bug()"

HI Mr. Malik,

We have created a stored procedure and ran it on our official binaries for 8.0.39, 8.4.2 and 9.0.1.

We have always got very consistent results:

./bin/mysql -u***** -p***** test -e "call cte_bug"
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
./bin/mysql -u***** -p***** test -e "call cte_bug"
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
./bin/mysql -u***** -p***** test -e "call cte_bug"
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
./bin/mysql -u***** -p***** test -e "call cte_bug"
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
./bin/mysql -u***** -p***** test -e "call cte_bug"
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
./bin/mysql -u***** -p***** test -e "call cte_bug"
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
./bin/mysql -u***** -p***** test -e "call cte_bug"
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
./bin/mysql -u***** -p***** test -e "call cte_bug"
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+

We simply can not repeat your results.

We used our official binaries, downloadable from htttps://dev.mysql.com.

Can't repeat.

As I mentioned in my bug please first connect mysql and then call stored procedure from mysql prompt i.e. from same session.

Kindly follow the same steps as I mentioned in my bug report.

Hi Mr. Malik,

You are quite right.

However, our Manual includes many limitations, including usage of GROUP BY, which is prohibited :

https://dev.mysql.com/doc/refman/9.0/en/with.html#common-table-expressions-recursive

Thanks for this clarification and sharing the documentation.

My suggestion is here that if something is prohibited then mysql query execution plan should catch it and generate some error.

As I check it again then The recursive SELECT part does not contain any of the prohibited constructs. We use GROUP BY and COUNT(*) only when we refer to CTE alias outside of the CTE definition. Kindly investigate it again in right direction.

Hi Mr. Malik,

We already decided to verify this report as a true bug in the Optimiser ......

Verified as reported for version 8.0 and all higher supported versions.

Posted by developer:
 
The error can be with if the CTE query is put in a prepared statement also:

prepare p from "
WITH RECURSIVE 
        tran(batch_uid, guid, type, trantime, refguid) AS (
                SELECT 549285, 'W5PT9A5F3D2WF57', 13, '2023-04-07 09:19:04', NULL
                UNION ALL
                SELECT 549286, 'W5PTVYU84715VJ7', 13, '2023-04-07 09:31:20', 'W5PT9A5F3D2WF57'
        )
        ,t(init_dataset, batch_uid, guid, type, trantime) AS
        (
        SELECT TRUE AS init_dataset,
                batch_uid,
                guid,
                type,
                trantime                
        FROM tran
        WHERE batch_uid = 549285
        UNION ALL
        SELECT FALSE,
                wt.batch_uid,
                wt.guid,
                wt.type,
                wt.trantime
        FROM tran wt
                JOIN t ts ON wt.refguid = ts.guid
        WHERE wt.batch_uid != 549285
                AND init_dataset
        )
        SELECT t2.count
        FROM t
                LEFT JOIN
                        (
                                SELECT type,
                                        COUNT(*) AS count
                                FROM t t2
                                WHERE batch_uid = 549285
                                GROUP BY type
                        ) t2 ON t2.type = t.type
"; 

[test] $ execute p;
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
2 rows in set (0.01 sec)

[test] $ execute p;
+-------+
| count |
+-------+
|  NULL |
|  NULL |
+-------+
2 rows in set (0.00 sec)

Thank you , Dag.

May I get some clue about next course of action.

Hi Mr. Malik,

We follow our usual procedure .........

On one of the next meetings of the team in charge, they will set a priority of the bug. It is an internal info, not available to the public.

Then, since this is not a critical nor a Security Vulnerability bug, in some time, a team in charge will schedule the fix of the bug for certain verision(s) or release ......

That info will also NEVER be made public.

These schedules may then change frequently, depending on the arrival of critical or crashing bugs .........

This was the simplest explanation that we could provide. The full procedure is far more complicated.

Posted by developer:
 
There is something fishy with the index lookup on the second but the last line of the explain analyze.
A work-around is to change the (last in query) predicate "WHERE batch_uid = 549285"
to "WHERE batch_uid >= 549285 AND batch_uid < 549286":

[test] $ PREPARE p FROM "
    "> WITH RECURSIVE
    ">         tran(batch_uid, guid, type, trantime, refguid) AS (
    ">                 SELECT 549285, 'W5PT9A5F3D2WF57', 13, '2023-04-07 09:19:04', NULL
    ">                 UNION ALL
    ">                 SELECT 549286, 'W5PTVYU84715VJ7', 13, '2023-04-07 09:31:20', 'W5PT9A5F3D2WF57'
    ">         )
    ">         ,t(init_dataset, batch_uid, guid, type, trantime) AS
    ">         (
    ">         SELECT TRUE AS init_dataset,
    ">                 batch_uid,
    ">                 guid,
    ">                 type,
    ">                 trantime
    ">         FROM tran
    ">         WHERE batch_uid = 549285
    ">         UNION ALL
    ">         SELECT FALSE,
    ">                 wt.batch_uid,
    ">                 wt.guid,
    ">                 wt.type,
    ">                 wt.trantime
    ">         FROM tran wt
    ">                 JOIN t ts ON wt.refguid = ts.guid
    ">         WHERE wt.batch_uid != 549285
    ">                 AND init_dataset
    ">         )
    "> SELECT t2.count
    "> FROM t
    ">      LEFT JOIN
    ">      (
    ">              SELECT type,
    ">                      COUNT(*) AS count
    ">              FROM t t2
    ">              # WHERE batch_uid = 549285 
    ">              WHERE batch_uid >= 549285 AND batch_uid < 549286
    ">              GROUP BY type
    ">      ) t2 ON t2.type = t.type
    "> ";
Query OK, 0 rows affected (0.01 sec)
Statement prepared

[test] $ execute p;
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
2 rows in set (0.01 sec)

[test] $ execute p;
+-------+
| count |
+-------+
|     1 |
|     1 |
+-------+
2 rows in set (0.00 sec)

With EXPLAIN ANALYZE:
-> Nested loop left join  ( rows=0) (actual time=.. rows=2 loops=1)
    -> Table scan on t  ( rows=3) (actual time=.. rows=2 loops=1)
        -> Materialize recursive CTE t  ( rows=3) (actual time=.. rows=2 loops=1)
            -> Index lookup on tran using <auto_key0> (refguid = 549285)  ( rows=1) (actual time=.. rows=1 loops=1)
                -> Materialize union CTE tran if needed  ( rows=2) (actual time=.. rows=2 loops=1)
                    -> Rows fetched before execution  ( rows=1) (actual time=.. rows=1 loops=1)
                    -> Rows fetched before execution  ( rows=1) (actual time=.. rows=1 loops=1)
            -> Repeat until convergence
                -> Nested loop inner join  ( rows=1) (actual time=.. rows=0.5 loops=2)
                    -> Filter: ((0 <> ts.init_dataset) and (ts.guid is not null))  ( rows=1) (actual time=.. rows=0.5 loops=2)
                        -> Scan new records on ts  ( rows=2) (actual time=.. rows=1 loops=2)
                    -> Filter: (wt.batch_uid <> 549285)  ( rows=1) (actual time=.. rows=1 loops=1)
                        -> Index lookup on wt using <auto_key0> (refguid = ts.guid)  ( rows=2) (actual time=.. rows=1 loops=1)
                            -> Materialize union CTE tran if needed (query plan printed elsewhere)  ( rows=2) (never executed)
    -> Index lookup on t2 using <auto_key0> (type = t.`type`)  ( rows=2) (actual time=.. rows=1 loops=2)
        -> Materialize  ( rows=0) (actual time=.. rows=1 loops=1)
            -> Table scan on <temporary>  (actual time=.. rows=1 loops=1)
                -> Aggregate using temporary table  (actual time=.. rows=1 loops=1)
                    -> Filter: ((t2.batch_uid >= 549285) and (t2.batch_uid < 549286))  ( rows=1) (actual time=.. rows=1 loops=1)
                        -> Table scan on t2  ( rows=3) (actual time=.. rows=2 loops=1)
                            -> Materialize recursive CTE t  ( rows=3) (never executed)

Unfortunately, this is slower since we are no longer using the index on t2 (auto_key0)

Thank you, Dag.