1# include/rowid_order.inc 2# 3# Test for rowid ordering (and comparison) functions. 4# do index_merge select for tables with PK of various types. 5# 6# The variable 7# $engine_type -- storage engine to be tested 8# has to be set before sourcing this script. 9# 10# Note: The comments/expections refer to InnoDB. 11# They might be not valid for other storage engines. 12# 13# Last update: 14# 2006-08-02 ML test refactored 15# old name was t/rowid_order.test 16# main code went into include/rowid_order.inc 17# 18 19eval SET SESSION STORAGE_ENGINE = $engine_type; 20 21--disable_warnings 22drop table if exists t1, t2, t3,t4; 23--enable_warnings 24 25# Signed number as rowid 26create table t1 ( 27 pk1 int not NULL, 28 key1 int(11), 29 key2 int(11), 30 PRIMARY KEY (pk1), 31 KEY key1 (key1), 32 KEY key2 (key2) 33); 34insert into t1 values (-5, 1, 1), 35 (-100, 1, 1), 36 (3, 1, 1), 37 (0, 1, 1), 38 (10, 1, 1); 39explain select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 40select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 41drop table t1; 42 43# Unsigned numbers as rowids 44create table t1 ( 45 pk1 int unsigned not NULL, 46 key1 int(11), 47 key2 int(11), 48 PRIMARY KEY (pk1), 49 KEY key1 (key1), 50 KEY key2 (key2) 51); 52insert into t1 values (0, 1, 1), 53 (0xFFFFFFFF, 1, 1), 54 (0xFFFFFFFE, 1, 1), 55 (1, 1, 1), 56 (2, 1, 1); 57select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 58drop table t1; 59 60# Case-insensitive char(N) 61create table t1 ( 62 pk1 char(4) not NULL, 63 key1 int(11), 64 key2 int(11), 65 PRIMARY KEY (pk1), 66 KEY key1 (key1), 67 KEY key2 (key2) 68) collate latin2_general_ci; 69insert into t1 values ('a1', 1, 1), 70 ('b2', 1, 1), 71 ('A3', 1, 1), 72 ('B4', 1, 1); 73select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 74drop table t1; 75 76# Multi-part PK 77create table t1 ( 78 pk1 int not NULL, 79 pk2 char(4) not NULL collate latin1_german1_ci, 80 pk3 char(4) not NULL collate latin1_bin, 81 key1 int(11), 82 key2 int(11), 83 PRIMARY KEY (pk1,pk2,pk3), 84 KEY key1 (key1), 85 KEY key2 (key2) 86); 87insert into t1 values 88 (1, 'u', 'u', 1, 1), 89 (1, 'u', char(0xEC), 1, 1), 90 (1, 'u', 'x', 1, 1); 91insert ignore into t1 select pk1, char(0xEC), pk3, key1, key2 from t1; 92insert ignore into t1 select pk1, 'x', pk3, key1, key2 from t1 where pk2='u'; 93insert ignore into t1 select 2, pk2, pk3, key1, key2 from t1; 94select * from t1; 95select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 96 97# Hidden PK 98alter table t1 drop primary key; 99select * from t1; 100select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 101drop table t1; 102 103# Variable-length PK 104# this is also test for Bug#2688 105create table t1 ( 106 pk1 varchar(8) NOT NULL default '', 107 pk2 varchar(4) NOT NULL default '', 108 key1 int(11), 109 key2 int(11), 110 primary key(pk1, pk2), 111 KEY key1 (key1), 112 KEY key2 (key2) 113); 114insert into t1 values ('','empt',2,2), 115 ('a','a--a',2,2), 116 ('bb','b--b',2,2), 117 ('ccc','c--c',2,2), 118 ('dddd','d--d',2,2); 119select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 120 121drop table t1; 122