[#MDEV-16868] Same query gives different results

I don't use the critical priority frivolously, but I have a query that always works on a mysql server, but only once at the first execution every 20 to 60 minutes on a MariaDB server. I am able to reproduce, that the query gives a different (wrong) result, if executed a few seconds after it gave the correct result. It also runs about 10 times slower on MariaDB than on mysql 5.7.
I am ready to give you more information than just the query, but the data is sensitive. I have a pseudonymized database dump (101 MB compressed) and a screen recording (87 MB) that I could send to the developers who want to look into the problem. The screen recording shows me executing the query twice with different results and a proof via query log and diff tool, that the queries are the same.
I filed this issue under "Data Definition - Temporary" because I did not find a component for issues with query results and had a vague feeling, that the temporary table has to do with it. However, I tried renaming it every time, but to no avail! Could it be the query cache?

I appended the query results with columns separated by tabs, since I could not display them cleanly with this editor.

DROP TEMPORARY TABLE IF EXISTS top5ads;

CREATE TEMPORARY TABLE top5ads (click_count INT(10) UNSIGNED, ad_id INT(10) UNSIGNED);

INSERT INTO top5ads (click_count, ad_id)

SELECT

	COUNT(*) AS click_count,

	ads.id AS ad_id

FROM

	employ__ad_clicks ad_clicks

	JOIN employ__ads ads ON ads.id = ad_clicks.ad_id

	JOIN employ__companies cmps ON cmps.id = ads.company_id

WHERE

	cmps.vdma_company_id > 0 AND

	ads.job_type_id IN (1,2,3,4,5,13,19) AND

	ad_clicks.host <> '' AND

	-- {job_type_id} AND

	click_date BETWEEN '2018-01-01' AND '2018-07-01'

GROUP BY

	ads.id

ORDER BY

	click_count DESC

LIMIT 5;

SELECT

	click_count,

	CONCAT

		TRIM(ads.title), ' | ',

			SELECT

				CONCAT(pcs.postal_code, ' ', ad_pcs.city)

			FROM

				employ__ads_postal_codes ad_pcs

				JOIN employ__postal_codes pcs ON pcs.id = ad_pcs.postal_code_id

			WHERE

				ad_pcs.ad_id = ads.id

			LIMIT 1

		), ' | ',

		click_count,

		'\n',

		cmps.name, ' ', cmps.second_name, ' | ', DATE_FORMAT(ads.created_at, '%d.%m.%Y'), ' | ',

		CASE WHEN

			ads.published AND

			CURRENT_DATE BETWEEN ads.publishes_at AND ads.ends_at

		THEN

			'online'

		ELSE

			'offline'

END

	) AS ad_title,

	ads.job_type_id,

	ads.permalink,

	mpgs_clicks_lists.mpgs_clicks_list,

	'Top' AS x_label

FROM

	top5ads

JOIN employ__ads ads ON ads.id = top5ads.ad_id

JOIN employ__companies cmps ON cmps.id = ads.company_id

JOIN

		SELECT

			adcs_ad_id,

			GROUP_CONCAT(mpg_name_click_count ORDER BY mpg_name_click_count SEPARATOR '\n') AS mpgs_clicks_list

		FROM

			SELECT

				adcs_ad_id,

				CONCAT(click_counts_mpgs.name, ': ', click_counts_mpgs.click_count_mpg) AS mpg_name_click_count

			FROM

				SELECT

					adcs.ad_id AS adcs_ad_id,

					COUNT(*) as click_count_mpg,

					mpgs.name

				FROM

					employ__ad_clicks adcs

					JOIN employ__marketplace_group_hosts mpg_hosts ON adcs.host = mpg_hosts.name

					JOIN employ__marketplace_groups mpgs ON mpgs.id = mpg_hosts.marketplace_group_id

				WHERE

					adcs.host <> '' AND

					adcs.click_date BETWEEN '2018-01-01' AND '2018-07-01'

				GROUP BY

					mpgs.name,

					adcs.ad_id

			) click_counts_mpgs

		) mpg_name_click_counts

		GROUP BY

			adcs_ad_id

		ORDER BY mpg_name_click_counts.adcs_ad_id

	mpgs_clicks_lists ON mpgs_clicks_lists.adcs_ad_id = ads.id;

[MDEV-16868] Same query gives different results Created: 2018-07-31 Updated: 2019-09-30 Resolved: 2018-08-24
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Definition - Temporary, Storage Engine - InnoDB
Affects Version/s:	10.2.9, 10.3.2, 10.2.16, 10.3.8
Fix Version/s:	10.2.18, 10.3.10

[MDEV-16868] Same query gives different results Created: 2018-07-31 Updated: 2019-09-30 Resolved: 2018-08-24