|
please add
show create table ApiResources;
|
show create table ApiScopes;
|
|
|
CREATE TABLE `apiscopes` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`ApiResourceId` int(11) NOT NULL,
`Description` varchar(1000) DEFAULT NULL,
`DisplayName` varchar(200) DEFAULT NULL,
`Emphasize` bit(1) NOT NULL,
`Name` varchar(200) NOT NULL,
`Required` bit(1) NOT NULL,
`ShowInDiscoveryDocument` bit(1) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_ApiScopes_Name` (`Name`),
KEY `IX_ApiScopes_ApiResourceId` (`ApiResourceId`),
CONSTRAINT `FK_ApiScopes_ApiResources_ApiResourceId` FOREIGN KEY (`ApiResourceId`) REFERENCES `apiresources` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
CREATE TABLE `apiresources` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Description` varchar(1000) DEFAULT NULL,
`DisplayName` varchar(200) DEFAULT NULL,
`Enabled` bit(1) NOT NULL,
`Name` varchar(200) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_ApiResources_Name` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
|
|
please describe what results did you get and provide mysqldump of this tables, if it is appropriate for you
|
|
Please find the dumps attached and 10.1 image shows the result getting and 10.2 shows the result expected.
|
|
Thank you for the report. Reproduced on MariaDB >=10.2.2
bug appeared after a95e384d54702 commit
As a workaround you can use straight_join
SELECT straight_join `api`.`Id`, `api`.`Description`, `api`.`DisplayName`, `api`.`Enabled`, `api`.`Name`
|
FROM `ApiResources` AS `api`
|
WHERE EXISTS (
|
SELECT 1
|
FROM `ApiScopes` AS `x`
|
WHERE `x`.`Name` IN ('openid', 'profile', 'ccx2api', 'ccx2lmapi') AND (`api`.`Id` = `x`.`ApiResourceId`))
|
ORDER BY `api`.`Id`
|
Test case:
CREATE TABLE `t1` (
|
`Id` int not null,
|
`Name` varchar(200) NOT NULL,
|
PRIMARY KEY (`Id`),
|
UNIQUE KEY (`Name`)
|
);
|
|
CREATE TABLE `t2` (
|
`Id` int not null,
|
`id_t2` int NOT NULL,
|
`Name` varchar(200) NOT NULL,
|
PRIMARY KEY (`Id`),
|
UNIQUE KEY (`Name`),
|
KEY (`id_t2`),
|
CONSTRAINT `F` FOREIGN KEY (`id_t2`) REFERENCES `t1` (`Id`) ON DELETE CASCADE);
|
|
INSERT INTO `t1` VALUES (1,'aa'),(2,'bb'),(3,'cc'),(4,'dd'),(5,'ee'),(6,'ff');
|
INSERT INTO `t2` VALUES (1,3,'gg'),(2,4,'hh'),(4,5,'jj'),(5,1,'kk'),(6,6,'ff'),(8,2,'bb'),(10,1,'aa');
|
|
SELECT `t1`.`Id`, `t1`.`Name` FROM `t1`
|
WHERE EXISTS (
|
SELECT 1 FROM `t2`
|
WHERE `t2`.`Name` IN ('aa', 'bb') AND (`t1`.`Id` = `t2`.`id_t2`))
|
ORDER BY `t1`.`Id`;
|
|
explain extended SELECT `t1`.`Id`, `t1`.`Name` FROM `t1`
|
WHERE EXISTS (
|
SELECT 1 FROM `t2`
|
WHERE `t2`.`Name` IN ('aa', 'bb') AND (`t1`.`Id` = `t2`.`id_t2`))
|
ORDER BY `t1`.`Id`;
|
|
drop table t2, t1;
|
Results in 10.1.26-MariaDB-debug
+----+------+
|
| Id | Name |
|
+----+------+
|
| 1 | aa |
|
| 2 | bb |
|
+----+------+
|
2 rows in set (0.00 sec)
|
|
+------+--------------+-------------+--------+---------------+---------+---------+---------------+------+----------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+--------+---------------+---------+---------+---------------+------+----------+---------------------------------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 2 | 100.00 | Using temporary; Using filesort |
|
| 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.id_t2 | 1 | 100.00 | |
|
| 2 | MATERIALIZED | t2 | range | Name,id_t2 | Name | 202 | NULL | 2 | 100.00 | Using index condition |
|
+------+--------------+-------------+--------+---------------+---------+---------+---------------+------+----------+---------------------------------+
|
3 rows in set, 2 warnings (0.04 sec)
|
|
Note (Code 1276): Field or reference 'test.t1.Id' of SELECT #2 was resolved in SELECT #1
|
Note (Code 1003): select `test`.`t1`.`Id` AS `Id`,`test`.`t1`.`Name` AS `Name` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`Id` = `test`.`t2`.`id_t2`) and (`test`.`t2`.`Name` in ('aa','bb'))) order by `test`.`t1`.`Id`
|
Query OK, 0 rows affected (0.22 sec)
|
Results in 10.2.7-MariaDB-debug
+----+------+
|
| Id | Name |
|
+----+------+
|
| 2 | bb |
|
| 2 | bb |
|
+----+------+
|
2 rows in set (0.00 sec)
|
|
+------+--------------+-------------+--------+---------------+---------+---------+---------------+------+----------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+--------+---------------+---------+---------+---------------+------+----------+-----------------------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 2 | 100.00 | Using filesort |
|
| 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.id_t2 | 1 | 100.00 | |
|
| 2 | MATERIALIZED | t2 | range | Name,id_t2 | Name | 202 | NULL | 2 | 100.00 | Using index condition |
|
+------+--------------+-------------+--------+---------------+---------+---------+---------------+------+----------+-----------------------+
|
3 rows in set, 2 warnings (0.00 sec)
|
|
Note (Code 1276): Field or reference 'test.t1.Id' of SELECT #2 was resolved in SELECT #1
|
Note (Code 1003): select `test`.`t1`.`Id` AS `Id`,`test`.`t1`.`Name` AS `Name` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t1`.`Id` = `test`.`t2`.`id_t2` and `test`.`t2`.`Name` in ('aa','bb') order by `test`.`t1`.`Id`
|
Query OK, 0 rows affected (0.24 sec)
|
|
|
i guess i have to downgrade as this query is from a dll i am using in my code, IdentityServer4.
hope to see a solution soon.
|
|
As a second workaround, try to turn off materialization:
set optimizer_switch='materialization=off';
|
|
|
Tried set optimizer_switch='materialization=off';
working Thanks
|
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 1.1786,
|
"read_sorted_file": {
|
"r_rows": 2,
|
"filesort": {
|
"sort_key": "t1.`Id`",
|
"r_loops": 1,
|
"r_total_time_ms": 0.1815,
|
"r_used_priority_queue": false,
|
"r_output_rows": 2,
|
"r_buffer_size": "240",
|
"table": {
|
"table_name": "<subquery2>",
|
"access_type": "ALL",
|
"possible_keys": ["distinct_key"],
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 2,
|
"r_total_time_ms": 0.2101,
|
"filtered": 100,
|
"r_filtered": 1,
|
"materialized": {
|
"unique": 1,
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "t2",
|
"access_type": "range",
|
"possible_keys": ["Name", "id_t2"],
|
"key": "Name",
|
"key_length": "202",
|
"used_key_parts": ["Name"],
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 2,
|
"r_total_time_ms": 0.4923,
|
"filtered": 100,
|
"r_filtered": 100,
|
"index_condition": "t2.`Name` in ('aa','bb')"
|
}
|
}
|
}
|
}
|
}
|
},
|
"table": {
|
"table_name": "t1",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["Id"],
|
"ref": ["test.t2.id_t2"],
|
"r_loops": 2,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0924,
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
}
|
|
|
Peculiar things about this query plan:
- first, we do the subquery materialization (this reads data from table t2)
- then, materialized subquery is used as a source data for filesort().
- filesort has "sort_key": "t1.`Id`". That is, sort criteria uses table t1.
This is valid since the query has
WHERE (api.Id = x.ApiResourceId)) -- this is in the subquery
|
...
|
ORDER BY api.Id
|
however, this is where the bug most likely is.
SJ-Materialization-scan strategy unpacks in-equality columns to the outer select' columns.
I mean, join execution does that but does this happen when the filesort() is employed?
The guess agrees with the observation that the error started to occur after commit a95e384d54702 (which made such query plans possible).
|
|
Sergei Petrunia, after fixing please check if initially reported problem and one from mdev-12959 are fixed
|
|
Fixed by MDEV-13994
|
10.1.png
ccx-id_apiresources.sql