Bug #109479 Inconsistent result set in outer query
Submitted: 24 Dec 2022 11:13 Modified: 27 Jan 2023 10:22
Reporter: Pranav Kelkar Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S7 (Test Cases)
Version:mysql Ver 8.0.31-0ubuntu0.22.04.1 for L OS:Ubuntu (Ubuntu 22.04)
Assigned to: MySQL Verification Team CPU Architecture:x86 (x86-64)
Tags: caching, inner, optimisation, outer, subquery

[24 Dec 2022 11:13] Pranav Kelkar 
Description:
It was observed that the outer query was failing to get full result set returned by inner query in some scenarios. There are no known steps to reproduce this observation, but this bug report is being raised when it occurred twice. 

The inner query (1) returns 18 rows. It is expected that the outer query (2) should return 18 rows given the nature of outer query. But under some non reproducible scenarios, the outer query returns only a subset of result set of inner query.

This problem got resolved with following actions:

1. Restarting the MySQL service `systemctl restart mysql`
2. Running the inner query with different outer query (3) and then running the original outer query (2)

Also attached below the query analysis dumps. It is interesting to note that query execution plan of inner query (1) matches with query execution plan of different outer query (3), but does not match at all with outer query(2) which is failing under some non reproducible scenarios.

How to repeat:
## 1 Inner Query

```
SELECT DISTINCT BASE_FILTER.parkingSpaceId FROM (select parkingSpaceId from parkingSpaceTBL where isActive = true AND isDeleted = false AND isPublished = true) AS BASE_FILTER INNER JOIN (
SELECT parkingSpaceId
FROM parkingSpaceTBL
WHERE parkingFacilityId IN
        (SELECT parkingFacilityId
        FROM parkingFacilityTBL
        WHERE locationId IN
                (SELECT locationId
                FROM locationTBL
                WHERE  MATCH(addressLine1,cityTown,stateProvince,locality,landmark,country,postalCode,googleGeocodeNeighborhood,googleGeocodeSubLocality2,googleGeocodeSubLocality1, googleGeocodeLocality, googleGeocodeAdministrativeAreaLevel4,googleGeocodeAdministrativeAreaLevel3, googleGeocodeAdministrativeAreaLevel2, googleGeocodeAdministrativeAreaLevel1,googleGeocodeFormattedAddress)
                AGAINST('pune')))) AS LOCATION_FILTER USING(parkingSpaceId)
```

```
+----------------+
| parkingSpaceId |
+----------------+
|             10 |
|             11 |
|             12 |
|              1 |
|              2 |
|             25 |
|             26 |
|             27 |
|             29 |
|             30 |
|              3 |
|              4 |
|              5 |
|              6 |
|              7 |
|              8 |
|              9 |
|             28 |
+----------------+
18 rows in set (0.00 sec)
```

```
-> Table scan on <temporary>  (cost=5.54..5.54 rows=0.2) (actual time=0.544..0.578 rows=18 loops=1)
-> Temporary table with deduplication  (cost=3.04..3.04 rows=0.2) (actual time=0.539..0.539 rows=18 loops=1)
-> Remove duplicate (parkingFacilityTBL, parkingSpaceTBL) rows using temporary table (weedout)  (cost=3.02 rows=0.2) (actual time=0.104..0.511 rows=18 loops=1)
-> Nested loop inner join  (cost=3.02 rows=0.2) (actual time=0.096..0.472 rows=18 loops=1)
-> Nested loop inner join  (cost=1.55 rows=4) (actual time=0.075..0.257 rows=18 loops=1)
-> Nested loop inner join  (cost=0.75 rows=2) (actual time=0.060..0.150 rows=10 loops=1)
-> Filter: (match locationTBL.addressLine1,locationTBL.cityTown,locationTBL.stateProvince,locationTBL.locality,locationTBL.landmark,locationTBL.country,locationTBL.postalCode,locationTBL.googleGeocodeNeighborhood,locationTBL.googleGeocodeSubLocality2,locationTBL.googleGeocodeSubLocality1,locationTBL.googleGeocodeLocality,locationTBL.googleGeocodeAdministrativeAreaLevel4,locationTBL.googleGeocodeAdministrativeAreaLevel3,locationTBL.googleGeocodeAdministrativeAreaLevel2,locationTBL.googleGeocodeAdministrativeAreaLevel1,locationTBL.googleGeocodeFormattedAddress against ('pune'))  (cost=0.35 rows=1) (actual time=0.041..0.080 rows=6 loops=1)
-> Full-text index search on locationTBL using addressLine1 (addressLine1='pune')  (cost=0.35 rows=1) (actual time=0.037..0.063 rows=6 loops=1)
-> Covering index lookup on parkingFacilityTBL using locationId (locationId=locationTBL.locationId)  (cost=0.40 rows=2) (actual time=0.005..0.007 rows=2 loops=6)
-> Covering index lookup on parkingSpaceTBL using parkingFacilityId (parkingFacilityId=parkingFacilityTBL.parkingFacilityId)  (cost=0.44 rows=3) (actual time=0.004..0.006 rows=2 loops=10)
-> Filter: ((parkingSpaceTBL.isPublished = true) and (parkingSpaceTBL.isDeleted = false) and (parkingSpaceTBL.isActive = true))  (cost=0.25 rows=0.05) (actual time=0.007..0.009 rows=1 loops=18)
-> Single-row index lookup on parkingSpaceTBL using PRIMARY (parkingSpaceId=parkingSpaceTBL.parkingSpaceId)  (cost=0.25 rows=1) (actual time=0.004..0.004 rows=1 loops=18)
```

## 2 Outer Query
```sql
explain analyze SELECT parkingSpaceId FROM parkingSpaceTBL WHERE parkingSpaceId IN (SELECT DISTINCT BASE_FILTER.parkingSpaceId FROM (select parkingSpaceId from parkingSpaceTBL where isActive = true AND isDeleted = false AND isPublished = true) AS BASE_FILTER INNER JOIN (
SELECT parkingSpaceId
FROM parkingSpaceTBL
WHERE parkingFacilityId IN
        (SELECT parkingFacilityId
        FROM parkingFacilityTBL
        WHERE locationId IN
                (SELECT locationId
                FROM locationTBL
                WHERE  MATCH(addressLine1,cityTown,stateProvince,locality,landmark,country,postalCode,googleGeocodeNeighborhood,googleGeocodeSubLocality2,googleGeocodeSubLocality1, googleGeocodeLocality, googleGeocodeAdministrativeAreaLevel4,googleGeocodeAdministrativeAreaLevel3, googleGeocodeAdministrativeAreaLevel2, googleGeocodeAdministrativeAreaLevel1,googleGeocodeFormattedAddress)
                AGAINST('example')))) AS LOCATION_FILTER USING(parkingSpaceId) )
```

```
+----------------+
| parkingSpaceId |
+----------------+
|              1 |
|              3 |
|              5 |
|              7 |
|              9 |
|             11 |
|             25 |
|             27 |
|             28 |
|             30 |
+----------------+
10 rows in set (0.00 sec)
```

```
-> Nested loop inner join  (cost=3.31 rows=0.1) (actual time=0.183..3.503 rows=10 loops=1)
-> Filter: ((parkingSpaceTBL.isPublished = true) and (parkingSpaceTBL.isDeleted = false) and (parkingSpaceTBL.isActive = true))  (cost=3.05 rows=1) (actual time=0.078..0.180 rows=30 loops=1)
-> Table scan on parkingSpaceTBL  (cost=3.05 rows=28) (actual time=0.072..0.109 rows=30 loops=1)
-> Limit: 1 row(s)  (cost=1.40 rows=0.1) (actual time=0.108..0.108 rows=0 loops=30)
-> Nested loop inner join  (cost=1.40 rows=0.1) (actual time=0.105..0.105 rows=0 loops=30)
-> Nested loop inner join  (cost=1.05 rows=1) (actual time=0.023..0.074 rows=4 loops=30)
-> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.017..0.050 rows=4 loops=30)
-> Filter: (match locationTBL.addressLine1,locationTBL.cityTown,locationTBL.stateProvince,locationTBL.locality,locationTBL.landmark,locationTBL.country,locationTBL.postalCode,locationTBL.googleGeocodeNeighborhood,locationTBL.googleGeocodeSubLocality2,locationTBL.googleGeocodeSubLocality1,locationTBL.googleGeocodeLocality,locationTBL.googleGeocodeAdministrativeAreaLevel4,locationTBL.googleGeocodeAdministrativeAreaLevel3,locationTBL.googleGeocodeAdministrativeAreaLevel2,locationTBL.googleGeocodeAdministrativeAreaLevel1,locationTBL.googleGeocodeFormattedAddress against ('example'))  (cost=0.35 rows=1) (actual time=0.009..0.025 rows=4 loops=30)
-> Full-text index search on locationTBL using addressLine1 (addressLine1='example')  (cost=0.35 rows=1) (actual time=0.007..0.018 rows=4 loops=30)
-> Single-row covering index lookup on parkingSpaceTBL using PRIMARY (parkingSpaceId=parkingSpaceTBL.parkingSpaceId)  (cost=0.35 rows=1) (actual time=0.002..0.002 rows=1 loops=122)
-> Single-row index lookup on parkingSpaceTBL using PRIMARY (parkingSpaceId=parkingSpaceTBL.parkingSpaceId)  (cost=0.35 rows=1) (actual time=0.002..0.002 rows=1 loops=122)
-> Filter: (parkingFacilityTBL.locationId = locationTBL.locationId)  (cost=0.26 rows=0.1) (actual time=0.005..0.005 rows=0 loops=122)
-> Single-row index lookup on parkingFacilityTBL using PRIMARY (parkingFacilityId=parkingSpaceTBL.parkingFacilityId)  (cost=0.26 rows=1) (actual time=0.002..0.002 rows=1 loops=122)
```

## 3 Different Outer Query (table alias)

### With Alias

```sql
SELECT * FROM  (SELECT DISTINCT BASE_FILTER.parkingSpaceId FROM (select parkingSpaceId from parkingSpaceTBL where isActive = true AND isDeleted = false AND isPublished = true) AS BASE_FILTER INNER JOIN (
	SELECT parkingSpaceId
	FROM parkingSpaceTBL
	WHERE parkingFacilityId IN
			(SELECT parkingFacilityId
			FROM parkingFacilityTBL
			WHERE locationId IN
					(SELECT locationId
					FROM locationTBL
					WHERE  MATCH(addressLine1,cityTown,stateProvince,locality,landmark,country,postalCode,googleGeocodeNeighborhood,googleGeocodeSubLocality2,googleGeocodeSubLocality1, googleGeocodeLocality, googleGeocodeAdministrativeAreaLevel4,googleGeocodeAdministrativeAreaLevel3, googleGeocodeAdministrativeAreaLevel2, googleGeocodeAdministrativeAreaLevel1,googleGeocodeFormattedAddress)
					AGAINST('example')))) AS LOCATION_FILTER USING(parkingSpaceId) ) AS ABCD;

```

```
+----------------+
| parkingSpaceId |
+----------------+
|             10 |
|             11 |
|             12 |
|              1 |
|              2 |
|             25 |
|             26 |
|             27 |
|             29 |
|             30 |
|              3 |
|              4 |
|              5 |
|              6 |
|              7 |
|              8 |
|              9 |
|             28 |
+----------------+
18 rows in set (0.00 sec)
```

### With Same Query

`1 row was inserted while computing following result, hence count shows 19`

```sql
explain analyze SELECT parkingSpaceId FROM parkingSpaceTBL WHERE parkingSpaceId IN (SELECT DISTINCT BASE_FILTER.parkingSpaceId FROM (select parkingSpaceId from parkingSpaceTBL where isActive = true AND isDeleted = false AND isPublished = true) AS BASE_FILTER INNER JOIN (
SELECT parkingSpaceId
FROM parkingSpaceTBL
WHERE parkingFacilityId IN
        (SELECT parkingFacilityId
        FROM parkingFacilityTBL
        WHERE locationId IN
                (SELECT locationId
                FROM locationTBL
                WHERE  MATCH(addressLine1,cityTown,stateProvince,locality,landmark,country,postalCode,googleGeocodeNeighborhood,googleGeocodeSubLocality2,googleGeocodeSubLocality1, googleGeocodeLocality, googleGeocodeAdministrativeAreaLevel4,googleGeocodeAdministrativeAreaLevel3, googleGeocodeAdministrativeAreaLevel2, googleGeocodeAdministrativeAreaLevel1,googleGeocodeFormattedAddress)
                AGAINST('example')))) AS LOCATION_FILTER USING(parkingSpaceId) )
```

```
+----------------+
| parkingSpaceId |
+----------------+
|             10 |
|             11 |
|             12 |
|              1 |
|              2 |
|             25 |
|             26 |
|             27 |
|             29 |
|             30 |
|             31 |
|              3 |
|              4 |
|              5 |
|              6 |
|              7 |
|              8 |
|              9 |
|             28 |
+----------------+
19 rows in set (0.00 sec)
```

```
explain analyze SELECT parkingSpaceId...
-> Remove duplicate (parkingFacilityTBL, parkingSpaceTBL) rows using temporary table (weedout)  (cost=3.70 rows=0.2) (actual time=0.110..0.651 rows=19 loops=1)
-> Nested loop inner join  (cost=3.70 rows=0.2) (actual time=0.101..0.609 rows=19 loops=1)
-> Nested loop inner join  (cost=2.56 rows=3) (actual time=0.084..0.395 rows=19 loops=1)
-> Nested loop inner join  (cost=1.41 rows=3) (actual time=0.071..0.253 rows=19 loops=1)
-> Nested loop inner join  (cost=0.74 rows=1) (actual time=0.056..0.141 rows=11 loops=1)
-> Filter: (match locationTBL.addressLine1,locationTBL.cityTown,locationTBL.stateProvince,locationTBL.locality,locationTBL.landmark,locationTBL.country,locationTBL.postalCode,locationTBL.googleGeocodeNeighborhood,locationTBL.googleGeocodeSubLocality2,locationTBL.googleGeocodeSubLocality1,locationTBL.googleGeocodeLocality,locationTBL.googleGeocodeAdministrativeAreaLevel4,locationTBL.googleGeocodeAdministrativeAreaLevel3,locationTBL.googleGeocodeAdministrativeAreaLevel2,locationTBL.googleGeocodeAdministrativeAreaLevel1,locationTBL.googleGeocodeFormattedAddress against ('example'))  (cost=0.35 rows=1) (actual time=0.037..0.070 rows=6 loops=1)
-> Full-text index search on locationTBL using addressLine1 (addressLine1='example')  (cost=0.35 rows=1) (actual time=0.034..0.058 rows=6 loops=1)
-> Covering index lookup on parkingFacilityTBL using locationId (locationId=locationTBL.locationId)  (cost=0.39 rows=1) (actual time=0.005..0.007 rows=2 loops=6)
-> Covering index lookup on parkingSpaceTBL using parkingFacilityId (parkingFacilityId=parkingFacilityTBL.parkingFacilityId)  (cost=0.42 rows=2) (actual time=0.004..0.006 rows=2 loops=11)
-> Single-row covering index lookup on parkingSpaceTBL using PRIMARY (parkingSpaceId=parkingSpaceTBL.parkingSpaceId)  (cost=0.28 rows=1) (actual time=0.003..0.004 rows=1 loops=19)
-> Filter: ((parkingSpaceTBL.isPublished = true) and (parkingSpaceTBL.isDeleted = false) and (parkingSpaceTBL.isActive = true))  (cost=0.25 rows=0.05) (actual time=0.006..0.008 rows=1 loops=19)
-> Single-row index lookup on parkingSpaceTBL using PRIMARY (parkingSpaceId=parkingSpaceTBL.parkingSpaceId)  (cost=0.25 rows=1) (actual time=0.003..0.004 rows=1 loops=19)
```

Suggested fix:
It is suspected that this inconsistency is due to some kind of caching being performed. Restarting mysql or running the inner query with different outer query must have invalidated the cache, hence we got the temporary resolution.
[27 Dec 2022 10:22] MySQL Verification Team 
Hi,

Trying to reproduce this when you can't reproduce it easily is not going to happen without dataset you manage to reproduce this with.

Can you provide us with the dataset and queries on top of that dataset that you see this issue happen? On top of that I'd like your full mysqld configuration. That way I can setup automated test to run for hours/days on top of regular load on the server to see if I can reproduce it. 

With the data provided and you not being able to reproduce the problem there's really not much I can do so if you can extract and sanitize the data from affected tables that would bring us much closer to figuring out what is going on.

Thanks
[28 Jan 2023 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".