1--source include/force_myisam_default.inc 2--source include/have_myisam.inc 3 4 5# Const table behavior, table order is not changed, hint is applicable 6 7CREATE TABLE t1(f1 INT) ENGINE=MyISAM; 8INSERT INTO t1 VALUES (1); 9 10CREATE TABLE t2(f1 INT) ENGINE=InnoDB; 11INSERT INTO t2 VALUES (1); 12 13EXPLAIN SELECT /*+ JOIN_PREFIX(t1, t2) */ 1 FROM t1 JOIN t2 ON t1.f1 = t2.f1; 14EXPLAIN SELECT /*+ JOIN_PREFIX(t2, t1) */ 1 FROM t1 JOIN t2 ON t1.f1 = t2.f1; 15 16DROP TABLE t1, t2; 17 18--echo # 19--echo # Bug#23144274 WL9158:ASSERTION `JOIN->BEST_READ < DOUBLE(1.79769313486231570815E+308L)' FAILED 20--echo # 21 22CREATE TABLE t1 23( 24 f1 DATETIME, 25 f2 DATE, 26 f3 VARCHAR(1), 27 KEY (f1) 28) ENGINE=myisam; 29 30CREATE TABLE t2 31( 32 f1 VARCHAR(1), 33 f2 INT, 34 f3 VARCHAR(1), 35 KEY (f1) 36) ENGINE=innodb; 37 38CREATE TABLE t3 39( 40 f1 VARCHAR(1), 41 f2 DATE, 42 f3 DATETIME, 43 f4 INT 44) ENGINE=myisam; 45 46EXPLAIN 47UPDATE /*+ JOIN_ORDER(t2, als1, als3) JOIN_FIXED_ORDER() */ t3 AS als1 48 JOIN t1 AS als2 ON (als1.f3 = als2 .f1) 49 JOIN t1 AS als3 ON (als1.f1 = als3.f3) 50 RIGHT OUTER JOIN t3 AS als4 ON (als1.f3 = als4.f2) 51SET als1.f4 = 'eogqjvbhzodzimqahyzlktkbexkhdwxwgifikhcgblhgswxyutepc' 52WHERE ('i','b') IN (SELECT f3, f1 FROM t2 WHERE f2 <> f2 AND als2.f2 IS NULL); 53 54DROP TABLE t1, t2, t3; 55 56CREATE TABLE t1( 57f1 VARCHAR(1)) ENGINE=myisam; 58 59CREATE TABLE t2( 60f1 VARCHAR(1), 61f2 VARCHAR(1), 62f3 DATETIME, 63KEY(f2)) ENGINE=innodb; 64 65CREATE TABLE t3( 66f1 INT, 67f2 DATE, 68f3 VARCHAR(1), 69KEY(f3)) ENGINE=myisam; 70 71CREATE TABLE t4( 72f1 VARCHAR(1), 73KEY(f1)) ENGINE=innodb; 74ALTER TABLE t4 DISABLE KEYS; 75INSERT INTO t4 VALUES ('x'), (NULL), ('d'), ('x'), ('u'); 76ALTER TABLE t4 ENABLE KEYS; 77 78CREATE TABLE t5( 79f1 VARCHAR(1), 80KEY(f1) ) ENGINE=myisam; 81INSERT INTO t5 VALUES (NULL), ('s'), ('c'), ('x'), ('z'); 82 83EXPLAIN UPDATE /*+ JOIN_ORDER(t4, alias1, alias3) */ t3 AS alias1 84 JOIN t5 ON (alias1.f3 = t5.f1) 85 JOIN t3 AS alias3 ON (alias1.f2 = alias3.f2 ) 86 RIGHT OUTER JOIN t1 ON (alias1.f3 = t1.f1) 87SET alias1.f1 = -1 88WHERE ( 'v', 'o' ) IN 89(SELECT DISTINCT t2.f1, t2.f2 FROM t4 RIGHT OUTER JOIN t2 ON (t4.f1 = t2.f1) 90 WHERE t2.f3 BETWEEN '2001-10-04' AND '2003-05-15'); 91 92DROP TABLE t1, t2, t3, t4, t5; 93 94CREATE TABLE t1 ( 95 f1 INT(11) DEFAULT NULL, 96 f3 VARCHAR(1) DEFAULT NULL, 97 f2 INT(11) DEFAULT NULL, 98 KEY (f1) 99) ENGINE=MyISAM; 100 101CREATE TABLE t2( 102 f1 INT(11) DEFAULT NULL 103) ENGINE=MyISAM; 104 105CREATE TABLE t3 ( 106 f1 VARCHAR(1) DEFAULT NULL, 107 f2 VARCHAR(1) DEFAULT NULL, 108 KEY (f2) 109) ENGINE=InnoDB; 110 111 112EXPLAIN UPDATE /*+ JOIN_SUFFIX(ta1, t2) */ 113 t1 AS ta1 JOIN t1 AS ta2 ON ta1.f1 = ta2.f1 RIGHT JOIN t2 ON (ta1.f1 = t2.f1) 114SET ta1.f2 = '', ta2.f3 = '' 115WHERE ('n', 'r') IN (SELECT f2, f1 FROM t3 WHERE f1 <> f2 XOR ta2.f3 IS NULL); 116 117DROP TABLE t1, t2, t3; 118 119CREATE TABLE t2(f1 VARCHAR(255) DEFAULT NULL, f2 INT(11) DEFAULT NULL, 120 KEY (f1), KEY (f2)) charset latin1 ENGINE=MyISAM; 121 122CREATE TABLE t4(f1 INT(11) DEFAULT NULL, f2 INT(11) DEFAULT NULL, KEY (f1)) 123charset latin1 ENGINE=MyISAM; 124CREATE TABLE t5(f1 INT(11) NOT NULL AUTO_INCREMENT, f2 INT(11) DEFAULT NULL, PRIMARY KEY (f1)) 125charset latin1 ENGINE=InnoDB; 126CREATE TABLE t6(f1 INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (f1)) 127charset latin1 ENGINE=InnoDB; 128CREATE TABLE t7 (f1 VARCHAR(255) DEFAULT NULL) 129charset latin1 ENGINE=InnoDB; 130 131CREATE TABLE t10(f1 INT(11) NOT NULL AUTO_INCREMENT,f2 INT(11) DEFAULT NULL,f3 VARCHAR(10) DEFAULT NULL, 132 PRIMARY KEY (f1),KEY (f2),KEY (f3)) charset latin1 ENGINE=MyISAM; 133 134CREATE TABLE t11(f1 INT(11) DEFAULT NULL,f2 VARCHAR(10) DEFAULT NULL, 135 KEY (f1),KEY (f2)) charset latin1 ENGINE=InnoDB; 136 137EXPLAIN 138SELECT /*+ JOIN_ORDER(alias11, alias8) */ 1 139FROM t4 AS alias4 140 LEFT JOIN t5 AS alias5 JOIN t6 AS alias6 ON alias5.f2 = alias6.f1 141 LEFT JOIN t7 AS alias7 JOIN t2 AS alias8 ON alias7.f1 = alias8.f1 142 ON alias5.f1 = alias8.f2 ON alias4.f2 = alias6.f1 143 JOIN t10 AS alias10 JOIN t11 AS alias11 ON alias10.f1 = alias11.f1 144 ON alias4.f2 = alias11.f2; 145 146EXPLAIN 147SELECT /*+ JOIN_ORDER(alias11, alias10, alias8, alias7) */ 1 148FROM t4 AS alias4 149 LEFT JOIN t5 AS alias5 JOIN t6 AS alias6 ON alias5.f2 = alias6.f1 150 LEFT JOIN t7 AS alias7 JOIN t2 AS alias8 ON alias7.f1 = alias8.f1 151 ON alias5.f1 = alias8.f2 ON alias4.f2 = alias6.f1 152 JOIN t10 AS alias10 JOIN t11 AS alias11 ON alias10.f1 = alias11.f1 153 ON alias4.f2 = alias11.f2; 154 155DROP TABLES t2, t4, t5, t6, t7, t10, t11; 156 157--echo # 158--echo # Bug#23651098 WL#9158 : ASSERTION `!(SJ_NEST->SJ_INNER_TABLES & JOIN->CONST_TABLE_MAP)' FAILED 159--echo # 160 161CREATE TABLE t1 162( 163 f1 INT(11) NOT NULL AUTO_INCREMENT, 164 PRIMARY KEY (f1) 165) ENGINE=InnoDB; 166 167CREATE TABLE t2 168( 169 f1 VARCHAR(1) DEFAULT NULL 170) ENGINE=MyISAM; 171 172CREATE TABLE t3 173( 174 f1 VARCHAR(1) DEFAULT NULL 175) ENGINE=MyISAM; 176 177EXPLAIN SELECT /*+ JOIN_PREFIX(t2, t1) */ t1.f1 FROM t1, t2 178WHERE t2.f1 IN (SELECT t3.f1 FROM t3) AND t1.f1 = 183; 179 180DROP TABLE t1, t2, t3; 181 182