1 | /* |
2 | Copyright (c) 2009, 2010, Oracle and/or its affiliates. All rights reserved. |
3 | |
4 | The MySQL Connector/C is licensed under the terms of the GPLv2 |
5 | <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most |
6 | MySQL Connectors. There are special exceptions to the terms and |
7 | conditions of the GPLv2 as it is applied to this software, see the |
8 | FLOSS License Exception |
9 | <http://www.mysql.com/about/legal/licensing/foss-exception.html>. |
10 | |
11 | This program is free software; you can redistribute it and/or modify |
12 | it under the terms of the GNU General Public License as published |
13 | by the Free Software Foundation; version 2 of the License. |
14 | |
15 | This program is distributed in the hope that it will be useful, but |
16 | WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY |
17 | or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License |
18 | for more details. |
19 | |
20 | You should have received a copy of the GNU General Public License along |
21 | with this program; if not, write to the Free Software Foundation, Inc., |
22 | 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA |
23 | */ |
24 | #include "my_test.h" |
25 | |
26 | static int test_view(MYSQL *mysql) |
27 | { |
28 | MYSQL_STMT *stmt; |
29 | int rc, i; |
30 | MYSQL_BIND my_bind[1]; |
31 | char str_data[50]; |
32 | ulong length = 0L; |
33 | long is_null = 0L; |
34 | const char *query= |
35 | "SELECT COUNT(*) FROM v1 WHERE SERVERNAME=?" ; |
36 | |
37 | rc = mysql_query(mysql, "DROP TABLE IF EXISTS t1,t2,t3,v1" ); |
38 | check_mysql_rc(rc, mysql); |
39 | |
40 | rc = mysql_query(mysql, "DROP VIEW IF EXISTS v1,t1,t2,t3" ); |
41 | check_mysql_rc(rc, mysql); |
42 | rc= mysql_query(mysql,"CREATE TABLE t1 (" |
43 | " SERVERGRP varchar(20) NOT NULL default '', " |
44 | " DBINSTANCE varchar(20) NOT NULL default '', " |
45 | " PRIMARY KEY (SERVERGRP)) " |
46 | " CHARSET=latin1 collate=latin1_bin" ); |
47 | check_mysql_rc(rc, mysql); |
48 | rc= mysql_query(mysql,"CREATE TABLE t2 (" |
49 | " SERVERNAME varchar(20) NOT NULL, " |
50 | " SERVERGRP varchar(20) NOT NULL, " |
51 | " PRIMARY KEY (SERVERNAME)) " |
52 | " CHARSET=latin1 COLLATE latin1_bin" ); |
53 | check_mysql_rc(rc, mysql); |
54 | rc= mysql_query(mysql, |
55 | "CREATE TABLE t3 (" |
56 | " SERVERGRP varchar(20) BINARY NOT NULL, " |
57 | " TABNAME varchar(30) NOT NULL, MAPSTATE char(1) NOT NULL, " |
58 | " ACTSTATE char(1) NOT NULL , " |
59 | " LOCAL_NAME varchar(30) NOT NULL, " |
60 | " CHG_DATE varchar(8) NOT NULL default '00000000', " |
61 | " CHG_TIME varchar(6) NOT NULL default '000000', " |
62 | " MXUSER varchar(12) NOT NULL default '', " |
63 | " PRIMARY KEY (SERVERGRP, TABNAME, MAPSTATE, ACTSTATE, " |
64 | " LOCAL_NAME)) CHARSET=latin1 COLLATE latin1_bin" ); |
65 | check_mysql_rc(rc, mysql); |
66 | rc= mysql_query(mysql,"CREATE VIEW v1 AS select sql_no_cache" |
67 | " T0001.SERVERNAME AS SERVERNAME, T0003.TABNAME AS" |
68 | " TABNAME,T0003.LOCAL_NAME AS LOCAL_NAME,T0002.DBINSTANCE AS" |
69 | " DBINSTANCE from t2 T0001 join t1 T0002 join t3 T0003 where" |
70 | " ((T0002.SERVERGRP = T0001.SERVERGRP) and" |
71 | " (T0002.SERVERGRP = T0003.SERVERGRP)" |
72 | " and (T0003.MAPSTATE = _latin1'A') and" |
73 | " (T0003.ACTSTATE = _latin1' '))" ); |
74 | check_mysql_rc(rc, mysql); |
75 | |
76 | stmt= mysql_stmt_init(mysql); |
77 | rc= mysql_stmt_prepare(stmt, SL(query)); |
78 | check_stmt_rc(rc, stmt); |
79 | |
80 | strcpy(str_data, "TEST" ); |
81 | memset(my_bind, '\0', sizeof(MYSQL_BIND)); |
82 | my_bind[0].buffer_type= MYSQL_TYPE_STRING; |
83 | my_bind[0].buffer= (char *)&str_data; |
84 | my_bind[0].buffer_length= 50; |
85 | my_bind[0].length= &length; |
86 | length= 4; |
87 | my_bind[0].is_null= (char*)&is_null; |
88 | rc= mysql_stmt_bind_param(stmt, my_bind); |
89 | check_stmt_rc(rc, stmt); |
90 | |
91 | for (i= 0; i < 3; i++) |
92 | { |
93 | int rowcount= 0; |
94 | |
95 | rc= mysql_stmt_execute(stmt); |
96 | check_stmt_rc(rc, stmt); |
97 | |
98 | while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA) |
99 | rowcount++; |
100 | FAIL_IF(rowcount != 1, "Expected 1 row" ); |
101 | } |
102 | mysql_stmt_close(stmt); |
103 | |
104 | rc= mysql_query(mysql, "DROP TABLE t1,t2,t3" ); |
105 | check_mysql_rc(rc, mysql); |
106 | rc= mysql_query(mysql, "DROP VIEW v1" ); |
107 | check_mysql_rc(rc, mysql); |
108 | |
109 | return OK; |
110 | } |
111 | |
112 | |
113 | static int test_view_where(MYSQL *mysql) |
114 | { |
115 | MYSQL_STMT *stmt; |
116 | int rc, i; |
117 | const char *query= |
118 | "select v1.c,v2.c from v1, v2" ; |
119 | |
120 | rc = mysql_query(mysql, "DROP TABLE IF EXISTS t1,v1,v2" ); |
121 | check_mysql_rc(rc, mysql); |
122 | |
123 | rc = mysql_query(mysql, "DROP VIEW IF EXISTS v1,v2,t1" ); |
124 | check_mysql_rc(rc, mysql); |
125 | rc= mysql_query(mysql,"CREATE TABLE t1 (a int, b int)" ); |
126 | check_mysql_rc(rc, mysql); |
127 | rc= mysql_query(mysql,"insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10)" ); |
128 | check_mysql_rc(rc, mysql); |
129 | rc= mysql_query(mysql,"create view v1 (c) as select b from t1 where a<3" ); |
130 | check_mysql_rc(rc, mysql); |
131 | rc= mysql_query(mysql,"create view v2 (c) as select b from t1 where a>=3" ); |
132 | check_mysql_rc(rc, mysql); |
133 | |
134 | stmt= mysql_stmt_init(mysql); |
135 | rc= mysql_stmt_prepare(stmt, SL(query)); |
136 | check_stmt_rc(rc, stmt); |
137 | |
138 | for (i= 0; i < 3; i++) |
139 | { |
140 | int rowcount= 0; |
141 | |
142 | rc= mysql_stmt_execute(stmt); |
143 | check_stmt_rc(rc, stmt); |
144 | while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA) |
145 | rowcount++; |
146 | FAIL_UNLESS(4 == rowcount, "Expected 4 rows" ); |
147 | } |
148 | mysql_stmt_close(stmt); |
149 | |
150 | rc= mysql_query(mysql, "DROP TABLE t1" ); |
151 | check_mysql_rc(rc, mysql); |
152 | rc= mysql_query(mysql, "DROP VIEW v1, v2" ); |
153 | check_mysql_rc(rc, mysql); |
154 | |
155 | return OK; |
156 | } |
157 | |
158 | |
159 | static int test_view_2where(MYSQL *mysql) |
160 | { |
161 | MYSQL_STMT *stmt; |
162 | int rc, i; |
163 | MYSQL_BIND my_bind[8]; |
164 | char parms[8][100]; |
165 | ulong length[8]; |
166 | const char *query= |
167 | "select relid, report, handle, log_group, username, variant, type, " |
168 | "version, erfdat, erftime, erfname, aedat, aetime, aename, dependvars, " |
169 | "inactive from V_LTDX where mandt = ? and relid = ? and report = ? and " |
170 | "handle = ? and log_group = ? and username in ( ? , ? ) and type = ?" ; |
171 | |
172 | rc= mysql_query(mysql, "DROP TABLE IF EXISTS LTDX" ); |
173 | check_mysql_rc(rc, mysql); |
174 | rc= mysql_query(mysql, "DROP VIEW IF EXISTS V_LTDX" ); |
175 | check_mysql_rc(rc, mysql); |
176 | rc= mysql_query(mysql, |
177 | "CREATE TABLE LTDX (MANDT char(3) NOT NULL default '000', " |
178 | " RELID char(2) NOT NULL, REPORT varchar(40) NOT NULL," |
179 | " HANDLE varchar(4) NOT NULL, LOG_GROUP varchar(4) NOT NULL," |
180 | " USERNAME varchar(12) NOT NULL," |
181 | " VARIANT varchar(12) NOT NULL," |
182 | " TYPE char(1) NOT NULL, SRTF2 int(11) NOT NULL," |
183 | " VERSION varchar(6) NOT NULL default '000000'," |
184 | " ERFDAT varchar(8) NOT NULL default '00000000'," |
185 | " ERFTIME varchar(6) NOT NULL default '000000'," |
186 | " ERFNAME varchar(12) NOT NULL," |
187 | " AEDAT varchar(8) NOT NULL default '00000000'," |
188 | " AETIME varchar(6) NOT NULL default '000000'," |
189 | " AENAME varchar(12) NOT NULL," |
190 | " DEPENDVARS varchar(10) NOT NULL," |
191 | " INACTIVE char(1) NOT NULL, CLUSTR smallint(6) NOT NULL," |
192 | " CLUSTD blob," |
193 | " PRIMARY KEY (MANDT, RELID, REPORT, HANDLE, LOG_GROUP, " |
194 | "USERNAME, VARIANT, TYPE, SRTF2))" |
195 | " CHARSET=latin1 COLLATE latin1_bin" ); |
196 | check_mysql_rc(rc, mysql); |
197 | rc= mysql_query(mysql, |
198 | "CREATE VIEW V_LTDX AS select T0001.MANDT AS " |
199 | " MANDT,T0001.RELID AS RELID,T0001.REPORT AS " |
200 | " REPORT,T0001.HANDLE AS HANDLE,T0001.LOG_GROUP AS " |
201 | " LOG_GROUP,T0001.USERNAME AS USERNAME,T0001.VARIANT AS " |
202 | " VARIANT,T0001.TYPE AS TYPE,T0001.VERSION AS " |
203 | " VERSION,T0001.ERFDAT AS ERFDAT,T0001.ERFTIME AS " |
204 | " ERFTIME,T0001.ERFNAME AS ERFNAME,T0001.AEDAT AS " |
205 | " AEDAT,T0001.AETIME AS AETIME,T0001.AENAME AS " |
206 | " AENAME,T0001.DEPENDVARS AS DEPENDVARS,T0001.INACTIVE AS " |
207 | " INACTIVE from LTDX T0001 where (T0001.SRTF2 = 0)" ); |
208 | check_mysql_rc(rc, mysql); |
209 | memset(my_bind, '\0', sizeof(MYSQL_BIND)); |
210 | for (i=0; i < 8; i++) { |
211 | strcpy(parms[i], "1" ); |
212 | my_bind[i].buffer_type = MYSQL_TYPE_VAR_STRING; |
213 | my_bind[i].buffer = (char *)&parms[i]; |
214 | my_bind[i].buffer_length = 1; |
215 | my_bind[i].is_null = 0; |
216 | length[i] = 1; |
217 | my_bind[i].length = &length[i]; |
218 | } |
219 | stmt= mysql_stmt_init(mysql); |
220 | rc= mysql_stmt_prepare(stmt, SL(query)); |
221 | check_stmt_rc(rc, stmt); |
222 | |
223 | rc= mysql_stmt_bind_param(stmt, my_bind); |
224 | check_stmt_rc(rc, stmt); |
225 | |
226 | rc= mysql_stmt_execute(stmt); |
227 | check_stmt_rc(rc, stmt); |
228 | |
229 | rc= mysql_stmt_fetch(stmt); |
230 | FAIL_UNLESS(MYSQL_NO_DATA == rc, "Expected 0 rows" ); |
231 | |
232 | mysql_stmt_close(stmt); |
233 | |
234 | rc= mysql_query(mysql, "DROP VIEW V_LTDX" ); |
235 | check_mysql_rc(rc, mysql); |
236 | rc= mysql_query(mysql, "DROP TABLE LTDX" ); |
237 | check_mysql_rc(rc, mysql); |
238 | |
239 | return OK; |
240 | } |
241 | |
242 | |
243 | static int test_view_star(MYSQL *mysql) |
244 | { |
245 | MYSQL_STMT *stmt; |
246 | int rc, i; |
247 | MYSQL_BIND my_bind[8]; |
248 | char parms[8][100]; |
249 | ulong length[8]; |
250 | const char *query= "SELECT * FROM vt1 WHERE a IN (?,?)" ; |
251 | |
252 | rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, vt1" ); |
253 | check_mysql_rc(rc, mysql); |
254 | rc= mysql_query(mysql, "DROP VIEW IF EXISTS t1, vt1" ); |
255 | check_mysql_rc(rc, mysql); |
256 | rc= mysql_query(mysql, "CREATE TABLE t1 (a int)" ); |
257 | check_mysql_rc(rc, mysql); |
258 | rc= mysql_query(mysql, "CREATE VIEW vt1 AS SELECT a FROM t1" ); |
259 | check_mysql_rc(rc, mysql); |
260 | memset(my_bind, '\0', sizeof(MYSQL_BIND)); |
261 | for (i= 0; i < 2; i++) { |
262 | sprintf((char *)&parms[i], "%d" , i); |
263 | my_bind[i].buffer_type = MYSQL_TYPE_VAR_STRING; |
264 | my_bind[i].buffer = (char *)&parms[i]; |
265 | my_bind[i].buffer_length = 100; |
266 | my_bind[i].is_null = 0; |
267 | my_bind[i].length = &length[i]; |
268 | length[i] = 1; |
269 | } |
270 | |
271 | stmt= mysql_stmt_init(mysql); |
272 | rc= mysql_stmt_prepare(stmt, SL(query)); |
273 | check_stmt_rc(rc, stmt); |
274 | |
275 | rc= mysql_stmt_bind_param(stmt, my_bind); |
276 | check_stmt_rc(rc, stmt); |
277 | |
278 | for (i= 0; i < 3; i++) |
279 | { |
280 | rc= mysql_stmt_execute(stmt); |
281 | check_stmt_rc(rc, stmt); |
282 | rc= mysql_stmt_fetch(stmt); |
283 | FAIL_UNLESS(MYSQL_NO_DATA == rc, "Expected 0 rows" ); |
284 | } |
285 | |
286 | mysql_stmt_close(stmt); |
287 | |
288 | rc= mysql_query(mysql, "DROP TABLE t1" ); |
289 | check_mysql_rc(rc, mysql); |
290 | rc= mysql_query(mysql, "DROP VIEW vt1" ); |
291 | check_mysql_rc(rc, mysql); |
292 | |
293 | return OK; |
294 | } |
295 | |
296 | |
297 | static int test_view_insert(MYSQL *mysql) |
298 | { |
299 | MYSQL_STMT *insert_stmt, *select_stmt; |
300 | int rc, i; |
301 | MYSQL_BIND my_bind[1]; |
302 | int my_val = 0; |
303 | ulong my_length = 0L; |
304 | long my_null = 0L; |
305 | const char *query= |
306 | "insert into v1 values (?)" ; |
307 | |
308 | rc = mysql_query(mysql, "DROP TABLE IF EXISTS t1,v1" ); |
309 | check_mysql_rc(rc, mysql); |
310 | rc = mysql_query(mysql, "DROP VIEW IF EXISTS t1,v1" ); |
311 | check_mysql_rc(rc, mysql); |
312 | |
313 | rc= mysql_query(mysql,"create table t1 (a int, primary key (a))" ); |
314 | check_mysql_rc(rc, mysql); |
315 | |
316 | rc= mysql_query(mysql, "create view v1 as select a from t1 where a>=1" ); |
317 | check_mysql_rc(rc, mysql); |
318 | |
319 | insert_stmt= mysql_stmt_init(mysql); |
320 | rc= mysql_stmt_prepare(insert_stmt, SL(query)); |
321 | check_stmt_rc(rc, insert_stmt); |
322 | query= "select * from t1" ; |
323 | select_stmt= mysql_stmt_init(mysql); |
324 | rc= mysql_stmt_prepare(select_stmt, SL(query)); |
325 | check_stmt_rc(rc, select_stmt); |
326 | |
327 | memset(my_bind, '\0', sizeof(MYSQL_BIND)); |
328 | my_bind[0].buffer_type = MYSQL_TYPE_LONG; |
329 | my_bind[0].buffer = (char *)&my_val; |
330 | my_bind[0].length = &my_length; |
331 | my_bind[0].is_null = (char*)&my_null; |
332 | rc= mysql_stmt_bind_param(insert_stmt, my_bind); |
333 | check_stmt_rc(rc, select_stmt); |
334 | |
335 | for (i= 0; i < 3; i++) |
336 | { |
337 | int rowcount= 0; |
338 | my_val= i; |
339 | |
340 | rc= mysql_stmt_execute(insert_stmt); |
341 | check_stmt_rc(rc, insert_stmt);; |
342 | |
343 | rc= mysql_stmt_execute(select_stmt); |
344 | check_stmt_rc(rc, select_stmt);; |
345 | while (mysql_stmt_fetch(select_stmt) != MYSQL_NO_DATA) |
346 | rowcount++; |
347 | FAIL_UNLESS((i+1) == rowcount, "rowcount != i+1" ); |
348 | } |
349 | mysql_stmt_close(insert_stmt); |
350 | mysql_stmt_close(select_stmt); |
351 | |
352 | rc= mysql_query(mysql, "DROP VIEW v1" ); |
353 | check_mysql_rc(rc, mysql); |
354 | rc= mysql_query(mysql, "DROP TABLE t1" ); |
355 | check_mysql_rc(rc, mysql); |
356 | |
357 | return OK; |
358 | } |
359 | |
360 | |
361 | static int test_left_join_view(MYSQL *mysql) |
362 | { |
363 | MYSQL_STMT *stmt; |
364 | int rc, i; |
365 | const char *query= |
366 | "select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x);" ; |
367 | |
368 | rc = mysql_query(mysql, "DROP TABLE IF EXISTS t1,v1" ); |
369 | check_mysql_rc(rc, mysql); |
370 | |
371 | rc = mysql_query(mysql, "DROP VIEW IF EXISTS v1,t1" ); |
372 | check_mysql_rc(rc, mysql); |
373 | rc= mysql_query(mysql,"CREATE TABLE t1 (a int)" ); |
374 | check_mysql_rc(rc, mysql); |
375 | rc= mysql_query(mysql,"insert into t1 values (1), (2), (3)" ); |
376 | check_mysql_rc(rc, mysql); |
377 | rc= mysql_query(mysql,"create view v1 (x) as select a from t1 where a > 1" ); |
378 | check_mysql_rc(rc, mysql); |
379 | stmt= mysql_stmt_init(mysql); |
380 | rc= mysql_stmt_prepare(stmt, SL(query)); |
381 | check_stmt_rc(rc, stmt); |
382 | |
383 | for (i= 0; i < 3; i++) |
384 | { |
385 | int rowcount= 0; |
386 | |
387 | rc= mysql_stmt_execute(stmt); |
388 | check_stmt_rc(rc, stmt); |
389 | while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA) |
390 | rowcount++; |
391 | FAIL_UNLESS(3 == rowcount, "Expected 3 rows" ); |
392 | } |
393 | mysql_stmt_close(stmt); |
394 | |
395 | rc= mysql_query(mysql, "DROP VIEW v1" ); |
396 | check_mysql_rc(rc, mysql); |
397 | rc= mysql_query(mysql, "DROP TABLE t1" ); |
398 | check_mysql_rc(rc, mysql); |
399 | |
400 | return OK; |
401 | } |
402 | |
403 | |
404 | static int test_view_insert_fields(MYSQL *mysql) |
405 | { |
406 | MYSQL_STMT *stmt; |
407 | char parm[11][1000]; |
408 | ulong l[11]; |
409 | int rc, i; |
410 | int rowcount= 0; |
411 | MYSQL_BIND my_bind[11]; |
412 | const char *query= "INSERT INTO `v1` ( `K1C4` ,`K2C4` ,`K3C4` ,`K4N4` ,`F1C4` ,`F2I4` ,`F3N5` ,`F7F8` ,`F6N4` ,`F5C8` ,`F9D8` ) VALUES( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )" ; |
413 | |
414 | rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, v1" ); |
415 | check_mysql_rc(rc, mysql); |
416 | rc= mysql_query(mysql, "DROP VIEW IF EXISTS t1, v1" ); |
417 | check_mysql_rc(rc, mysql); |
418 | rc= mysql_query(mysql, |
419 | "CREATE TABLE t1 (K1C4 varchar(4) NOT NULL," |
420 | "K2C4 varchar(4) NOT NULL, K3C4 varchar(4) NOT NULL," |
421 | "K4N4 varchar(4) NOT NULL default '0000'," |
422 | "F1C4 varchar(4) NOT NULL, F2I4 int(11) NOT NULL," |
423 | "F3N5 varchar(5) NOT NULL default '00000'," |
424 | "F4I4 int(11) NOT NULL default '0', F5C8 varchar(8) NOT NULL," |
425 | "F6N4 varchar(4) NOT NULL default '0000'," |
426 | "F7F8 double NOT NULL default '0'," |
427 | "F8F8 double NOT NULL default '0'," |
428 | "F9D8 decimal(8,2) NOT NULL default '0.00'," |
429 | "PRIMARY KEY (K1C4,K2C4,K3C4,K4N4)) " |
430 | "CHARSET=latin1 COLLATE latin1_bin" ); |
431 | check_mysql_rc(rc, mysql); |
432 | rc= mysql_query(mysql, |
433 | "CREATE VIEW v1 AS select sql_no_cache " |
434 | " K1C4 AS K1C4, K2C4 AS K2C4, K3C4 AS K3C4, K4N4 AS K4N4, " |
435 | " F1C4 AS F1C4, F2I4 AS F2I4, F3N5 AS F3N5," |
436 | " F7F8 AS F7F8, F6N4 AS F6N4, F5C8 AS F5C8, F9D8 AS F9D8" |
437 | " from t1 T0001" ); |
438 | |
439 | memset(my_bind, '\0', sizeof(my_bind)); |
440 | for (i= 0; i < 11; i++) |
441 | { |
442 | l[i]= 20; |
443 | my_bind[i].buffer_type= MYSQL_TYPE_STRING; |
444 | my_bind[i].is_null= 0; |
445 | my_bind[i].buffer= (char *)&parm[i]; |
446 | |
447 | strcpy(parm[i], "1" ); |
448 | my_bind[i].buffer_length= 2; |
449 | my_bind[i].length= &l[i]; |
450 | } |
451 | stmt= mysql_stmt_init(mysql); |
452 | rc= mysql_stmt_prepare(stmt, SL(query)); |
453 | check_stmt_rc(rc, stmt); |
454 | rc= mysql_stmt_bind_param(stmt, my_bind); |
455 | check_stmt_rc(rc, stmt); |
456 | |
457 | rc= mysql_stmt_execute(stmt); |
458 | check_stmt_rc(rc, stmt); |
459 | mysql_stmt_close(stmt); |
460 | |
461 | query= "select * from t1" ; |
462 | stmt= mysql_stmt_init(mysql); |
463 | rc= mysql_stmt_prepare(stmt, SL(query)); |
464 | check_stmt_rc(rc, stmt); |
465 | rc= mysql_stmt_execute(stmt); |
466 | check_stmt_rc(rc, stmt); |
467 | while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA) |
468 | rowcount++; |
469 | FAIL_UNLESS(1 == rowcount, "Expected 1 row" ); |
470 | |
471 | mysql_stmt_close(stmt); |
472 | rc= mysql_query(mysql, "DROP VIEW v1" ); |
473 | check_mysql_rc(rc, mysql); |
474 | rc= mysql_query(mysql, "DROP TABLE t1" ); |
475 | check_mysql_rc(rc, mysql); |
476 | |
477 | return OK; |
478 | } |
479 | |
480 | static int test_view_sp_list_fields(MYSQL *mysql) |
481 | { |
482 | int rc; |
483 | MYSQL_RES *res; |
484 | MYSQL_ROW row; |
485 | int skip; |
486 | |
487 | /* skip this test if bin_log is on */ |
488 | rc= mysql_query(mysql, "SHOW VARIABLES LIKE 'log_bin'" ); |
489 | check_mysql_rc(rc, mysql); |
490 | res= mysql_store_result(mysql); |
491 | FAIL_IF(!res, "empty/invalid resultset" ); |
492 | row = mysql_fetch_row(res); |
493 | skip= (strcmp((char *)row[1], "ON" ) == 0); |
494 | mysql_free_result(res); |
495 | |
496 | if (skip) { |
497 | diag("bin_log is ON -> skip" ); |
498 | return SKIP; |
499 | } |
500 | |
501 | rc= mysql_query(mysql, "DROP FUNCTION IF EXISTS f1" ); |
502 | check_mysql_rc(rc, mysql); |
503 | rc= mysql_query(mysql, "DROP TABLE IF EXISTS v1, t1, t2" ); |
504 | check_mysql_rc(rc, mysql); |
505 | rc= mysql_query(mysql, "DROP VIEW IF EXISTS v1, t1, t2" ); |
506 | check_mysql_rc(rc, mysql); |
507 | rc= mysql_query(mysql, "create function f1 () returns int return 5" ); |
508 | check_mysql_rc(rc, mysql); |
509 | rc= mysql_query(mysql, "create table t1 (s1 char,s2 char)" ); |
510 | check_mysql_rc(rc, mysql); |
511 | rc= mysql_query(mysql, "create table t2 (s1 int);" ); |
512 | check_mysql_rc(rc, mysql); |
513 | rc= mysql_query(mysql, "create view v1 as select s2,sum(s1) - \ |
514 | count(s2) as vx from t1 group by s2 having sum(s1) - count(s2) < (select f1() \ |
515 | from t2);" ); |
516 | check_mysql_rc(rc, mysql); |
517 | res= mysql_list_fields(mysql, "v1" , NullS); |
518 | FAIL_UNLESS(res != 0 && mysql_num_fields(res) != 0, "0 Fields" ); |
519 | rc= mysql_query(mysql, "DROP FUNCTION f1" ); |
520 | check_mysql_rc(rc, mysql); |
521 | rc= mysql_query(mysql, "DROP VIEW v1" ); |
522 | check_mysql_rc(rc, mysql); |
523 | rc= mysql_query(mysql, "DROP TABLE t1, t2" ); |
524 | mysql_free_result(res); |
525 | check_mysql_rc(rc, mysql); |
526 | |
527 | return OK; |
528 | } |
529 | |
530 | static int test_bug19671(MYSQL *mysql) |
531 | { |
532 | MYSQL_RES *result; |
533 | MYSQL_FIELD *field; |
534 | int rc, retcode= OK; |
535 | |
536 | |
537 | rc= mysql_query(mysql, "set sql_mode=''" ); |
538 | check_mysql_rc(rc, mysql); |
539 | rc= mysql_query(mysql, "drop table if exists t1" ); |
540 | check_mysql_rc(rc, mysql); |
541 | |
542 | rc= mysql_query(mysql, "drop view if exists v1" ); |
543 | check_mysql_rc(rc, mysql); |
544 | |
545 | rc= mysql_query(mysql, "create table t1(f1 int)" ); |
546 | check_mysql_rc(rc, mysql); |
547 | |
548 | rc= mysql_query(mysql, "create view v1 as select va.* from t1 va" ); |
549 | check_mysql_rc(rc, mysql); |
550 | |
551 | rc= mysql_query(mysql, "SELECT * FROM v1" ); |
552 | check_mysql_rc(rc, mysql); |
553 | |
554 | result= mysql_store_result(mysql); |
555 | FAIL_IF(!result, "Invalid result set" ); |
556 | |
557 | field= mysql_fetch_field(result); |
558 | FAIL_IF(!field, "Can't fetch field" ); |
559 | |
560 | if (strcmp(field->table, "v1" ) != 0) { |
561 | diag("Wrong value '%s' for field_table. Expected 'v1'. (%s: %d)" , field->table, __FILE__, __LINE__); |
562 | retcode= FAIL; |
563 | } |
564 | |
565 | mysql_free_result(result); |
566 | |
567 | rc= mysql_query(mysql, "drop view v1" ); |
568 | check_mysql_rc(rc, mysql); |
569 | rc= mysql_query(mysql, "drop table t1" ); |
570 | check_mysql_rc(rc, mysql); |
571 | |
572 | return retcode; |
573 | } |
574 | |
575 | /* |
576 | Bug#11111: fetch from view returns wrong data |
577 | */ |
578 | |
579 | static int test_bug11111(MYSQL *mysql) |
580 | { |
581 | MYSQL_STMT *stmt; |
582 | MYSQL_BIND my_bind[2]; |
583 | char buf[2][20]; |
584 | ulong len[2]; |
585 | int i; |
586 | int rc; |
587 | const char *query= "SELECT DISTINCT f1,ff2 FROM v1" ; |
588 | |
589 | rc= mysql_query(mysql, "drop table if exists t1, t2, v1" ); |
590 | check_mysql_rc(rc, mysql); |
591 | rc= mysql_query(mysql, "drop view if exists t1, t2, v1" ); |
592 | check_mysql_rc(rc, mysql); |
593 | rc= mysql_query(mysql, "create table t1 (f1 int, f2 int)" ); |
594 | check_mysql_rc(rc, mysql); |
595 | rc= mysql_query(mysql, "create table t2 (ff1 int, ff2 int)" ); |
596 | check_mysql_rc(rc, mysql); |
597 | rc= mysql_query(mysql, "create view v1 as select * from t1, t2 where f1=ff1" ); |
598 | check_mysql_rc(rc, mysql); |
599 | rc= mysql_query(mysql, "insert into t1 values (1,1), (2,2), (3,3)" ); |
600 | check_mysql_rc(rc, mysql); |
601 | rc= mysql_query(mysql, "insert into t2 values (1,1), (2,2), (3,3)" ); |
602 | check_mysql_rc(rc, mysql); |
603 | |
604 | stmt= mysql_stmt_init(mysql); |
605 | |
606 | rc= mysql_stmt_prepare(stmt, SL(query)); |
607 | check_stmt_rc(rc, stmt); |
608 | rc= mysql_stmt_execute(stmt); |
609 | check_stmt_rc(rc, stmt); |
610 | |
611 | memset(my_bind, '\0', sizeof(my_bind)); |
612 | for (i=0; i < 2; i++) |
613 | { |
614 | my_bind[i].buffer_type= MYSQL_TYPE_STRING; |
615 | my_bind[i].buffer= (uchar* *)&buf[i]; |
616 | my_bind[i].buffer_length= 20; |
617 | my_bind[i].length= &len[i]; |
618 | } |
619 | |
620 | rc= mysql_stmt_bind_result(stmt, my_bind); |
621 | check_stmt_rc(rc, stmt); |
622 | |
623 | rc= mysql_stmt_fetch(stmt); |
624 | check_stmt_rc(rc, stmt); |
625 | FAIL_UNLESS(!strcmp(buf[1],"1" ), "buf[1] != '1'" ); |
626 | mysql_stmt_close(stmt); |
627 | rc= mysql_query(mysql, "drop view v1" ); |
628 | check_mysql_rc(rc, mysql); |
629 | rc= mysql_query(mysql, "drop table t1, t2" ); |
630 | check_mysql_rc(rc, mysql); |
631 | |
632 | return OK; |
633 | } |
634 | |
635 | /** |
636 | Bug#29306 Truncated data in MS Access with decimal (3,1) columns in a VIEW |
637 | */ |
638 | |
639 | static int test_bug29306(MYSQL *mysql) |
640 | { |
641 | MYSQL_FIELD *field; |
642 | int rc; |
643 | MYSQL_RES *res; |
644 | |
645 | rc= mysql_query(mysql, "DROP TABLE IF EXISTS tab17557" ); |
646 | check_mysql_rc(rc, mysql); |
647 | rc= mysql_query(mysql, "DROP VIEW IF EXISTS view17557" ); |
648 | check_mysql_rc(rc, mysql); |
649 | rc= mysql_query(mysql, "CREATE TABLE tab17557 (dd decimal (3,1))" ); |
650 | check_mysql_rc(rc, mysql); |
651 | rc= mysql_query(mysql, "CREATE VIEW view17557 as SELECT dd FROM tab17557" ); |
652 | check_mysql_rc(rc, mysql); |
653 | rc= mysql_query(mysql, "INSERT INTO tab17557 VALUES (7.6)" ); |
654 | check_mysql_rc(rc, mysql); |
655 | |
656 | /* Checking the view */ |
657 | res= mysql_list_fields(mysql, "view17557" , NULL); |
658 | while ((field= mysql_fetch_field(res))) |
659 | { |
660 | FAIL_UNLESS(field->decimals == 1, "field->decimals != 1" ); |
661 | } |
662 | mysql_free_result(res); |
663 | |
664 | rc= mysql_query(mysql, "DROP TABLE tab17557" ); |
665 | check_mysql_rc(rc, mysql); |
666 | rc= mysql_query(mysql, "DROP VIEW view17557" ); |
667 | check_mysql_rc(rc, mysql); |
668 | |
669 | return OK; |
670 | } |
671 | |
672 | |
673 | struct my_tests_st my_tests[] = { |
674 | {"test_view" , test_view, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
675 | {"test_view_where" , test_view_where, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
676 | {"test_view_2where" , test_view_2where, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
677 | {"test_view_star" , test_view_star, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
678 | {"test_view_insert" , test_view_insert, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
679 | {"test_left_join_view" , test_left_join_view, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
680 | {"test_view_insert_fields" , test_view_insert_fields, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
681 | {"test_view_sp_list_fields" , test_view_sp_list_fields,TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
682 | {"test_bug19671" , test_bug19671, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
683 | {"test_bug29306" , test_bug29306, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
684 | {"test_bug11111" , test_bug11111, TEST_CONNECTION_DEFAULT, 0, NULL , NULL}, |
685 | {NULL, NULL, 0, 0, NULL, NULL} |
686 | }; |
687 | |
688 | int main(int argc, char **argv) |
689 | { |
690 | if (argc > 1) |
691 | get_options(argc, argv); |
692 | |
693 | get_envvars(); |
694 | |
695 | run_tests(my_tests); |
696 | |
697 | return(exit_status()); |
698 | } |
699 | |