← Back to branch summary

~ubuntu-branches/ubuntu/trusty/mariadb-5.5/trusty-proposed

« back to all changes in this revision

Viewing changes to mysql-test/suite/storage_engine/type_binary_indexes.result

  • Committer: Package Import Robot
  • Author(s): Otto Kekäläinen
  • Date: 2013-12-22 10:27:05 UTC
  • Revision ID: package-import@ubuntu.com-20131222102705-mndw7s12mz0szrcn
Tags: upstream-5.5.32
Import upstream version 5.5.32

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/suite/storage_engine/type_binary_indexes.result
 
1
DROP TABLE IF EXISTS t1;
 
2
CREATE TABLE t1 (b BINARY <CUSTOM_COL_OPTIONS>,
 
3
b20 BINARY(20) <CUSTOM_COL_OPTIONS>,
 
4
v16 VARBINARY(16) <CUSTOM_COL_OPTIONS>,
 
5
v128 VARBINARY(128) <CUSTOM_COL_OPTIONS>,
 
6
<CUSTOM_INDEX> b20 (b20)
 
7
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
 
8
SHOW INDEX IN t1;
 
9
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_comment
 
10
t1      1       #       1       b20     #       #       NULL    NULL    #       #               
 
11
INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
 
12
SELECT HEX(b20) FROM t1 ORDER BY b20;
 
13
HEX(b20)
 
14
6368617231000000000000000000000000000000
 
15
6368617232000000000000000000000000000000
 
16
6368617233000000000000000000000000000000
 
17
6368617234000000000000000000000000000000
 
18
DROP TABLE t1;
 
19
CREATE TABLE t1 (b BINARY <CUSTOM_COL_OPTIONS>,
 
20
b20 BINARY(20) <CUSTOM_COL_OPTIONS> PRIMARY KEY,
 
21
v16 VARBINARY(16) <CUSTOM_COL_OPTIONS>,
 
22
v128 VARBINARY(128) <CUSTOM_COL_OPTIONS>
 
23
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
 
24
SHOW INDEX IN t1;
 
25
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_comment
 
26
t1      0       PRIMARY 1       b20     #       #       NULL    NULL    #       #               
 
27
INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
 
28
EXPLAIN SELECT HEX(b20) FROM t1 ORDER BY b20;
 
29
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
30
#       #       #       #       #       PRIMARY #       #       #       #
 
31
SELECT HEX(b20) FROM t1 ORDER BY b20;
 
32
HEX(b20)
 
33
6368617231000000000000000000000000000000
 
34
6368617232000000000000000000000000000000
 
35
6368617233000000000000000000000000000000
 
36
6368617234000000000000000000000000000000
 
37
EXPLAIN SELECT HEX(b20) FROM t1 IGNORE INDEX (PRIMARY) ORDER BY b20 DESC;
 
38
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
39
#       #       #       #       #       NULL    #       #       #       #
 
40
SELECT HEX(b20) FROM t1 ORDER BY b20 DESC;
 
41
HEX(b20)
 
42
6368617234000000000000000000000000000000
 
43
6368617233000000000000000000000000000000
 
44
6368617232000000000000000000000000000000
 
45
6368617231000000000000000000000000000000
 
46
DROP TABLE t1;
 
47
CREATE TABLE t1 (b BINARY <CUSTOM_COL_OPTIONS>,
 
48
b20 BINARY(20) <CUSTOM_COL_OPTIONS>,
 
49
v16 VARBINARY(16) <CUSTOM_COL_OPTIONS>,
 
50
v128 VARBINARY(128) <CUSTOM_COL_OPTIONS>,
 
51
UNIQUE INDEX b_v (b,v128)
 
52
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
 
53
SHOW INDEX IN t1;
 
54
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_comment
 
55
t1      0       b_v     1       b       #       #       NULL    NULL    #       #               
 
56
t1      0       b_v     2       v128    #       #       NULL    NULL    #       #               
 
57
INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
 
58
EXPLAIN SELECT HEX(b), HEX(v128) FROM t1 WHERE b != 'a' AND v128 > 'varchar';
 
59
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
60
#       #       #       #       #       b_v     #       #       #       #
 
61
SELECT HEX(b), HEX(v128) FROM t1 WHERE b != 'a' AND v128 > 'varchar';
 
62
HEX(b)  HEX(v128)
 
63
62      766172636861723162
 
64
63      766172636861723362
 
65
EXPLAIN SELECT HEX(b), HEX(v128) FROM t1 USE INDEX (b_v) WHERE b != 'a' AND v128 > 'varchar';
 
66
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
67
#       #       #       #       #       b_v     #       #       #       #
 
68
SELECT HEX(b), HEX(v128) FROM t1 USE INDEX (b_v) WHERE b != 'a' AND v128 > 'varchar';
 
69
HEX(b)  HEX(v128)
 
70
62      766172636861723162
 
71
63      766172636861723362
 
72
EXPLAIN SELECT HEX(v128), COUNT(*) FROM t1 GROUP BY HEX(v128);
 
73
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
74
#       #       #       #       #       b_v     #       #       #       #
 
75
SELECT HEX(v128), COUNT(*) FROM t1 GROUP BY HEX(v128);
 
76
HEX(v128)       COUNT(*)
 
77
766172636861723162      2
 
78
766172636861723262      1
 
79
766172636861723362      1
 
80
DROP TABLE t1;
 
81
CREATE TABLE t1 (b BINARY <CUSTOM_COL_OPTIONS>,
 
82
b20 BINARY(20) <CUSTOM_COL_OPTIONS>,
 
83
v16 VARBINARY(16) <CUSTOM_COL_OPTIONS>,
 
84
v128 VARBINARY(128) <CUSTOM_COL_OPTIONS>,
 
85
INDEX (v16(10))
 
86
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
 
87
SHOW INDEX IN t1;
 
88
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_comment
 
89
t1      1       v16     1       v16     #       #       10      NULL    #       #               
 
90
INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b'),('d','char5','varchar4a','varchar3b'),('e','char6','varchar2a','varchar3b');
 
91
INSERT INTO t1 (b,b20,v16,v128) SELECT b,b20,v16,v128 FROM t1;
 
92
EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 WHERE v16 LIKE 'varchar%';
 
93
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
94
#       #       #       #       #       NULL    #       #       #       #
 
95
SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 WHERE v16 LIKE 'varchar%';
 
96
HEX(SUBSTRING(v16,7,3))
 
97
723161
 
98
723161
 
99
723161
 
100
723161
 
101
723261
 
102
723261
 
103
723261
 
104
723261
 
105
723361
 
106
723361
 
107
723461
 
108
723461
 
109
EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%';
 
110
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
111
#       #       #       #       #       v16     #       #       #       #
 
112
SELECT HEX(SUBSTRING(v16,7,3)) FROM t1 FORCE INDEX (v16) WHERE v16 LIKE 'varchar%';
 
113
HEX(SUBSTRING(v16,7,3))
 
114
723161
 
115
723161
 
116
723161
 
117
723161
 
118
723261
 
119
723261
 
120
723261
 
121
723261
 
122
723361
 
123
723361
 
124
723461
 
125
723461
 
126
DROP TABLE t1;