Merge remote-tracking branch '10.0' into 10.1

1 files changed, 207 insertions, 0 deletions

diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index e421517aa21..5fdcb56de54 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1950,6 +1950,213 @@ set optimizer_switch= @save_optimizer_switch;
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # mdev-12838: scan of materialized of semi-join subquery in join
+--echo #
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+CREATE TABLE t1 (
+  dispatch_group varchar(32),
+  assignment_group varchar(32),
+  sys_id char(32),
+  PRIMARY KEY (sys_id),
+  KEY idx1 (dispatch_group),
+  KEY idx2 (assignment_group)
+) ENGINE=MyISAM;
+
+CREATE TABLE t2 (
+  ugroup varchar(32),
+  user varchar(32),
+  sys_id char(32),
+  PRIMARY KEY (sys_id),
+  KEY idx3 (ugroup),
+  KEY idx4 (user)
+) ENGINE=MyISAM;
+
+CREATE TABLE t3 (
+  type mediumtext,
+  sys_id char(32),
+  PRIMARY KEY (sys_id)
+) ENGINE=MyISAM;
+
+--disable_query_log
+
+INSERT INTO t1 VALUES 
+('e5d9f63237232000158bbfc8bcbe5dbf','f304ae0037332000158bbfc8bcbe5d4f',
+'5398c0e037003000158bbfc8bcbe5dbb'),
+('69d9f63237232000158bbfc8bcbe5dcb','7172ea0037332000158bbfc8bcbe5db6',
+'5c188ca037003000158bbfc8bcbe5dbc'),
+('577ed708d773020058c92cf65e61037a','699708d4d773020058c92cf65e61037c',
+'623a8cd4d773020058c92cf65e6103ea'),
+('96fb652637232000158bbfc8bcbe5db4','df50316637232000158bbfc8bcbe5d23',
+'6835bd6637232000158bbfc8bcbe5d21'),
+('e1d9f63237232000158bbfc8bcbe5db8','96346e0037332000158bbfc8bcbe5daa',
+'697880e037003000158bbfc8bcbe5dcd'),
+('25d9f63237232000158bbfc8bcbe5dbe','f304ae0037332000158bbfc8bcbe5d4f',
+'6a9804e037003000158bbfc8bcbe5d09'),
+('96fb652637232000158bbfc8bcbe5db4','e08fad2637232000158bbfc8bcbe5d39',
+'6d25f96637232000158bbfc8bcbe5d79'),
+('e9d9f63237232000158bbfc8bcbe5dc6','7172ea0037332000158bbfc8bcbe5db6',
+'702880e037003000158bbfc8bcbe5d94'),
+('a5d9f63237232000158bbfc8bcbe5dca','f304ae0037332000158bbfc8bcbe5d4f',
+'7188c0e037003000158bbfc8bcbe5d75'),
+('65d9f63237232000158bbfc8bcbe5dc4','f304ae0037332000158bbfc8bcbe5d4f',
+'778880e037003000158bbfc8bcbe5d9e'),
+('a1d9f63237232000158bbfc8bcbe5dc3','7172ea0037332000158bbfc8bcbe5db6',
+'7d0840e037003000158bbfc8bcbe5dde'),
+('21d9f63237232000158bbfc8bcbe5db7','96346e0037332000158bbfc8bcbe5daa',
+'7f6880e037003000158bbfc8bcbe5da7'),
+('96fb652637232000158bbfc8bcbe5db4','ec70316637232000158bbfc8bcbe5d60',
+'8025f96637232000158bbfc8bcbe5dd0'),
+('3dd9f63237232000158bbfc8bcbe5dcc','7172ea0037332000158bbfc8bcbe5db6',
+'823880e037003000158bbfc8bcbe5ded'),
+('96fb652637232000158bbfc8bcbe5db4','7b10fd2637232000158bbfc8bcbe5d30',
+'9a353d6637232000158bbfc8bcbe5dee'),
+('75d9f63237232000158bbfc8bcbe5dd0','ebb4620037332000158bbfc8bcbe5d89',
+'a558c0e037003000158bbfc8bcbe5d36'),
+('6dd9f63237232000158bbfc8bcbe5db5','96346e0037332000158bbfc8bcbe5daa',
+'bc78cca037003000158bbfc8bcbe5d74'),
+('add9f63237232000158bbfc8bcbe5dc7','7172ea0037332000158bbfc8bcbe5db6',
+'c53804a037003000158bbfc8bcbe5db8'),
+('fdd9f63237232000158bbfc8bcbe5dcd','7864ae0037332000158bbfc8bcbe5db8',
+'cfe740e037003000158bbfc8bcbe5de8'),
+('96fb652637232000158bbfc8bcbe5db4','3120fd2637232000158bbfc8bcbe5d42',
+'e2257d6637232000158bbfc8bcbe5ded'),
+('3c3725e237232000158bbfc8bcbe5da1','96346e0037332000158bbfc8bcbe5daa',
+'ee78c0e037003000158bbfc8bcbe5db5'),
+('a9d9f63237232000158bbfc8bcbe5dc0','7172ea0037332000158bbfc8bcbe5db6',
+'f00888a037003000158bbfc8bcbe5dd3'),
+('29d9f63237232000158bbfc8bcbe5db9','7172ea0037332000158bbfc8bcbe5db6',
+'fa0880e037003000158bbfc8bcbe5d70'),
+('b1d9f63237232000158bbfc8bcbe5dcf','ebb4620037332000158bbfc8bcbe5d89',
+'fa48c0e037003000158bbfc8bcbe5d28');
+
+INSERT INTO t2 VALUES
+('17801ac21b13200050fdfbcd2c0713e8','8e826bf03710200044e0bfc8bcbe5d86',
+'14c19a061b13200050fdfbcd2c07134b'),
+('577ed708d773020058c92cf65e61037a','931644d4d773020058c92cf65e61034c',
+'339888d4d773020058c92cf65e6103aa'),
+('df50316637232000158bbfc8bcbe5d23','92826bf03710200044e0bfc8bcbe5da9',
+'3682f56637232000158bbfc8bcbe5d44'),
+('b4f342b237232000158bbfc8bcbe5def','86826bf03710200044e0bfc8bcbe5d70',
+'38e4c2b237232000158bbfc8bcbe5dea'),
+('7b10fd2637232000158bbfc8bcbe5d30','8a826bf03710200044e0bfc8bcbe5d72',
+'4442b56637232000158bbfc8bcbe5d43'),
+('3120fd2637232000158bbfc8bcbe5d42','82826bf03710200044e0bfc8bcbe5d89',
+'49d2396637232000158bbfc8bcbe5d12'),
+('96fb652637232000158bbfc8bcbe5db4','86826bf03710200044e0bfc8bcbe5d79',
+'4e3ca52637232000158bbfc8bcbe5d3e'),
+('17801ac21b13200050fdfbcd2c0713e8','824fd523bf4320007a6d257b3f073963',
+'58c19a061b13200050fdfbcd2c07134e'),
+('699708d4d773020058c92cf65e61037c','901784d4d773020058c92cf65e6103da',
+'5bc708d4d773020058c92cf65e6103d5'),
+('75d9f63237232000158bbfc8bcbe5dd0','86826bf03710200044e0bfc8bcbe5d79',
+'6b52cb7237232000158bbfc8bcbe5ded'),
+('f253da061b13200050fdfbcd2c0713ab','8e826bf03710200044e0bfc8bcbe5d86',
+'81045e061b13200050fdfbcd2c071373'),
+('7b10fd2637232000158bbfc8bcbe5d30','8e826bf03710200044e0bfc8bcbe5d74',
+'8c42b56637232000158bbfc8bcbe5d3f'),
+('e5d9f63237232000158bbfc8bcbe5dbf','7a826bf03710200044e0bfc8bcbe5df5',
+'a7acfe3237232000158bbfc8bcbe5d78'),
+('8a5055c9c61122780043563ef53438e3','9ee1b13dc6112271007f9d0efdb69cd0',
+'a9aff553c6112276015a8006174bee21'),
+('8a4dde73c6112278017a6a4baf547aa7','9ee1b13dc6112271007f9d0efdb69cd0',
+'a9b2f526c61122760003ae07349d294f'),
+('aaccc971c0a8001500fe1ff4302de101','9ee1b13dc6112271007f9d0efdb69cd0',
+'aacceed3c0a80015009069bba51c4e21'),
+('65d9f63237232000158bbfc8bcbe5dc4','8d56406a0a0a0a6b004070b354aada28',
+'ac1bfa3237232000158bbfc8bcbe5dc3'),
+('b85d44954a3623120004689b2d5dd60a','97000fcc0a0a0a6e0104ca999f619e5b',
+'b77bc032cbb00200d71cb9c0c24c9c45'),
+('220f8e71c61122840197e57c33464f70','8d56406a0a0a0a6b004070b354aada28',
+'b9b74f080a0a0b343ba75b95bdb27056'),
+('e08fad2637232000158bbfc8bcbe5d39','82826bf03710200044e0bfc8bcbe5d80',
+'be02756637232000158bbfc8bcbe5d8b'),
+('ebb4620037332000158bbfc8bcbe5d89','7682abf03710200044e0bfc8bcbe5d25',
+'c0122f4437732000158bbfc8bcbe5d7d'),
+('96fb652637232000158bbfc8bcbe5db4','7a82abf03710200044e0bfc8bcbe5d27',
+'c23ca52637232000158bbfc8bcbe5d3b'),
+('22122b37c611228400f9ff91c857581d','9ee1b13dc6112271007f9d0efdb69cd0',
+'d23bbf5dac14641866947512bde59dc5'),
+('db53a9290a0a0a650091abebccf833c6','9ee1b13dc6112271007f9d0efdb69cd0',
+'db54a0f60a0a0a65002c54dcb72b4f41'),
+('e08fad2637232000158bbfc8bcbe5d39','8e826bf03710200044e0bfc8bcbe5d86',
+'f602756637232000158bbfc8bcbe5d88'),
+('699708d4d773020058c92cf65e61037c','8d59d601d7b3020058c92cf65e6103c2',
+'f718a241d7b3020058c92cf65e610332'),
+('df50316637232000158bbfc8bcbe5d23','9e826bf03710200044e0bfc8bcbe5da6',
+'fe82f56637232000158bbfc8bcbe5d4e'),
+('f972d6061b13200050fdfbcd2c0713e5','780395f0df031100a9e78b6c3df2631f',
+'ff4395f0df031100a9e78b6c3df2637e');
+
+INSERT INTO t3 VALUES 
+('87245e061b13200050fdfbcd2c0713cc','7172ea0037332000158bbfc8bcbe5db6'),
+('74af88c6c611227d0066386e74dc853d','74ad1ff3c611227d01d25feac2af603f'),
+('59e22fb137032000158bbfc8bcbe5d52','75d9f63237232000158bbfc8bcbe5dd0'),
+('98906fb137032000158bbfc8bcbe5d65','781da52637232000158bbfc8bcbe5db8'),
+('87245e061b13200050fdfbcd2c0713cc','7864ae0037332000158bbfc8bcbe5db8'),
+('87245e061b13200050fdfbcd2c0713cc','7b10fd2637232000158bbfc8bcbe5d30'),
+('59e22fb137032000158bbfc8bcbe5d52','81a880e037003000158bbfc8bcbe5df8'),
+('74af88c6c611227d0066386e74dc853d','8a4cb6d4c61122780043b1642efcd52b'),
+('1cb8ab9bff500200158bffffffffff62','8a4dde73c6112278017a6a4baf547aa7'),
+('1cb8ab9bff500200158bffffffffff62','8a5055c9c61122780043563ef53438e3'),
+('87245e061b13200050fdfbcd2c0713cc','96346e0037332000158bbfc8bcbe5daa'),
+('59e22fb137032000158bbfc8bcbe5d52','96fb652637232000158bbfc8bcbe5db4'),
+('59e22fb137032000158bbfc8bcbe5d52','a1d9f63237232000158bbfc8bcbe5dc3'),
+('59e22fb137032000158bbfc8bcbe5d52','a5d9f63237232000158bbfc8bcbe5dca'),
+('1cb8ab9bff500200158bffffffffff62','a715cd759f2002002920bde8132e7018'),
+('59e22fb137032000158bbfc8bcbe5d52','a9d9f63237232000158bbfc8bcbe5dc0'),
+('74af88c6c611227d0066386e74dc853d','aacb62e2c0a80015007f67f752c2b12c'),
+('74af88c6c611227d0066386e74dc853d','aaccc971c0a8001500fe1ff4302de101'),
+('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dbb'),
+('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dc7'),
+('59e22fb137032000158bbfc8bcbe5d52','b1d9f63237232000158bbfc8bcbe5dcf'),
+('1cb8ab9bff500200158bffffffffff62','b85d44954a3623120004689b2d5dd60a'),
+('1cb8ab9bff500200158bffffffffff62','b97e89b94a36231201676b73322a0311'),
+('1cb8ab9bff500200158bffffffffff62','cfcbad03d711110050f5edcb9e61038f'),
+('1cb8ab9bff500200158bffffffffff62','d625dccec0a8016700a222a0f7900d06'),
+('1cb8ab9bff500200158bffffffffff62','db53580b0a0a0a6501aa37c294a2ba6b'),
+('1cb8ab9bff500200158bffffffffff62','db53a9290a0a0a650091abebccf833c6'),
+('1cb8ab9bff500200158bffffffffff62','dc0db135c332010016194ffe5bba8f23'),
+('87245e061b13200050fdfbcd2c0713cc','df50316637232000158bbfc8bcbe5d23'),
+('87245e061b13200050fdfbcd2c0713cc','e08fad2637232000158bbfc8bcbe5d39'),
+('59e22fb137032000158bbfc8bcbe5d52','e1d9f63237232000158bbfc8bcbe5db8'),
+('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5db4'),
+('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5dbf'),
+('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dba'),
+('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dc6'),
+('87245e061b13200050fdfbcd2c0713cc','ebb4620037332000158bbfc8bcbe5d89'),
+('87245e061b13200050fdfbcd2c0713cc','ec70316637232000158bbfc8bcbe5d60'),
+('87245e061b13200050fdfbcd2c0713cc','f253da061b13200050fdfbcd2c0713ab'),
+('87245e061b13200050fdfbcd2c0713cc','f304ae0037332000158bbfc8bcbe5d4f'),
+('98906fb137032000158bbfc8bcbe5d65','f972d6061b13200050fdfbcd2c0713e5'),
+('59e22fb137032000158bbfc8bcbe5d52','fdd9f63237232000158bbfc8bcbe5dcd');
+
+--enable_query_log
+
+let $q=
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+      t1.dispatch_group IN
+      (SELECT t2.ugroup
+       FROM t2, t3 t3_i
+       WHERE t2.ugroup = t3_i.sys_id AND 
+             t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+             t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+
+set optimizer_switch='materialization=off';
+eval explain $q;
+eval $q;
+
+set optimizer_switch='materialization=on';
+eval explain $q;
+eval $q;
+
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@save_optimizer_switch;
+
 --echo # End of 5.5 tests
 --echo #
 --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT