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

[18 Sep 10:28] Zafar Malik
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> 
--------------------------------------------------------------
[18 Sep 10:35] Zafar Malik
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()"
[18 Sep 10:50] MySQL Verification Team
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.
[18 Sep 10:59] Zafar Malik
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.
[18 Sep 11:37] MySQL Verification Team
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
[18 Sep 11:55] Zafar Malik
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.
[18 Sep 12:10] Zafar Malik
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.
[18 Sep 12:36] MySQL Verification Team
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.
[19 Sep 10:57] Dag Wanvik
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)
[19 Sep 11:03] MySQL Verification Team
Thank you , Dag.
[19 Sep 12:11] Zafar Malik
May I get some clue about next course of action.
[19 Sep 12:17] MySQL Verification Team
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.
[19 Sep 12:33] Dag Wanvik
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)
[19 Sep 12:38] MySQL Verification Team
Thank you, Dag.