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