select1.test

sqlite数据库源码

} {0 {f1 11 f2 22}}
do_test select1-6.1.6 {
  set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg
} {0 {f1 11 f2 22}}
do_test select1-6.2 {
  set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {xyzzy 11 xyzzy 33}}
do_test select1-6.3 {
  set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {xyzzy 11 xyzzy 33}}
do_test select1-6.3.1 {
  set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {{xyzzy } 11 {xyzzy } 33}}
do_test select1-6.4 {
  set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {xyzzy 33 xyzzy 77}}
do_test select1-6.4a {
  set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {f1+F2 33 f1+F2 77}}
do_test select1-6.5 {
  set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {test1.f1+F2 33 test1.f1+F2 77}}
do_test select1-6.5.1 {
  execsql2 {PRAGMA full_column_names=on}
  set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
  execsql2 {PRAGMA full_column_names=off}
  lappend v $msg
} {0 {test1.f1+F2 33 test1.f1+F2 77}}
do_test select1-6.6 {
  set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 
         ORDER BY f2}} msg]
  lappend v $msg
} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}}
do_test select1-6.7 {
  set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 
         ORDER BY f2}} msg]
  lappend v $msg
} {0 {A.f1 11 t1 abc A.f1 33 t1 abc}}
do_test select1-6.8 {
  set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 
         ORDER BY f2}} msg]
  lappend v $msg
} {1 {ambiguous column name: f1}}
do_test select1-6.8b {
  set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
         ORDER BY f2}} msg]
  lappend v $msg
} {1 {ambiguous column name: f2}}
do_test select1-6.8c {
  set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 
         ORDER BY f2}} msg]
  lappend v $msg
} {1 {ambiguous column name: A.f1}}
do_test select1-6.9 {
  set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
         ORDER BY A.f1, B.f1}} msg]
  lappend v $msg
} {0 {A.f1 11 B.f1 11 A.f1 11 B.f1 33 A.f1 33 B.f1 11 A.f1 33 B.f1 33}}
do_test select1-6.10 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2 FROM test1
    ORDER BY f2;
  }} msg]
  lappend v $msg
} {0 {f2 11 f2 22 f2 33 f2 44}}
do_test select1-6.11 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+100;
  }} msg]
  lappend v $msg
} {0 {f2+100 11 f2+100 33 f2+100 122 f2+100 144}}

do_test select1-7.1 {
  set v [catch {execsql {
     SELECT f1 FROM test1 WHERE f2=;
  }} msg]
  lappend v $msg
} {1 {near ";": syntax error}}
do_test select1-7.2 {
  set v [catch {execsql {
     SELECT f1 FROM test1 UNION SELECT WHERE;
  }} msg]
  lappend v $msg
} {1 {near "WHERE": syntax error}}
do_test select1-7.3 {
  set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg]
  lappend v $msg
} {1 {near "as": syntax error}}
do_test select1-7.4 {
  set v [catch {execsql {
     SELECT f1 FROM test1 ORDER BY;
  }} msg]
  lappend v $msg
} {1 {near ";": syntax error}}
do_test select1-7.5 {
  set v [catch {execsql {
     SELECT f1 FROM test1 ORDER BY f1 desc, f2 where;
  }} msg]
  lappend v $msg
} {1 {near "where": syntax error}}
do_test select1-7.6 {
  set v [catch {execsql {
     SELECT count(f1,f2 FROM test1;
  }} msg]
  lappend v $msg
} {1 {near "FROM": syntax error}}
do_test select1-7.7 {
  set v [catch {execsql {
     SELECT count(f1,f2+) FROM test1;
  }} msg]
  lappend v $msg
} {1 {near ")": syntax error}}
do_test select1-7.8 {
  set v [catch {execsql {
     SELECT f1 FROM test1 ORDER BY f2, f1+;
  }} msg]
  lappend v $msg
} {1 {near ";": syntax error}}

do_test select1-8.1 {
  execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1}
} {11 33}
do_test select1-8.2 {
  execsql {
    SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20'
    ORDER BY f1
  }
} {11}
do_test select1-8.3 {
  execsql {
    SELECT f1 FROM test1 WHERE 5-3==2
    ORDER BY f1
  }
} {11 33}
do_test select1-8.4 {
  execsql {
    SELECT coalesce(f1/(f1-11),'x'),
           coalesce(min(f1/(f1-11),5),'y'),
           coalesce(max(f1/(f1-33),6),'z')
    FROM test1 ORDER BY f1
  }
} {x y 6 1.5 1.5 z}
do_test select1-8.5 {
  execsql {
    SELECT min(1,2,3), -max(1,2,3)
    FROM test1 ORDER BY f1
  }
} {1 -3 1 -3}


# Check the behavior when the result set is empty
#
do_test select1-9.1 {
  catch {unset r}
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<0} r {}
  set r(*)
} {}
do_test select1-9.2 {
  execsql {PRAGMA empty_result_callbacks=on}
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<0} r {}
  set r(*)
} {f1 f2}
do_test select1-9.3 {
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}
  set r(*)
} {f1 f2}
do_test select1-9.4 {
  set r(*) {}
  db eval {SELECT * FROM test1 ORDER BY f1} r {}
  set r(*)
} {f1 f2}
do_test select1-9.5 {
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {}
  set r(*)
} {f1 f2}
unset r

# Check for ORDER BY clauses that refer to an AS name in the column list
#
do_test select1-10.1 {
  execsql {
    SELECT f1 AS x FROM test1 ORDER BY x
  }
} {11 33}
do_test select1-10.2 {
  execsql {
    SELECT f1 AS x FROM test1 ORDER BY -x
  }
} {33 11}
do_test select1-10.3 {
  execsql {
    SELECT f1-23 AS x FROM test1 ORDER BY abs(x)
  }
} {10 -12}
do_test select1-10.4 {
  execsql {
    SELECT f1-23 AS x FROM test1 ORDER BY -abs(x)
  }
} {-12 10}
do_test select1-10.5 {
  execsql {
    SELECT f1-22 AS x, f2-22 as y FROM test1
  }
} {-11 0 11 22}
do_test select1-10.6 {
  execsql {
    SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50
  }
} {11 22}

# Check the ability to specify "TABLE.*" in the result set of a SELECT
#
do_test select1-11.1 {
  execsql {
    DELETE FROM t3;
    DELETE FROM t4;
    INSERT INTO t3 VALUES(1,2);
    INSERT INTO t4 VALUES(3,4);
    SELECT * FROM t3, t4;
  }
} {1 2 3 4}
do_test select1-11.2 {
  execsql2 {
    SELECT * FROM t3, t4;
  }
} {t3.a 1 t3.b 2 t4.a 3 t4.b 4}
do_test select1-11.3 {
  execsql2 {
    SELECT * FROM t3 AS x, t4 AS y;
  }
} {x.a 1 x.b 2 y.a 3 y.b 4}
do_test select1-11.4.1 {
  execsql {
    SELECT t3.*, t4.b FROM t3, t4;
  }
} {1 2 4}
do_test select1-11.4.2 {
  execsql {
    SELECT "t3".*, t4.b FROM t3, t4;
  }
} {1 2 4}
do_test select1-11.5 {
  execsql2 {
    SELECT t3.*, t4.b FROM t3, t4;
  }
} {t3.a 1 t3.b 2 t4.b 4}
do_test select1-11.6 {
  execsql2 {
    SELECT x.*, y.b FROM t3 AS x, t4 AS y;
  }
} {x.a 1 x.b 2 y.b 4}
do_test select1-11.7 {
  execsql {
    SELECT t3.b, t4.* FROM t3, t4;
  }
} {2 3 4}
do_test select1-11.8 {
  execsql2 {
    SELECT t3.b, t4.* FROM t3, t4;
  }
} {t3.b 2 t4.a 3 t4.b 4}
do_test select1-11.9 {
  execsql2 {
    SELECT x.b, y.* FROM t3 AS x, t4 AS y;
  }
} {x.b 2 y.a 3 y.b 4}
do_test select1-11.10 {
  catchsql {
    SELECT t5.* FROM t3, t4;
  }
} {1 {no such table: t5}}
do_test select1-11.11 {
  catchsql {
    SELECT t3.* FROM t3 AS x, t4;
  }
} {1 {no such table: t3}}
do_test select1-11.12 {
  execsql2 {
    SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)
  }
} {t3.a 1 t3.b 2}
do_test select1-11.13 {
  execsql2 {
    SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3
  }
} {t3.a 1 t3.b 2}
do_test select1-11.14 {
  execsql2 {
    SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'
  }
} {t3.a 1 t3.b 2 tx.max(a) 3 tx.max(b) 4}
do_test select1-11.15 {
  execsql2 {
    SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
  }
} {y.max(a) 3 y.max(b) 4 t3.a 1 t3.b 2}
do_test select1-11.16 {
  execsql2 {
    SELECT y.* FROM t3 as y, t4 as z
  }
} {y.a 1 y.b 2}

# Tests of SELECT statements without a FROM clause.
#
do_test select1-12.1 {
  execsql2 {
    SELECT 1+2+3
  }
} {1+2+3 6}
do_test select1-12.2 {
  execsql2 {
    SELECT 1,'hello',2
  }
} {1 1 'hello' hello 2 2}
do_test select1-12.3 {
  execsql2 {
    SELECT 1 AS 'a','hello' AS 'b',2 AS 'c'
  }
} {a 1 b hello c 2}
do_test select1-12.4 {
  execsql {
    DELETE FROM t3;
    INSERT INTO t3 VALUES(1,2);
    SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;
  }
} {1 2 3 4}
do_test select1-12.5 {
  execsql {
    SELECT 3, 4 UNION SELECT * FROM t3;
  }
} {1 2 3 4}
do_test select1-12.6 {
  execsql {
    SELECT * FROM t3 WHERE a=(SELECT 1);
  }
} {1 2}
do_test select1-12.7 {
  execsql {
    SELECT * FROM t3 WHERE a=(SELECT 2);
  }
} {}
do_test select1-12.8 {
  execsql2 {
    SELECT x FROM (
      SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b
    ) ORDER BY x;
  }
} {x 1 x 3}
do_test select1-12.9 {
  execsql2 {
    SELECT z.x FROM (
      SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b
    ) AS 'z' ORDER BY x;
  }
} {z.x 1 z.x 3}


finish_test