| Bug #112373 | Incorrect result when using INNER JOIN with SELECT FROM VIEW | ||
|---|---|---|---|
| Submitted: | 18 Sep 2023 8:52 | Modified: | 20 Sep 2023 10:27 |
| Reporter: | IVAN HO | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | INNER JOIN, VIEW | ||
[19 Sep 2023 11:04]
MySQL Verification Team
Hi Mr. HO, Thank you for your bug report. However, we need to have a fully repeatable test case in order to reproduce the behaviour. We do not see anywhere the file that you mention. Also, do note that views are not expected to return the same result set as the original table, unless view has "SELECT * FROM table" in its definition. Hence, we need all data. CREATE TABLE(s), VIEW(s), all the table dump data and used queries. We can not proceed to any analysing of your report, unless we have all the above. Also, we. now discovered that this report is a duplicate of the following report: https://bugs.mysql.com/bug.php?id=112159 Please, do not create two bug reports for the same problem. If you do not think that this is the same problem as in the above report, then check whether your report is a duplicate of : https://bugs.mysql.com/bug.php?id=112161
[19 Sep 2023 15:30]
IVAN HO
test case
Attachment: testcase.sql (application/octet-stream, text), 2.17 KiB.
[19 Sep 2023 15:30]
IVAN HO
test case data
Attachment: testcase_loaddata.sql (application/octet-stream, text), 50.63 KiB.
[19 Sep 2023 15:31]
IVAN HO
I forgot to upload the files before. I've just uploaded the test case file.
[20 Sep 2023 10:27]
MySQL Verification Team
Hi Mr. HO, We have managed to repeat your test case. We changed some attributers of the view, to all possible values and variants, we changed the contents of the table, but we always got different results from the two queries. Table T9 and view v9 have identical definitions and data. So, with original data, this is the output from table and view are vastly different: The output from the query done with the table T9: ----------- ----------- C01 C04 C08 C09 C11 C10 5000044 600139 8001 9002 3 7202 5000044 600139 8001 9002 1 7152 5000044 600139 8001 9002 1 7151 5000044 600139 8002 9003 1 7119 5000044 600139 8002 9003 1 7130 5000044 600139 8002 9003 1 7131 5000044 600139 8002 9004 1 7202 5000044 600139 8002 9004 1 7131 5000044 600139 8002 9004 1 7135 5000044 600139 8002 9005 1 7116 5000044 600139 8003 9006 1 7119 5000044 600139 8003 9007 1 7202 5000044 600139 8003 9007 1 7118 5000044 600139 8003 9007 1 7126 5000044 600139 8008 9008 1 7127 5000044 600139 8009 9009 1 7146 5000044 600139 8024 9010 1 7142 5000044 600139 8025 9010 1 7142 5000044 600139 8029 9010 1 7142 5000044 600139 8024 9011 1 7127 5000044 600139 8024 9012 1 7137 5000044 600139 8024 9012 1 7140 5000044 600139 8024 9012 1 7127 5000044 600139 8024 9012 1 7126 5000044 600139 8024 9012 1 7125 5000044 600139 8025 9013 1 7123 5000044 600139 8025 9014 1 7123 5000044 600139 8025 9014 1 7155 5000044 600139 8026 9015 1 7118 5000044 600139 8026 9016 1 7118 5000044 600139 8026 9017 1 7118 5000044 600139 8029 9018 1 7134 5000044 600139 8029 9018 1 7141 5000044 600139 8029 9018 1 7133 5000044 600139 8029 9018 1 7176 5000044 600139 8029 9018 1 7128 5000044 600139 8029 9018 1 7143 5000044 600139 8029 9018 1 7135 5000044 600139 8029 9018 1 7144 5000044 600139 8029 9018 1 7142 5000044 600139 8029 9018 1 7175 5000044 600139 8029 9018 1 7202 5000044 600139 8029 9018 1 7122 5000044 600139 8029 9018 1 7129 5000044 600139 8029 9018 1 7139 5000044 600139 8029 9018 1 7126 5000044 600139 8029 9018 1 7145 5000044 600139 8029 9018 1 7124 5000044 600139 8029 9019 1 7137 5000044 600139 8029 9019 1 7123 5000044 600139 8029 9019 1 7128 5000044 600139 8029 9019 3 7143 5000044 600139 8029 9019 1 7135 5000044 600139 8029 9019 1 7122 5000044 600139 8029 9019 1 7144 5000044 600139 8029 9019 1 7120 5000044 600139 8029 9019 1 7138 5000044 600139 8029 9019 1 7143 5000044 600139 8029 9019 1 7176 5000044 600139 8029 9019 1 7126 5000044 600139 8029 9019 1 7124 5000044 600139 8029 9019 1 7141 5000044 600139 8029 9019 1 7129 5000044 600139 8029 9019 1 7139 5000044 600139 8029 9019 1 7175 5000044 600139 8029 9019 1 7202 5000044 600139 8029 9020 1 7145 5000044 600139 8029 9020 1 7176 5000044 600139 8029 9021 1 7142 5000044 600139 8029 9021 1 7175 5000044 600139 8029 9021 1 7176 5000044 600139 8043 9022 1 7118 5000044 600139 8053 9023 1 7130 5000044 600139 8063 9024 1 7155 5000044 600139 8063 9024 1 7156 5000044 600139 8065 9025 1 7202 5000044 600139 8064 9026 3 7119 5000044 600139 8064 9027 3 7126 5000044 600139 8064 9027 3 7136 5000044 600139 8064 9028 3 7120 5000044 600139 8064 9029 3 7166 5000044 600139 8064 9030 3 7122 5000044 600139 8064 9030 3 7135 5000044 600139 8064 9030 3 7126 5000044 600139 8064 9030 3 7159 5000044 600139 8064 9031 3 7121 The output from the query with a view v9: C01 C04 C08 C09 C11 C10 5000044 600139 8002 9004 1 7135 5000044 600139 8029 9018 1 7135 5000044 600139 8029 9019 1 7135 5000044 600139 8064 9030 3 7135 Hence, this is now a fully verified bug. This bug affects both 8.0 and 8.1.
Description: - SQL1 uses view in subquery - SQL2 uses physical table in subquery - SQL1 & SQL2 return different results - SQL1 returns 86 rows, SQL2 returns 4 rows. - Testing environment is static, there is no data changes. # SQL-1 [ SELECT FROM VIEW v9 in subquery ] # Result => Wrong SELECT DISTINCT ugp.C01, ugm.C04, staff.C08, staff.C09, staff.C11, staff.C10 FROM T1 ugp INNER JOIN T2 ugm ON ( ugm.C02 <= CURRENT_DATE () AND IFNULL( ugm.C03, '3000-01-01' ) >= CURRENT_DATE () AND ugm.C01 = ugp.C01 ) INNER JOIN T3 cugd ON ( cugd.C04 = ugm.C04 AND cugd.C06 = 'M01' AND cugd.C07 IN ('1', 'Y')) INNER JOIN T4 staff ON ( ( staff.C08 = cugd.C08 OR cugd.C08 IS NULL ) AND ( staff.C09 = cugd.C09 OR cugd.C09 IS NULL ) AND ( staff.C10 IN ( SELECT C10 FROM v9 WHERE C10_HIERACHY LIKE CONCAT('%#', cugd.C10, '#%')) or (cugd.C10 IS NULL) ) AND ( staff.C11 = cugd.C05 OR cugd.C05 IS NULL )) AND ugp.C01 = 5000044 ; # SQL-2 [ SELECT FROM TABLE T9 in subquery ] # Result => Correct SELECT DISTINCT ugp.C01, ugm.C04, staff.C08, staff.C09, staff.C11, staff.C10 FROM T1 ugp INNER JOIN T2 ugm ON ( ugm.C02 <= CURRENT_DATE () AND IFNULL( ugm.C03, '3000-01-01' ) >= CURRENT_DATE () AND ugm.C01 = ugp.C01 ) INNER JOIN T3 cugd ON ( cugd.C04 = ugm.C04 AND cugd.C06 = 'M01' AND cugd.C07 IN ('1', 'Y')) INNER JOIN T4 staff ON ( ( staff.C08 = cugd.C08 OR cugd.C08 IS NULL ) AND ( staff.C09 = cugd.C09 OR cugd.C09 IS NULL ) AND ( staff.C10 IN ( SELECT C10 FROM T9 WHERE C10_HIERACHY LIKE CONCAT('%#', cugd.C10, '#%')) or (cugd.C10 IS NULL) ) AND ( staff.C11 = cugd.C05 OR cugd.C05 IS NULL )) AND ugp.C01 = 5000044 ; How to repeat: Place 'testcase_loaddata.sql' in /tmp and run testcase.sql.