| blob | e68242a819140c2efc064a9fcccb51a874943df7 |
1 create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB;
2 insert into t1 values (1, 3);
3 select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ;
4 count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ
5 1
6 select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
7 Case When Count(*) < MAX_REQ Then 1 Else 0 End
8 1
9 drop table t1;
10 create table t1m (a int) engine=myisam;
11 create table t1i (a int) engine=innodb;
12 create table t2m (a int) engine=myisam;
13 create table t2i (a int) engine=innodb;
14 insert into t2m values (5);
15 insert into t2i values (5);
16 select min(a) from t1m;
17 min(a)
18 NULL
19 select min(7) from t1m;
20 min(7)
21 NULL
22 select min(7) from DUAL;
23 min(7)
24 7
25 explain select min(7) from t2m join t1m;
26 id select_type table type possible_keys key key_len ref rows Extra
27 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
28 select min(7) from t2m join t1m;
29 min(7)
30 NULL
31 select max(a) from t1m;
32 max(a)
33 NULL
34 select max(7) from t1m;
35 max(7)
36 NULL
37 select max(7) from DUAL;
38 max(7)
39 7
40 explain select max(7) from t2m join t1m;
41 id select_type table type possible_keys key key_len ref rows Extra
42 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
43 select max(7) from t2m join t1m;
44 max(7)
45 NULL
46 select 1, min(a) from t1m where a=99;
47 1 min(a)
48 1 NULL
49 select 1, min(a) from t1m where 1=99;
50 1 min(a)
51 1 NULL
52 select 1, min(1) from t1m where a=99;
53 1 min(1)
54 1 NULL
55 select 1, min(1) from t1m where 1=99;
56 1 min(1)
57 1 NULL
58 select 1, max(a) from t1m where a=99;
59 1 max(a)
60 1 NULL
61 select 1, max(a) from t1m where 1=99;
62 1 max(a)
63 1 NULL
64 select 1, max(1) from t1m where a=99;
65 1 max(1)
66 1 NULL
67 select 1, max(1) from t1m where 1=99;
68 1 max(1)
69 1 NULL
70 select min(a) from t1i;
71 min(a)
72 NULL
73 select min(7) from t1i;
74 min(7)
75 NULL
76 select min(7) from DUAL;
77 min(7)
78 7
79 explain select min(7) from t2i join t1i;
80 id select_type table type possible_keys key key_len ref rows Extra
81 1 SIMPLE t2i ALL NULL NULL NULL NULL 1
82 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
83 select min(7) from t2i join t1i;
84 min(7)
85 NULL
86 select max(a) from t1i;
87 max(a)
88 NULL
89 select max(7) from t1i;
90 max(7)
91 NULL
92 select max(7) from DUAL;
93 max(7)
94 7
95 explain select max(7) from t2i join t1i;
96 id select_type table type possible_keys key key_len ref rows Extra
97 1 SIMPLE t2i ALL NULL NULL NULL NULL 1
98 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
99 select max(7) from t2i join t1i;
100 max(7)
101 NULL
102 select 1, min(a) from t1i where a=99;
103 1 min(a)
104 1 NULL
105 select 1, min(a) from t1i where 1=99;
106 1 min(a)
107 1 NULL
108 select 1, min(1) from t1i where a=99;
109 1 min(1)
110 1 NULL
111 select 1, min(1) from t1i where 1=99;
112 1 min(1)
113 1 NULL
114 select 1, max(a) from t1i where a=99;
115 1 max(a)
116 1 NULL
117 select 1, max(a) from t1i where 1=99;
118 1 max(a)
119 1 NULL
120 select 1, max(1) from t1i where a=99;
121 1 max(1)
122 1 NULL
123 select 1, max(1) from t1i where 1=99;
124 1 max(1)
125 1 NULL
126 explain select count(*), min(7), max(7) from t1m, t1i;
127 id select_type table type possible_keys key key_len ref rows Extra
128 1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
129 1 SIMPLE t1i ALL NULL NULL NULL NULL 1
130 select count(*), min(7), max(7) from t1m, t1i;
131 count(*) min(7) max(7)
132 0 NULL NULL
133 explain select count(*), min(7), max(7) from t1m, t2i;
134 id select_type table type possible_keys key key_len ref rows Extra
135 1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
136 1 SIMPLE t2i ALL NULL NULL NULL NULL 1
137 select count(*), min(7), max(7) from t1m, t2i;
138 count(*) min(7) max(7)
139 0 NULL NULL
140 explain select count(*), min(7), max(7) from t2m, t1i;
141 id select_type table type possible_keys key key_len ref rows Extra
142 1 SIMPLE t2m system NULL NULL NULL NULL 1
143 1 SIMPLE t1i ALL NULL NULL NULL NULL 1
144 select count(*), min(7), max(7) from t2m, t1i;
145 count(*) min(7) max(7)
146 0 NULL NULL
147 drop table t1m, t1i, t2m, t2i;
148 #
149 # Bug#12713907: STRANGE OPTIMIZE & WRONG RESULT UNDER ORDER BY
150 # COUNT(*) LIMIT.
151 #
152 CREATE TABLE t1 (
153 id BIGINT(20) ,
154 member_id_to INT(11) ,
155 r_date DATE ,
156 PRIMARY KEY (id,r_date),
157 KEY r_date_idx (r_date),
158 KEY t1_idx01 (member_id_to)
159 ) ENGINE=InnoDB;
160 INSERT INTO t1 VALUES
161 (107924526,518491,'2011-05-01'),
162 (107924527,518491,'2011-05-01'),
163 (107924528,518491,'2011-05-01'),
164 (107924529,518491,'2011-05-01'),
165 (107924530,518491,'2011-05-01'),
166 (107924531,518491,'2011-05-01'),
167 (107924532,518491,'2011-05-01'),
168 (107924534,518491,'2011-06-21'),
169 (107924535,518491,'2011-06-21'),
170 (107924536,518491,'2011-06-21'),
171 (107924537,518491,'2011-06-21'),
172 (107924538,518491,'2011-06-21'),
173 (107924542,1601319,'2011-06-21'),
174 (107924543,1601319,'2011-06-21'),
175 (107924544,1601319,'2011-06-21'),
176 (107924545,1601319,'2011-06-21'),
177 (107924546,1601319,'2011-06-21'),
178 (107924547,1601319,'2011-06-21'),
179 (107924548,1601319,'2011-06-21'),
180 (107924549,1601319,'2011-06-21'),
181 (107924550,1601319,'2011-06-21');
182 SELECT member_id_to, COUNT(*) FROM t1 WHERE r_date =
183 '2011-06-21' GROUP BY member_id_to ORDER BY 2 LIMIT 1;
184 member_id_to COUNT(*)
185 518491 5
186 DROP TABLE t1;
187 # End of test BUG#12713907