MySQL Bugs: #114995: by using 'with ....as ...inner join ...' bug

Bug #114995	by using 'with ....as ...inner join ...' bug
Submitted:	14 May 2024 10:00	Modified:	14 May 2024 12:46
Reporter:	haotian yan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.28 && 8.0.30	OS:	CentOS (7.9)
Assigned to:		CPU Architecture:	x86 (3.10.0-1160.el7.x86_64 #1 SMP Mon Oct 19 16:18:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux)

Description:
I am able to query the data when I execute the following sql statement in mysql, which uses statements such as 'with ... as' and '...union ... join' statements, but I found that once I switched the order of 'seletc * from A join B ....' in the clause.However, I found that once I switch the order of table A and table B in the clause 'seletc * from A join B ', the query is empty, I can guarantee that there is no change in the data content, and it is reproduced stably when I import the data from production to my local environment for testing, please help me to resolve this issue.

-- The following sql executes successfully and has data.
with monitorTwoHours AS (SELECT * FROM monitor2024050815 UNION SELECT * FROM monitor2024050816), 
monitorMaxTime AS(SELECT resource_id, MAX(timestamp) AS timestamp from monitorTwoHours  where monitor_type = 'ph' and region = 'regionTyJtBeijing'  GROUP BY resource_id) 
select * from monitorMaxTime JOIN monitorTwoHours ON (monitorTwoHours.resource_id = monitorMaxTime.resource_id and monitorTwoHours.timestamp = monitorMaxTime.timestamp);

-- The following sql executes successfully but has empty result.
with monitorTwoHours AS (SELECT * FROM monitor2024050815 UNION SELECT * FROM monitor2024050816), 
monitorMaxTime AS(SELECT resource_id, MAX(timestamp) AS timestamp from monitorTwoHours  where monitor_type = 'ph' and region = 'regionTyJtBeijing' GROUP BY resource_id) 
select * from monitorTwoHours JOIN monitorMaxTime ON (monitorTwoHours.resource_id = monitorMaxTime.resource_id and monitorTwoHours.timestamp = monitorMaxTime.timestamp);

How to repeat:
You just need to import the initialisation data I have given to mysql server (V8.0.28 or V8.0.30) and execute the two different sql statements I have given and you can repeat it.

Suggested fix:
I think both sql should output results and be the same result.

Hi Mr. yan,

You can upload your test case if you click on the "Files" tab.

We are waiting on your feedback.

Do note that current release of 8.0 is 8.0.37, so if we can not repeat your report with 8.0.37, then it will not be a bug. Hence, we recommend you to first test your test case with 8.0.37.

Hi Mr. yan,

We have got expected results with our 8.0.37 binary.

Both queries returned the same rows.

You will find our results in the "FIles" tab, under the name of results.txt.

There is a difference in the order of the columns presented, which is caused by a difference in the JOIN order. That is expected behaviour.

Can't repeat.

results from the both queries

Attachment: results.txt (text/plain), 20.49 KiB.

I'm very sorry, but I really can't find the test result file you provided.

Hi,

Try "Files" tab now .......

I have installed mysql version 8.0.37 and I found that I get the same result for two SQL statements, but I get different result as I said in mysql V8.0.28 or V8.0.30 version, so I would like to know if there is some optimisation in mysql V8.0.28 or V8.0.30.Finally thanks a lot for your patience!

Hi,

No, there is not due to any optimisation in the earlier releases.

This is just a bug that is already fixed.