test/tools/csql/exp.test141.ksh

   1 echo CREATE TABLE t1(f1 INT, f2 CHAR(10), f3 DOUBLE, f4 DATE, f5 TIMESTAMP);
   2 Statement Executed
   3 Statement Executed: Rows Affected = 1
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed: Rows Affected = 1
   8 Statement Executed: Rows Affected = 1
   9 Statement Executed: Rows Affected = 1
  10 Statement Executed: Rows Affected = 1
  11 Statement Executed: Rows Affected = 1
  12 Statement Executed: Rows Affected = 1
  13 Statement Executed: Rows Affected = 1
  14 Statement Executed: Rows Affected = 1
  15 Statement Executed: Rows Affected = 1
  16 echo SELECT * FROM t1;
  17 ---------------------------------------------------------
  18         t1.f1   t1.f2   t1.f3   t1.f4   t1.f5
  19 ---------------------------------------------------------
  20         90000   Pitter  90000.555555    2009/7/30       2009/7/29 11:29:0.0
  21         60000   Simran  60000.555555    2006/7/30       2006/7/29 11:29:0.0
  22         40000   Jemini  40000.555555    2004/7/30       NULL
  23         60000   PRITAM  60000.555555    2006/7/30       2006/7/29 11:29:0.0
  24         65000   PITTER  65000.555555    2065/7/30       2065/7/29 11:29:0.0
  25         40000   JASMIN  40000.555555    2004/7/30       NULL
  26         45000   jasmin  45000.555555    2045/7/30       2045/7/29 11:29:0.0
  27         60000   Pitter  60000.555555    2006/7/30       NULL
  28         40000   kishor  40000.555555    2004/7/30       2004/7/29 11:29:0.0
  29         50000   Jemini  50000.555555    2005/7/30       2004/8/29 11:29:0.0
  30         40000   Jemini  60000.555555    2006/7/30       2004/7/29 11:29:0.0
  31         50000   Jemini  70000.555555    NULL    2004/6/29 11:29:0.0
  32         40000   Sitala  80000.555555    2008/7/30       2004/5/29 11:29:0.0
  33
  34 echo SELECT * FROM t1 WHERE f1 = 40000 OR f1 <= 50000 OR f2 = 'Jemini';
  35 ---------------------------------------------------------
  36         t1.f1   t1.f2   t1.f3   t1.f4   t1.f5
  37 ---------------------------------------------------------
  38         40000   Jemini  40000.555555    2004/7/30       NULL
  39         40000   JASMIN  40000.555555    2004/7/30       NULL
  40         45000   jasmin  45000.555555    2045/7/30       2045/7/29 11:29:0.0
  41         40000   kishor  40000.555555    2004/7/30       2004/7/29 11:29:0.0
  42         50000   Jemini  50000.555555    2005/7/30       2004/8/29 11:29:0.0
  43         40000   Jemini  60000.555555    2006/7/30       2004/7/29 11:29:0.0
  44         50000   Jemini  70000.555555    NULL    2004/6/29 11:29:0.0
  45         40000   Sitala  80000.555555    2008/7/30       2004/5/29 11:29:0.0
  46
  47 echo SELECT * FROM t1 WHERE f1 = 40000 OR f1 <= 50000 OR f2 = 'Jemini' OR f2 LIKE 'Pi%';
  48 ---------------------------------------------------------
  49         t1.f1   t1.f2   t1.f3   t1.f4   t1.f5
  50 ---------------------------------------------------------
  51         90000   Pitter  90000.555555    2009/7/30       2009/7/29 11:29:0.0
  52         40000   Jemini  40000.555555    2004/7/30       NULL
  53         40000   JASMIN  40000.555555    2004/7/30       NULL
  54         45000   jasmin  45000.555555    2045/7/30       2045/7/29 11:29:0.0
  55         60000   Pitter  60000.555555    2006/7/30       NULL
  56         40000   kishor  40000.555555    2004/7/30       2004/7/29 11:29:0.0
  57         50000   Jemini  50000.555555    2005/7/30       2004/8/29 11:29:0.0
  58         40000   Jemini  60000.555555    2006/7/30       2004/7/29 11:29:0.0
  59         50000   Jemini  70000.555555    NULL    2004/6/29 11:29:0.0
  60         40000   Sitala  80000.555555    2008/7/30       2004/5/29 11:29:0.0
  61
  62 echo SELECT * FROM t1 WHERE (f1 = 40000 OR f1 <= 50000 OR f2 = 'Jemini' OR f2 LIKE 'Pi%') AND f3 BETWEEN 44999 AND 89999;
  63 ---------------------------------------------------------
  64         t1.f1   t1.f2   t1.f3   t1.f4   t1.f5
  65 ---------------------------------------------------------
  66         45000   jasmin  45000.555555    2045/7/30       2045/7/29 11:29:0.0
  67         60000   Pitter  60000.555555    2006/7/30       NULL
  68         50000   Jemini  50000.555555    2005/7/30       2004/8/29 11:29:0.0
  69         40000   Jemini  60000.555555    2006/7/30       2004/7/29 11:29:0.0
  70         50000   Jemini  70000.555555    NULL    2004/6/29 11:29:0.0
  71         40000   Sitala  80000.555555    2008/7/30       2004/5/29 11:29:0.0
  72
  73 echo SELECT * FROM t1 WHERE (f1 = 40000 OR f1 <= 50000 OR f2 = 'Jemini' OR f2 LIKE 'Pi%') AND (f3 BETWEEN 44999 AND 89999) AND f4 IN ('2045-07-30', '2006-07-30', '2007-07-30');
  74 ---------------------------------------------------------
  75         t1.f1   t1.f2   t1.f3   t1.f4   t1.f5
  76 ---------------------------------------------------------
  77         45000   jasmin  45000.555555    2045/7/30       2045/7/29 11:29:0.0
  78         60000   Pitter  60000.555555    2006/7/30       NULL
  79         40000   Jemini  60000.555555    2006/7/30       2004/7/29 11:29:0.0
  80
  81 echo SELECT * FROM t1 WHERE (f1 = 40000 OR f1 <= 50000 OR f2 = 'Jemini' OR f2 LIKE 'Pi%') AND (f3 BETWEEN 44999 AND 89999) AND (f4 IN ('2045-07-30', '2006-07-30', '2007-07-30') AND f5 IS NOT NULL);
  82 ---------------------------------------------------------
  83         t1.f1   t1.f2   t1.f3   t1.f4   t1.f5
  84 ---------------------------------------------------------
  85         45000   jasmin  45000.555555    2045/7/30       2045/7/29 11:29:0.0
  86         40000   Jemini  60000.555555    2006/7/30       2004/7/29 11:29:0.0
  87
  88 echo SELECT * FROM t1 WHERE (f1 = 40000 OR f1 <= 50000 OR f2 = 'Jemini' OR f2 LIKE 'Pi%') AND (f3 BETWEEN 44999 AND 89999) AND (f4 IN ('2045-07-30', '2006-07-30', '2007-07-30') AND f4 IS NULL AND f5 IS NOT NULL);
  89 ---------------------------------------------------------
  90         t1.f1   t1.f2   t1.f3   t1.f4   t1.f5
  91 ---------------------------------------------------------
  92
  93 Statement Executed