Alice Sherepa added a comment - 2017-07-27 12:20 please add show create table ApiResources; show create table ApiScopes;

Ajay Kumar added a comment - 2017-07-28 08:16 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

Alice Sherepa added a comment - 2017-07-28 10:31 please describe what results did you get and provide mysqldump of this tables, if it is appropriate for you

Ajay Kumar added a comment - 2017-07-28 10:53 Please find the dumps attached and 10.1 image shows the result getting and 10.2 shows the result expected.

Alice Sherepa added a comment - 2017-07-28 13:00 - edited 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)

Ajay Kumar added a comment - 2017-07-28 13:03 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.

Alice Sherepa added a comment - 2017-07-28 18:21 As a second workaround, try to turn off materialization: set optimizer_switch= 'materialization=off' ;

Ajay Kumar added a comment - 2017-07-31 06:49 - edited Tried set optimizer_switch='materialization=off'; working Thanks

Sergei Petrunia added a comment - 2017-08-01 11:50 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 } } }

Sergei Petrunia added a comment - 2017-08-01 12:11 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).

Alice Sherepa added a comment - 2017-09-14 11:41 Sergei Petrunia, after fixing please check if initially reported problem and one from mdev-12959 are fixed

Varun Gupta (Inactive) added a comment - 2020-07-09 05:57 Fixed by MDEV-13994