--source include/have_innodb.inc

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `t_8_h22c`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `t_8_h22c` (

  `wkey` int(11) DEFAULT NULL,

  `pkey` int(11) NOT NULL,

  `c_zib3db` double DEFAULT NULL,

  `c_ufw4k` text DEFAULT NULL,

  `c_ss3rcd` int(11) DEFAULT NULL,

  `c_epb6pb` int(11) DEFAULT NULL,

  `c_btw2nd` text DEFAULT NULL,

  `c_50oqyb` double DEFAULT NULL,

  `c_guli2b` double DEFAULT NULL,

  `c_jwxhwc` int(11) DEFAULT NULL,

  `c_dbhvpc` text DEFAULT NULL,

  PRIMARY KEY (`pkey`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `t_8_h22c` WRITE;

/*!40000 ALTER TABLE `t_8_h22c` DISABLE KEYS */;

INSERT INTO `t_8_h22c` VALUES

(4,24000,85.7,NULL,92,53,'taij5b',NULL,24.21,NULL,NULL),

(4,25000,28.9,NULL,9,17,'kstagc',80.63,79.25,NULL,NULL),

(4,26000,92.94,NULL,71,74,'p7ckgd',9.2,NULL,NULL,NULL),

(4,27000,15.75,NULL,73,24,'0xkmqb',44.77,54.81,NULL,NULL),

(4,28000,67.41,NULL,2,75,'bf186c',NULL,68.8,NULL,NULL),

(5,29000,47.85,'8nwltc',NULL,4,'95sonb',16.34,19.66,NULL,'gjxqyb'),

(5,30000,48.77,'bkljrd',NULL,24,'e2prqc',60.3,99.11,NULL,'q_ujt'),

(5,31000,20.49,'zlunhc',NULL,80,'clrfmd',57.89,58.16,NULL,'vnai0d'),

(7,36000,46.6,'umf1db',43,35,'3_jjo',NULL,NULL,15,'c53_fc'),

(7,37000,85.21,'x_khec',42,27,'_blr_',NULL,58.32,55,'anvn_'),

(7,38000,61.68,'1jmbtd',20,89,'tairb',NULL,21.59,54,'h0vefb'),

(7,39000,NULL,'yf1dy',57,7,'lojqod',NULL,NULL,65,'uy3nkd'),

(7,40000,56.46,'c6kggd',18,43,'b7i8ud',NULL,NULL,81,'h2tgbb'),

(7,41000,9.19,'u7abj',50,61,'pqlz_',NULL,52.29,66,'qsk88c'),

(8,42000,82.18,'uceeyb',14,52,NULL,21.93,NULL,50,'ngghwc'),

(8,43000,67.21,'pn2uv',17,44,NULL,57.18,81.29,58,NULL),

(8,44000,21.21,'_ccifc',44,38,'2bfdid',53.83,77.6,23,NULL),

(8,45000,46.92,NULL,71,82,NULL,95.29,38.42,40,'e_mg_d'),

(8,46000,46.5,'jbtopc',62,84,'gkcf9',48.17,11.74,71,'ufzifd'),

(8,47000,15.3,'sigkgc',46,46,'kvjq2c',96.78,26.57,52,NULL),

(15,82000,34.76,'ljtpv',63,96,'cuzczd',80.93,NULL,NULL,NULL),

(15,83000,73.61,'lczz_',10,78,'meq2cb',22.91,NULL,NULL,'knsokc'),

(15,84000,16.42,'azvwd',77,66,NULL,61.33,NULL,NULL,'rc9bnb'),

(15,85000,65.95,'6ojbo',79,75,'v_nduc',97.58,NULL,NULL,'uz3r7c'),

(15,86000,47.6,'rz7s4',35,60,'ad4s_d',69.66,NULL,NULL,'rvdcx'),

(15,87000,2.3,'mdz58',4,16,'u1ci8',95.37,NULL,NULL,'ldc1vd');

/*!40000 ALTER TABLE `t_8_h22c` ENABLE KEYS */;

UNLOCK TABLES;

DROP TABLE IF EXISTS `t_ejqvmc`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `t_ejqvmc` (

  `wkey` int(11) DEFAULT NULL,

  `pkey` int(11) NOT NULL,

  `c_8yhj0b` text DEFAULT NULL,

  `c_cdyxcd` int(11) DEFAULT NULL,

  `c_rwkkmd` int(11) DEFAULT NULL,

  `c_hv3_p` double DEFAULT NULL,

  `c_ph2fed` int(11) DEFAULT NULL,

  `c_qfwknb` text DEFAULT NULL,

  `c_r8mgoc` text DEFAULT NULL,

  `c_uk0c2b` text DEFAULT NULL,

  PRIMARY KEY (`pkey`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `t_ejqvmc` WRITE;

/*!40000 ALTER TABLE `t_ejqvmc` DISABLE KEYS */;

INSERT INTO `t_ejqvmc` VALUES

(6,32000,'rlsw9',85,34,15.16,9,'guyu3d',NULL,'odenzc'),

(6,33000,'sbf7i',57,76,18.55,65,'hrof_',NULL,'6aqqob'),

(6,34000,'bsepl',45,39,72.31,48,'o9w7wc',NULL,'w8l33c'),

(6,35000,'arfhdc',19,62,66.48,32,'2hmhcd',NULL,'lxh98d'),

(9,48000,'3k6ezd',NULL,46,44.31,75,'_jidn',NULL,'_cx4qd'),

(9,49000,'_jncub',NULL,34,NULL,87,'t4susc','gg2ruc','w3f_3'),

(9,50000,'9uc5ub',NULL,86,87.28,52,'ybj2wd','veegnb','cblskc'),

(9,51000,'ie4zob',NULL,31,37.78,66,'ms9j2d','fhz7m','r2szrc'),

(9,52000,'y0fjyb',NULL,58,99.78,10,NULL,'vrltj','xdaxxc'),

(9,53000,'d6b62d',NULL,58,28.86,92,'8hv5sb','5ac5yc','ymefgb'),

(9,54000,'pagfnc',NULL,62,NULL,90,'o6usrd','dqpayc','c02_k'),

(9,55000,NULL,NULL,1,2147483648.1,81,'bgmukd','egwrec','vfwgd'),

(12,65000,'a7p9zc',20,83,39.5,100,'kgmhqc','e7pvyd','11ibib'),

(12,66000,'yg2qrd',16,93,27.78,21,'ihp3i',NULL,'qd_vic'),

(12,67000,NULL,51,58,62.68,91,NULL,NULL,'4_smdb'),

(14,76000,'ifeqg',36,NULL,NULL,66,'esjmcd','c80nhd','plhue'),

(14,77000,NULL,48,NULL,40.6,5,'w2zo6b','vm1nvc',NULL),

(14,78000,'gwxbad',91,NULL,21.52,23,'5keng','xc5oad','3jddfc'),

(14,79000,'9pbxw',93,NULL,90.37,32,'kwvf7','olnnmb','uumj3c'),

(14,80000,'hhookd',100,NULL,79.58,57,'exfl7d','l1y8b',NULL),

(14,81000,NULL,43,NULL,96.55,10,'yqu7ud','nmsco','eyby2b'),

(16,88000,'ivb8y',26,29,57.71,16,NULL,'6kfi0c',NULL),

(16,89000,'am275b',41,6,20.1,67,NULL,'nuzcad',NULL),

(16,90000,'yc036c',13,99,10.43,32,NULL,'mcgxk','vrvhe'),

(16,91000,'jeurnd',4,26,88.22,28,NULL,'gumvgc','ze5a_'),

(16,92000,'e0_y0b',65,1,95.12,6,NULL,'_yvtgc','3wilnd'),

(16,93000,NULL,6,31,76.52,30,NULL,'mjj2ac',NULL),

(16,94000,'6dtuz',68,12,NULL,47,NULL,'fbh5vc','68m4hc'),

(16,95000,'lrmiw',71,19,16.29,95,NULL,'q94bo','z3t7hc');

/*!40000 ALTER TABLE `t_ejqvmc` ENABLE KEYS */;

UNLOCK TABLES;

DROP TABLE IF EXISTS `t_tu__yc`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `t_tu__yc` (

  `wkey` int(11) DEFAULT NULL,

  `pkey` int(11) NOT NULL,

  `c_q03lu` double DEFAULT NULL,

  `c_y2uw` text DEFAULT NULL,

  `c_1f41gb` text DEFAULT NULL,

  `c_9daz8b` text DEFAULT NULL,

  `c_joc9kb` int(11) DEFAULT NULL,

  `c_ndt7r` double DEFAULT NULL,

  `c_dsdveb` int(11) DEFAULT NULL,

  PRIMARY KEY (`pkey`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `t_tu__yc` WRITE;

/*!40000 ALTER TABLE `t_tu__yc` DISABLE KEYS */;

INSERT INTO `t_tu__yc` VALUES

(1,11000,84.47,'_zi_wc','phds2c',NULL,11,NULL,59),

(1,12000,44.54,'shtqnb','fnsi0',NULL,8,2147483648.1,37),

(1,13000,67.69,'ohmogd','kmvhg',NULL,77,28.4,11),

(2,14000,81.63,'m1rbo','_uj5ab','pau_bd',70,NULL,12),

(2,15000,26.59,'lo4xbb','0j212d','i8qtdc',28,NULL,38),

(2,16000,96.28,'gqf6o','d_fttc','blv1k',25,NULL,68),

(2,17000,77.52,'k1q6kd','srorfc','3hozeb',35,NULL,60),

(3,18000,12.15,'cbnkrb',NULL,'kotd6c',63,25.54,40),

(3,19000,28.17,'wn1qpd',NULL,'kk3lv',60,20.6,71),

(3,20000,39.2,'r7txyb',NULL,'dcvvj',44,87.15,4),

(3,21000,86.94,'uk6vtd',NULL,'zxw54',63,95.1,50),

(3,22000,46.79,'xy_rqd',NULL,'_8dxj',86,46.12,42),

(3,23000,98.38,'jud5_c',NULL,'qjiswd',71,2.1,65),

(10,56000,NULL,NULL,'grpa_d','cx_zkb',61,4.95,3),

(10,57000,NULL,NULL,'_h5e4','u0yfm',78,92.89,83),

(10,58000,NULL,NULL,'xbox7b','wnxwod',96,16.84,54),

(10,59000,NULL,NULL,'gbtdv',NULL,97,70.86,61),

(11,60000,70.67,'evl_3b','1epdod','uudutd',75,NULL,NULL),

(11,61000,29.41,'x_lkyd','alqu3','hbeso',2,NULL,NULL),

(11,62000,29.66,'y_uezd','zi9wb','sahip',28,NULL,NULL),

(11,63000,31.11,NULL,'rdy_wb',NULL,82,NULL,NULL),

(11,64000,75.21,'6yzn6c','_ll4ld','afpiw',53,NULL,NULL),

(13,68000,NULL,'rtmyuc','dqv7ib',NULL,44,NULL,NULL),

(13,69000,NULL,'w6_wbd','6mku1',NULL,86,NULL,NULL),

(13,70000,NULL,'lth_bb',NULL,NULL,23,NULL,NULL),

(13,71000,NULL,'brr9rc','2j2nqb',NULL,84,NULL,NULL),

(13,72000,NULL,'7dpt2',NULL,NULL,62,NULL,NULL),

(13,73000,NULL,'xiewhd','dndc6d',NULL,37,NULL,NULL),

(13,74000,NULL,'ervd5b','6sv_rd',NULL,68,NULL,NULL),

(13,75000,NULL,'sz1wc',NULL,NULL,5,NULL,NULL);

/*!40000 ALTER TABLE `t_tu__yc` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

let $query= 

select  

  t_tu__yc.wkey as c0, 

  t_tu__yc.pkey as c1, 

  t_tu__yc.c_q03lu as c2, 

  t_tu__yc.c_y2uw as c3, 

  t_tu__yc.c_1f41gb as c4, 

  t_tu__yc.c_9daz8b as c5, 

  t_tu__yc.c_joc9kb as c6, 

  t_tu__yc.c_ndt7r as c7, 

  t_tu__yc.c_dsdveb as c8

from 

  t_tu__yc

where t_tu__yc.c_q03lu not in (

  select  

        PERCENT_RANK() over (partition by ref_0.c_50oqyb,ref_0.c_dbhvpc order by ref_0.c_ss3rcd,ref_0.c_zib3db) as c0

    from 

      t_8_h22c as ref_0

    );

eval analyze $query;

eval $query;

analyze table t_tu__yc, t_8_h22c, t_ejqvmc persistent for all;

eval analyze $query;

eval $query;

drop table t_tu__yc, t_8_h22c, t_ejqvmc;

{code:sql|title=10.6 0fbcb0a2}

id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra

1	PRIMARY	t_tu__yc	ALL	NULL	NULL	NULL	NULL	1	30.00	100.00	3.33	Using where

2	DEPENDENT SUBQUERY	ref_0	ALL	NULL	NULL	NULL	NULL	2	26.00	100.00	100.00	Using temporary

c0	c1	c2	c3	c4	c5	c6	c7	c8

11	60000	70.67	evl_3b	1epdod	uudutd	75	NULL	NULL

id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra

1	PRIMARY	t_tu__yc	ALL	NULL	NULL	NULL	NULL	30	30.00	100.00	60.00	Using where

2	MATERIALIZED	ref_0	ALL	NULL	NULL	NULL	NULL	26	26.00	100.00	100.00	Using temporary

c0	c1	c2	c3	c4	c5	c6	c7	c8

1	11000	84.47	_zi_wc	phds2c	NULL	11	NULL	59

1	12000	44.54	shtqnb	fnsi0	NULL	8	2147483648.1	37

1	13000	67.69	ohmogd	kmvhg	NULL	77	28.4	11

2	14000	81.63	m1rbo	_uj5ab	pau_bd	70	NULL	12

2	15000	26.59	lo4xbb	0j212d	i8qtdc	28	NULL	38

2	16000	96.28	gqf6o	d_fttc	blv1k	25	NULL	68

2	17000	77.52	k1q6kd	srorfc	3hozeb	35	NULL	60

3	18000	12.15	cbnkrb	NULL	kotd6c	63	25.54	40

3	19000	28.17	wn1qpd	NULL	kk3lv	60	20.6	71

3	20000	39.2	r7txyb	NULL	dcvvj	44	87.15	4

3	21000	86.94	uk6vtd	NULL	zxw54	63	95.1	50

3	22000	46.79	xy_rqd	NULL	_8dxj	86	46.12	42

3	23000	98.38	jud5_c	NULL	qjiswd	71	2.1	65

11	60000	70.67	evl_3b	1epdod	uudutd	75	NULL	NULL

11	61000	29.41	x_lkyd	alqu3	hbeso	2	NULL	NULL

11	62000	29.66	y_uezd	zi9wb	sahip	28	NULL	NULL

11	63000	31.11	NULL	rdy_wb	NULL	82	NULL	NULL

11	64000	75.21	6yzn6c	_ll4ld	afpiw	53	NULL	NULL