test/sql/Predicate/test008.ksh

   1 #!/bin/ksh
   2 # Testing Logical Operators(AND, OR, NOT) with varchar using Update statement. (sql/Predicate/test004.ksh)
   3 # Create table t1(f1 varchar(10), f2 char(10), f3 varchar(10), f4 int);
   4 # Insert into t1 values('CSQL1', 'LAKS1','BBSR1',10);
   5 # Insert into t1 values('CSQL1', 'LAKS1','BBSR2',20);
   6 # Insert into t1 values('CSQL1', 'LAKS2','BBSR3',30);
   7 # Insert into t1 values('CSQL1', 'LAKS2','BBSR4',40);
   8 # Insert into t1 values('CSQL2', 'LAKS3','BBSR5',50);
   9 # Insert into t1 values('CSQL2', 'LAKS3','BBSR6',60);
  10 # Insert into t1 values('CSQL2', 'LAKS4','BBSR7',70);
  11 # Insert into t1 values('CSQL2', 'LAKS4','BBSR8',80);
  12 # Test the below queries return proper result set or not.
  13 # select * from t1;
  14 # update t1 set f3='Orissa1',f4=100 where f3='BBSR1' and f1='CSQL1';
  15 # update t1 set f3='Orissa1',f4=100 where not(f3='BBSR1') and f1='CSQL2';
  16 # update t1 set f3='Orissa2',f4=200 where f3='BBSR2' or f1='CSQL1';
  17 # update t1 set f3='Orissa3',f4=300 where f1='CSQL1' and f3 like '__issa2';
  18 # update t1 set f3='Orissa4',f4=400 where f2='LAKS2' and f3 like '_r%3';
  19 # update t1 set f3='Orissa4',f4=400 where not(f3='Orissa1') or f2 in ('LAKS3','LAKS5');
  20 # update t1 set f3='Orissa4',f4=400 where not(f3='Orissa1') or f2 in ('LAKS3',NULL);
  21 # update t1 set f3='Orissa5',f2='LAKS5' where f1='CSQL1' and f3 like 'O_is_a4';
  22 #
  23 CREATEFILE=${PWD}/sql/Predicate/csql.conf
  24 REL_PATH=.
  25 if [ -s "$CREATEFILE" ]
  26 then
  27     REL_PATH=`pwd`/sql/Predicate
  28 fi
  29
  30 echo "drop table t1;" >${REL_PATH}/dropt1.sql
  31
  32 $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/dropt1.sql >/dev/null 2>&1
  33
  34 if [ "$VALGRIND" = "true" ]
  35 then
  36     valgrind --tool=memcheck --leak-check=full $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/varchar_logical_upd.sql
  37 else
  38     $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/varchar_logical_upd.sql
  39     if [ $? -ne 0 ]
  40     then
  41        $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/dropt1.sql
  42        rm -f ${REL_PATH}/dropt1.sql
  43        exit 1;
  44     fi
  45 fi
  46
  47 $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/dropt1.sql
  48 if [ $? -ne 0 ]
  49 then
  50    exit 2;
  51 fi
  52
  53 rm -f ${REL_PATH}/dropt1.sql
  54 exit 0;