test/sql/Limit/exp.test002.ksh

   1 echo CREATE TABLE t1(f1 int, f2 char(10), f3 date);
   2 Statement Executed
   3 echo CREATE TABLE t2(f1 int, f2 char(10), f3 date);
   4 Statement Executed
   5 echo INSERT INTO t1 VALUES(1, 'ABCD', '2009-08-01');
   6 Statement Executed: Rows Affected = 1
   7 echo INSERT INTO t1 VALUES(3, 'BCDA', '2009-08-02');
   8 Statement Executed: Rows Affected = 1
   9 echo INSERT INTO t1 VALUES(5, 'CDBA', '2009-08-03');
  10 Statement Executed: Rows Affected = 1
  11 echo INSERT INTO t1 VALUES(9, 'DCBA', '2009-08-09');
  12 Statement Executed: Rows Affected = 1
  13 echo INSERT INTO t1 VALUES(7, 'ACBD', '2009-08-07');
  14 Statement Executed: Rows Affected = 1
  15 echo INSERT INTO t2 VALUES(2, 'PQRS', '2009-08-02');
  16 Statement Executed: Rows Affected = 1
  17 echo INSERT INTO t2 VALUES(3, 'TUVW', '2009-08-03');
  18 Statement Executed: Rows Affected = 1
  19 echo INSERT INTO t2 VALUES(4, 'EFGH', '2009-08-04');
  20 Statement Executed: Rows Affected = 1
  21 echo INSERT INTO t2 VALUES(5, 'IJKL', '2009-08-07');
  22 Statement Executed: Rows Affected = 1
  23 echo INSERT INTO t2 VALUES(6, 'MNOP', '2009-08-08');
  24 Statement Executed: Rows Affected = 1
  25 echo SELECT * FROM t1;
  26 ---------------------------------------------------------
  27         t1.f1   t1.f2   t1.f3
  28 ---------------------------------------------------------
  29         1       ABCD    2009/8/1
  30         3       BCDA    2009/8/2
  31         5       CDBA    2009/8/3
  32         9       DCBA    2009/8/9
  33         7       ACBD    2009/8/7
  34
  35 echo SELECT * FROM t2;
  36 ---------------------------------------------------------
  37         t2.f1   t2.f2   t2.f3
  38 ---------------------------------------------------------
  39         2       PQRS    2009/8/2
  40         3       TUVW    2009/8/3
  41         4       EFGH    2009/8/4
  42         5       IJKL    2009/8/7
  43         6       MNOP    2009/8/8
  44
  45 echo SELECT * FROM t1,t2;
  46 ---------------------------------------------------------
  47         t1.f1   t1.f2   t1.f3   t2.f1   t2.f2   t2.f3
  48 ---------------------------------------------------------
  49         1       ABCD    2009/8/1        2       PQRS    2009/8/2
  50         1       ABCD    2009/8/1        3       TUVW    2009/8/3
  51         1       ABCD    2009/8/1        4       EFGH    2009/8/4
  52         1       ABCD    2009/8/1        5       IJKL    2009/8/7
  53         1       ABCD    2009/8/1        6       MNOP    2009/8/8
  54         3       BCDA    2009/8/2        2       PQRS    2009/8/2
  55         3       BCDA    2009/8/2        3       TUVW    2009/8/3
  56         3       BCDA    2009/8/2        4       EFGH    2009/8/4
  57         3       BCDA    2009/8/2        5       IJKL    2009/8/7
  58         3       BCDA    2009/8/2        6       MNOP    2009/8/8
  59         5       CDBA    2009/8/3        2       PQRS    2009/8/2
  60         5       CDBA    2009/8/3        3       TUVW    2009/8/3
  61         5       CDBA    2009/8/3        4       EFGH    2009/8/4
  62         5       CDBA    2009/8/3        5       IJKL    2009/8/7
  63         5       CDBA    2009/8/3        6       MNOP    2009/8/8
  64         9       DCBA    2009/8/9        2       PQRS    2009/8/2
  65         9       DCBA    2009/8/9        3       TUVW    2009/8/3
  66         9       DCBA    2009/8/9        4       EFGH    2009/8/4
  67         9       DCBA    2009/8/9        5       IJKL    2009/8/7
  68         9       DCBA    2009/8/9        6       MNOP    2009/8/8
  69         7       ACBD    2009/8/7        2       PQRS    2009/8/2
  70         7       ACBD    2009/8/7        3       TUVW    2009/8/3
  71         7       ACBD    2009/8/7        4       EFGH    2009/8/4
  72         7       ACBD    2009/8/7        5       IJKL    2009/8/7
  73         7       ACBD    2009/8/7        6       MNOP    2009/8/8
  74
  75 echo SELECT * FROM t1,t2 LIMIT 5;
  76 ---------------------------------------------------------
  77         t1.f1   t1.f2   t1.f3   t2.f1   t2.f2   t2.f3
  78 ---------------------------------------------------------
  79         1       ABCD    2009/8/1        2       PQRS    2009/8/2
  80         1       ABCD    2009/8/1        3       TUVW    2009/8/3
  81         1       ABCD    2009/8/1        4       EFGH    2009/8/4
  82         1       ABCD    2009/8/1        5       IJKL    2009/8/7
  83         1       ABCD    2009/8/1        6       MNOP    2009/8/8
  84
  85 echo SELECT * FROM t1,t2 LIMIT 5 OFFSET 5;
  86 ---------------------------------------------------------
  87         t1.f1   t1.f2   t1.f3   t2.f1   t2.f2   t2.f3
  88 ---------------------------------------------------------
  89         3       BCDA    2009/8/2        2       PQRS    2009/8/2
  90         3       BCDA    2009/8/2        3       TUVW    2009/8/3
  91         3       BCDA    2009/8/2        4       EFGH    2009/8/4
  92         3       BCDA    2009/8/2        5       IJKL    2009/8/7
  93         3       BCDA    2009/8/2        6       MNOP    2009/8/8
  94
  95 echo SELECT t1.f1, t2.f1 FROM t1 INNER JOIN t2 ON t1.f1 = t2.f1;
  96 ---------------------------------------------------------
  97         t1.f1   t2.f1
  98 ---------------------------------------------------------
  99         3       3
 100         5       5
 101
 102 echo SELECT t1.f1, t2.f1 FROM t1 INNER JOIN t2 ON t1.f1 = t2.f1 LIMIT 2;
 103 ---------------------------------------------------------
 104         t1.f1   t2.f1
 105 ---------------------------------------------------------
 106         3       3
 107         5       5
 108
 109 echo SELECT t1.f1, t2.f1 FROM t1 INNER JOIN t2 ON t1.f1 = t2.f1 LIMIT 2 OFFSET 1;
 110 ---------------------------------------------------------
 111         t1.f1   t2.f1
 112 ---------------------------------------------------------
 113         5       5
 114
 115 echo SELECT t1.f1, t2.f1 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1;
 116 ---------------------------------------------------------
 117         t1.f1   t2.f1
 118 ---------------------------------------------------------
 119         1       NULL
 120         3       3
 121         5       5
 122         9       NULL
 123         7       NULL
 124
 125 echo SELECT t1.f1, t2.f1 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 LIMIT 4;
 126 ---------------------------------------------------------
 127         t1.f1   t2.f1
 128 ---------------------------------------------------------
 129         1       NULL
 130         3       3
 131         5       5
 132         9       NULL
 133
 134 echo SELECT t1.f1, t2.f1 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 LIMIT 3 OFFSET 2;
 135 ---------------------------------------------------------
 136         t1.f1   t2.f1
 137 ---------------------------------------------------------
 138         5       5
 139         9       NULL
 140         7       NULL
 141
 142 Statement Executed
 143 Statement Executed