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
test_view(MYSQL * mysql)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 my_bool is_null = 0;
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= &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
test_view_where(MYSQL * mysql)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
test_view_2where(MYSQL * mysql)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 params[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', 8 * sizeof(MYSQL_BIND));
210 for (i=0; i < 8; i++) {
211 strcpy(params[i], "1");
212 my_bind[i].buffer_type = MYSQL_TYPE_VAR_STRING;
213 my_bind[i].buffer = (char *)¶ms[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
test_view_star(MYSQL * mysql)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 params[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', 8 * sizeof(MYSQL_BIND));
261 for (i= 0; i < 2; i++) {
262 sprintf((char *)¶ms[i], "%d", i);
263 my_bind[i].buffer_type = MYSQL_TYPE_VAR_STRING;
264 my_bind[i].buffer = (char *)¶ms[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
test_view_insert(MYSQL * mysql)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 my_bool my_null = 0;
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 = &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
test_left_join_view(MYSQL * mysql)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
test_view_insert_fields(MYSQL * mysql)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 check_mysql_rc(rc, mysql);
431 rc= mysql_query(mysql,
432 "CREATE VIEW v1 AS select sql_no_cache "
433 " K1C4 AS K1C4, K2C4 AS K2C4, K3C4 AS K3C4, K4N4 AS K4N4, "
434 " F1C4 AS F1C4, F2I4 AS F2I4, F3N5 AS F3N5,"
435 " F7F8 AS F7F8, F6N4 AS F6N4, F5C8 AS F5C8, F9D8 AS F9D8"
436 " from t1 T0001");
437
438 memset(my_bind, '\0', sizeof(my_bind));
439 for (i= 0; i < 11; i++)
440 {
441 l[i]= 2;
442 my_bind[i].buffer_type= MYSQL_TYPE_STRING;
443 my_bind[i].is_null= 0;
444 my_bind[i].buffer= (char *)&parm[i];
445
446 strcpy(parm[i], "1");
447 my_bind[i].buffer_length= 2;
448 my_bind[i].length= &l[i];
449 }
450 stmt= mysql_stmt_init(mysql);
451 rc= mysql_stmt_prepare(stmt, SL(query));
452 check_stmt_rc(rc, stmt);
453 rc= mysql_stmt_bind_param(stmt, my_bind);
454 check_stmt_rc(rc, stmt);
455
456 rc= mysql_stmt_execute(stmt);
457 check_stmt_rc(rc, stmt);
458 mysql_stmt_close(stmt);
459
460 query= "select * from t1";
461 stmt= mysql_stmt_init(mysql);
462 rc= mysql_stmt_prepare(stmt, SL(query));
463 check_stmt_rc(rc, stmt);
464 rc= mysql_stmt_execute(stmt);
465 check_stmt_rc(rc, stmt);
466 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
467 rowcount++;
468 FAIL_UNLESS(1 == rowcount, "Expected 1 row");
469
470 mysql_stmt_close(stmt);
471 rc= mysql_query(mysql, "DROP VIEW v1");
472 check_mysql_rc(rc, mysql);
473 rc= mysql_query(mysql, "DROP TABLE t1");
474 check_mysql_rc(rc, mysql);
475
476 return OK;
477 }
478
test_view_sp_list_fields(MYSQL * mysql)479 static int test_view_sp_list_fields(MYSQL *mysql)
480 {
481 int rc;
482 MYSQL_RES *res;
483 MYSQL_ROW row;
484 int skip;
485
486 /* skip this test if bin_log is on */
487 rc= mysql_query(mysql, "SHOW VARIABLES LIKE 'log_bin'");
488 check_mysql_rc(rc, mysql);
489 res= mysql_store_result(mysql);
490 FAIL_IF(!res, "empty/invalid resultset");
491 row = mysql_fetch_row(res);
492 skip= (strcmp((char *)row[1], "ON") == 0);
493 mysql_free_result(res);
494
495 if (skip) {
496 diag("bin_log is ON -> skip");
497 return SKIP;
498 }
499
500 rc= mysql_query(mysql, "DROP FUNCTION IF EXISTS f1");
501 check_mysql_rc(rc, mysql);
502 rc= mysql_query(mysql, "DROP TABLE IF EXISTS v1, t1, t2");
503 check_mysql_rc(rc, mysql);
504 rc= mysql_query(mysql, "DROP VIEW IF EXISTS v1, t1, t2");
505 check_mysql_rc(rc, mysql);
506 rc= mysql_query(mysql, "create function f1 () returns int return 5");
507 check_mysql_rc(rc, mysql);
508 rc= mysql_query(mysql, "create table t1 (s1 char,s2 char)");
509 check_mysql_rc(rc, mysql);
510 rc= mysql_query(mysql, "create table t2 (s1 int);");
511 check_mysql_rc(rc, mysql);
512 rc= mysql_query(mysql, "create view v1 as select s2,sum(s1) - \
513 count(s2) as vx from t1 group by s2 having sum(s1) - count(s2) < (select f1() \
514 from t2);");
515 check_mysql_rc(rc, mysql);
516 res= mysql_list_fields(mysql, "v1", NullS);
517 FAIL_UNLESS(res != 0 && mysql_num_fields(res) != 0, "0 Fields");
518 rc= mysql_query(mysql, "DROP FUNCTION f1");
519 check_mysql_rc(rc, mysql);
520 rc= mysql_query(mysql, "DROP VIEW v1");
521 check_mysql_rc(rc, mysql);
522 rc= mysql_query(mysql, "DROP TABLE t1, t2");
523 mysql_free_result(res);
524 check_mysql_rc(rc, mysql);
525
526 return OK;
527 }
528
test_bug19671(MYSQL * mysql)529 static int test_bug19671(MYSQL *mysql)
530 {
531 MYSQL_RES *result;
532 MYSQL_FIELD *field;
533 int rc, retcode= OK;
534
535
536 rc= mysql_query(mysql, "set sql_mode=''");
537 check_mysql_rc(rc, mysql);
538 rc= mysql_query(mysql, "drop table if exists t1");
539 check_mysql_rc(rc, mysql);
540
541 rc= mysql_query(mysql, "drop view if exists v1");
542 check_mysql_rc(rc, mysql);
543
544 rc= mysql_query(mysql, "create table t1(f1 int)");
545 check_mysql_rc(rc, mysql);
546
547 rc= mysql_query(mysql, "create view v1 as select va.* from t1 va");
548 check_mysql_rc(rc, mysql);
549
550 rc= mysql_query(mysql, "SELECT * FROM v1");
551 check_mysql_rc(rc, mysql);
552
553 result= mysql_store_result(mysql);
554 FAIL_IF(!result, "Invalid result set");
555
556 field= mysql_fetch_field(result);
557 FAIL_IF(!field, "Can't fetch field");
558
559 if (strcmp(field->table, "v1") != 0) {
560 diag("Wrong value '%s' for field_table. Expected 'v1'. (%s: %d)", field->table, __FILE__, __LINE__);
561 retcode= FAIL;
562 }
563
564 mysql_free_result(result);
565
566 rc= mysql_query(mysql, "drop view v1");
567 check_mysql_rc(rc, mysql);
568 rc= mysql_query(mysql, "drop table t1");
569 check_mysql_rc(rc, mysql);
570
571 return retcode;
572 }
573
574 /*
575 Bug#11111: fetch from view returns wrong data
576 */
577
test_bug11111(MYSQL * mysql)578 static int test_bug11111(MYSQL *mysql)
579 {
580 MYSQL_STMT *stmt;
581 MYSQL_BIND my_bind[2];
582 char buf[2][20];
583 ulong len[2];
584 int i;
585 int rc;
586 const char *query= "SELECT DISTINCT f1,ff2 FROM v1";
587
588 rc= mysql_query(mysql, "drop table if exists t1, t2, v1");
589 check_mysql_rc(rc, mysql);
590 rc= mysql_query(mysql, "drop view if exists t1, t2, v1");
591 check_mysql_rc(rc, mysql);
592 rc= mysql_query(mysql, "create table t1 (f1 int, f2 int)");
593 check_mysql_rc(rc, mysql);
594 rc= mysql_query(mysql, "create table t2 (ff1 int, ff2 int)");
595 check_mysql_rc(rc, mysql);
596 rc= mysql_query(mysql, "create view v1 as select * from t1, t2 where f1=ff1");
597 check_mysql_rc(rc, mysql);
598 rc= mysql_query(mysql, "insert into t1 values (1,1), (2,2), (3,3)");
599 check_mysql_rc(rc, mysql);
600 rc= mysql_query(mysql, "insert into t2 values (1,1), (2,2), (3,3)");
601 check_mysql_rc(rc, mysql);
602
603 stmt= mysql_stmt_init(mysql);
604
605 rc= mysql_stmt_prepare(stmt, SL(query));
606 check_stmt_rc(rc, stmt);
607 rc= mysql_stmt_execute(stmt);
608 check_stmt_rc(rc, stmt);
609
610 memset(my_bind, '\0', sizeof(my_bind));
611 for (i=0; i < 2; i++)
612 {
613 my_bind[i].buffer_type= MYSQL_TYPE_STRING;
614 my_bind[i].buffer= (uchar* *)&buf[i];
615 my_bind[i].buffer_length= 20;
616 my_bind[i].length= &len[i];
617 }
618
619 rc= mysql_stmt_bind_result(stmt, my_bind);
620 check_stmt_rc(rc, stmt);
621
622 rc= mysql_stmt_fetch(stmt);
623 check_stmt_rc(rc, stmt);
624 FAIL_UNLESS(!strcmp(buf[1],"1"), "buf[1] != '1'");
625 mysql_stmt_close(stmt);
626 rc= mysql_query(mysql, "drop view v1");
627 check_mysql_rc(rc, mysql);
628 rc= mysql_query(mysql, "drop table t1, t2");
629 check_mysql_rc(rc, mysql);
630
631 return OK;
632 }
633
634 /**
635 Bug#29306 Truncated data in MS Access with decimal (3,1) columns in a VIEW
636 */
637
test_bug29306(MYSQL * mysql)638 static int test_bug29306(MYSQL *mysql)
639 {
640 MYSQL_FIELD *field;
641 int rc;
642 MYSQL_RES *res;
643
644 rc= mysql_query(mysql, "DROP TABLE IF EXISTS tab17557");
645 check_mysql_rc(rc, mysql);
646 rc= mysql_query(mysql, "DROP VIEW IF EXISTS view17557");
647 check_mysql_rc(rc, mysql);
648 rc= mysql_query(mysql, "CREATE TABLE tab17557 (dd decimal (3,1))");
649 check_mysql_rc(rc, mysql);
650 rc= mysql_query(mysql, "CREATE VIEW view17557 as SELECT dd FROM tab17557");
651 check_mysql_rc(rc, mysql);
652 rc= mysql_query(mysql, "INSERT INTO tab17557 VALUES (7.6)");
653 check_mysql_rc(rc, mysql);
654
655 /* Checking the view */
656 res= mysql_list_fields(mysql, "view17557", NULL);
657 while ((field= mysql_fetch_field(res)))
658 {
659 FAIL_UNLESS(field->decimals == 1, "field->decimals != 1");
660 }
661 mysql_free_result(res);
662
663 rc= mysql_query(mysql, "DROP TABLE tab17557");
664 check_mysql_rc(rc, mysql);
665 rc= mysql_query(mysql, "DROP VIEW view17557");
666 check_mysql_rc(rc, mysql);
667
668 return OK;
669 }
670
671
672 struct my_tests_st my_tests[] = {
673 {"test_view", test_view, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
674 {"test_view_where", test_view_where, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
675 {"test_view_2where", test_view_2where, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
676 {"test_view_star", test_view_star, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
677 {"test_view_insert", test_view_insert, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
678 {"test_left_join_view", test_left_join_view, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
679 {"test_view_insert_fields", test_view_insert_fields, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
680 {"test_view_sp_list_fields", test_view_sp_list_fields,TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
681 {"test_bug19671", test_bug19671, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
682 {"test_bug29306", test_bug29306, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
683 {"test_bug11111", test_bug11111, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
684 {NULL, NULL, 0, 0, NULL, NULL}
685 };
686
main(int argc,char ** argv)687 int main(int argc, char **argv)
688 {
689 if (argc > 1)
690 get_options(argc, argv);
691
692 get_envvars();
693
694 run_tests(my_tests);
695
696 return(exit_status());
697 }
698