test/sqlapi/Csql/DDLStmt/test009.ksh

   1 #!/bin/ksh
   2 # Composite tree index testing
   3 # CREATE TABLE t1(f1 TINYINT ,f2 SMALLINT ,f3 INT ,f4 BIGINT ,f5 CHAR(20) ,f6 VARCHAR(30) ,f7 FLOAT ,f8 DOUBLE ,f9 DATE ,f10 TIME ,f11 TIMESTAMP);
   4
   5 # CREATE INDEX idxtinyint_smallint ON t1(f1,f2) HASH;
   6 # CREATE INDEX idxtinyint_int ON t1(f1,f3) HASH;
   7 # CREATE INDEX idxtinyint_bigint ON t1(f1,f4) HASH;
   8 # CREATE INDEX idxtinyint_char ON t1(f1,f5) HASH;
   9 # CREATE INDEX idxtinyint_varchar ON t1(f1,f6) HASH;
  10 # CREATE INDEX idxtinyint_date ON t1(f1,f9) HASH;
  11 # CREATE INDEX idxtinyint_time ON t1(f1,f10) HASH;
  12
  13 # CREATE INDEX idxsmallint_tinyint ON t1(f2,f1) HASH; should fail
  14 # CREATE INDEX idxsmallint_int ON t1(f2,f3) HASH;
  15 # CREATE INDEX idxsmallint_bigint ON t1(f2,f4) HASH;
  16 # CREATE INDEX idxsmallint_char ON t1(f2,f5) HASH;
  17 # CREATE INDEX idxsmallint_varchar ON t1(f2,f6) HASH;
  18 # CREATE INDEX idxsmallint_date ON t1(f2,f9) HASH;
  19 # CREATE INDEX idxsmallint_time ON t1(f2,f10) HASH;
  20
  21 # CREATE INDEX idxint_tinyint ON t1(f3,f1) HASH; should fail
  22 # CREATE INDEX idxint_smallint ON t1(f3,f2) HASH; should fail
  23 # CREATE INDEX idxint_bigint ON t1(f3,f4) HASH;
  24 # CREATE INDEX idxint_char ON t1(f3,f5) HASH;
  25 # CREATE INDEX idxint_varchar ON t1(f3,f6) HASH;
  26 # CREATE INDEX idxint_date ON t1(f3,f9) HASH;
  27 # CREATE INDEX idxint_time ON t1(f3,f10) HASH;
  28
  29 # CREATE INDEX idxbigint_tinyint ON t1(f4,f1) HASH; should fail
  30 # CREATE INDEX idxbigint_smallint ON t1(f4,f2) HASH; should fail
  31 # CREATE INDEX idxbigint_int ON t1(f4,f3) HASH; should fail
  32 # CREATE INDEX idxbigint_char ON t1(f4,f5) HASH;
  33 # CREATE INDEX idxbigint_varchar ON t1(f4,f6) HASH;
  34 # CREATE INDEX idxbigint_date ON t1(f4,f9) HASH;
  35 # CREATE INDEX idxbigint_time ON t1(f4,f10) HASH;
  36
  37 # CREATE INDEX idxchar_tinyint ON t1(f5,f1) HASH; should fail
  38 # CREATE INDEX idxchar_smallint ON t1(f5,f2) HASH; should fail
  39 # CREATE INDEX idxchar_int ON t1(f5,f3) HASH; should fail
  40 # CREATE INDEX idxchar_bigint ON t1(f5,f4) HASH; should fail
  41 # CREATE INDEX idxchar_varchar ON t1(f5,f6) HASH;
  42 # CREATE INDEX idxchar_date ON t1(f5,f9) HASH;
  43 # CREATE INDEX idxchar_time ON t1(f5,f10) HASH;
  44
  45 # CREATE INDEX idxvarchar_tinyint ON t1(f6,f1) HASH; should fail
  46 # CREATE INDEX idxvarchar_smallint ON t1(f6,f2) HASH; should fail
  47 # CREATE INDEX idxvarchar_int ON t1(f6,f3) HASH; should fail
  48 # CREATE INDEX idxvarchar_bigint ON t1(f6,f4) HASH; should fail
  49 # CREATE INDEX idxvarchar_char ON t1(f6,f5) HASH; should fail
  50 # CREATE INDEX idxvarchar_date ON t1(f6,f9) HASH;
  51 # CREATE INDEX idxvarchar_time ON t1(f6,f10) HASH;
  52
  53 # CREATE INDEX idxdate_tinyint ON t1(f9,f1) HASH; should fail
  54 # CREATE INDEX idxdate_smallint ON t1(f9,f2) HASH; should fail
  55 # CREATE INDEX idxdate_int ON t1(f9,f3) HASH; should fail
  56 # CREATE INDEX idxdate_bigint ON t1(f9,f4) HASH; should fail
  57 # CREATE INDEX idxdate_char ON t1(f9,f5) HASH; should fail
  58 # CREATE INDEX idxdate_date ON t1(f9,f6) HASH; should fail
  59 # CREATE INDEX idxdate_time ON t1(f9,f10) HASH;
  60
  61 # CREATE INDEX idxtime_tinyint ON t1(f10,f1) HASH; should fail
  62 # CREATE INDEX idxtime_smallint ON t1(f10,f2) HASH; should fail
  63 # CREATE INDEX idxtime_int ON t1(f10,f3) HASH; should fail
  64 # CREATE INDEX idxtime_bigint ON t1(f10,f4) HASH; should fail
  65 # CREATE INDEX idxtime_char ON t1(f10,f5) HASH; should fail
  66 # CREATE INDEX idxtime_date ON t1(f10,f6) HASH; should fail
  67 # CREATE INDEX idxtime_time ON t1(f10,f9) HASH; should fail
  68
  69 # CREATE INDEX idxtinyint_smallint_int ON t1(f1,f2,f3) HASH;
  70 # CREATE INDEX idxtinyint_smallint_int_bigint ON t1(f1,f2,f3,f4) HASH;
  71 # CREATE INDEX idxtinyint_smallint_int_bigint_char ON t1(f1,f2,f3,f4,f5) HASH;
  72 # CREATE INDEX idxtinyint_smallint_int_bigint_char_varchar ON t1(f1,f2,f3,f4,f5,f6) HASH;
  73 # CREATE INDEX idxtinyint_smallint_int_bigint_char_varchar_date ON t1(f1,f2,f3,f4,f5,f6,f9) HASH;
  74 # CREATE INDEX idxtinyint_smallint_int_bigint_char_varchar_date_time ON t1(f1,f2,f3,f4,f5,f6,f9,f10) HASH;
  75
  76 FILE=${PWD}/sqlapi/Csql/DDLStmt/compositehashonalldatatype
  77 REL_PATH=.
  78 if [ -s "$FILE" ]
  79 then
  80     REL_PATH=`pwd`/sqlapi/Csql/DDLStmt
  81 fi
  82
  83 if [ "$VALGRIND" = "true" ]
  84 then
  85     valgrind --tool=memcheck --leak-check=full ${REL_PATH}/compositehashonalldatatype
  86 else
  87     $REL_PATH/compositehashonalldatatype
  88 fi
  89
  90 if [ "$VALGRIND" = "true" ]
  91 then
  92     valgrind --tool=memcheck --leak-check=full $CSQL_INSTALL_ROOT/bin/catalog -u root -p manager -l
  93 else
  94    $CSQL_INSTALL_ROOT/bin/catalog -u root -p manager -l
  95 fi
  96
  97 echo "echo drop table t1;" >${REL_PATH}/drop_t1.sql
  98 echo "drop table t1;" >>${REL_PATH}/drop_t1.sql
  99
 100 $CSQL_INSTALL_ROOT/bin/csql -u root -p manager -s ${REL_PATH}/drop_t1.sql
 101 rm -f ${REL_PATH}/drop_t1.sql
 102 exit 0