"Fossies" - the Fresh Open Source Software Archive

Member "mariadb-connector-c-3.0.9-src/unittest/libmariadb/view.c" (8 Feb 2019, 22863 Bytes) of package /linux/misc/mariadb-connector-c-3.0.9-src.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) C and C++ source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. See also the last Fossies "Diffs" side-by-side code changes report for "view.c": 3.0.5-src_vs_3.0.6-src.

    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   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 
  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   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 
  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 }