1/*
2Copyright (c) 2009, 2010, Oracle and/or its affiliates. All rights reserved.
3
4The 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
6MySQL Connectors. There are special exceptions to the terms and
7conditions of the GPLv2 as it is applied to this software, see the
8FLOSS License Exception
9<http://www.mysql.com/about/legal/licensing/foss-exception.html>.
10
11This program is free software; you can redistribute it and/or modify
12it under the terms of the GNU General Public License as published
13by the Free Software Foundation; version 2 of the License.
14
15This program is distributed in the hope that it will be useful, but
16WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
17or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
18for more details.
19
20You should have received a copy of the GNU General Public License along
21with this program; if not, write to the Free Software Foundation, Inc.,
2251 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
23*/
24#include "my_test.h"
25
26static 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
113static 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
159static 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
243static 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
297static 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
361static 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
404static 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
480static 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) - \
514count(s2) as vx from t1 group by s2 having sum(s1) - count(s2) < (select f1() \
515from 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
530static 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
579static 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
639static 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
673struct 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
688int 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