select1.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

  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 {f1 11 t1 abc 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.1 {
  set v [catch {execsql {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
         ORDER BY A.f1, B.f1}} msg]
  lappend v $msg
} {0 {11 11 11 33 33 11 33 33}}
do_test select1-6.9.2 {
  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 {f1 11 f1 11 f1 33 f1 33 f1 11 f1 11 f1 33 f1 33}}

ifcapable compound {
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 {f1 11 f1 22 f1 33 f1 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
} {1 {ORDER BY term number 1 does not match any result column}}

# Ticket #2296
do_test select1-6.20 {
   execsql {
     CREATE TABLE t6(a TEXT, b TEXT);
     INSERT INTO t6 VALUES('a','0');
     INSERT INTO t6 VALUES('b','1');
     INSERT INTO t6 VALUES('c','2');
     INSERT INTO t6 VALUES('d','3');
     SELECT a FROM t6 WHERE b IN 
        (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
                 ORDER BY 1 LIMIT 1)
   }
} {a}
do_test select1-6.21 {
   execsql {
     SELECT a FROM t6 WHERE b IN 
        (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
                 ORDER BY 1 DESC LIMIT 1)
   }
} {d}
do_test select1-6.22 {
   execsql {
     SELECT a FROM t6 WHERE b IN 
        (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
                 ORDER BY b LIMIT 2)
     ORDER BY a;
   }
} {a b}
do_test select1-6.23 {
   execsql {
     SELECT a FROM t6 WHERE b IN 
        (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
                 ORDER BY x DESC LIMIT 2)
     ORDER BY a;
   }
} {b d}

} ;#ifcapable compound

do_test select1-7.1 {
  set v [catch {execsql {
     SELECT f1 FROM test1 WHERE f2=;
  }} msg]
  lappend v $msg
} {1 {near ";": syntax error}}
ifcapable compound {
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}}
} ;# ifcapable compound
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-7.9 {
  catchsql {
     SELECT f1 FROM test1 LIMIT 5+3 OFFSET 11 ORDER BY f2;
  }
} {1 {near "ORDER": 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}

# TODO: This test is failing because f1 is now being loaded off the
# disk as a vdbe integer, not a string. Hence the value of f1/(f1-11)
# changes because of rounding. Disable the test for now.
if 0 {
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
#
# SQLite v3 always sets r(*).
#
# 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}
  catch {unset r}
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<0} r {}
  set r(*)
} {f1 f2}
ifcapable subquery {
  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.1 {
  execsql {
    SELECT * FROM t3, t4;
  }
} {1 2 3 4}
do_test select1-11.2.2 {
  execsql2 {
    SELECT * FROM t3, t4;
  }
} {a 3 b 4 a 3 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.1 {
  execsql2 {
    SELECT t3.*, t4.b FROM t3, t4;
  }
} {a 1 b 4 b 4}
do_test select1-11.6 {
  execsql2 {
    SELECT x.*, y.b FROM t3 AS x, t4 AS y;
  }
} {a 1 b 4 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;
  }
} {b 4 a 3 b 4}
do_test select1-11.9 {
  execsql2 {
    SELECT x.b, y.* FROM t3 AS x, t4 AS y;
  }
} {b 4 a 3 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}}
ifcapable subquery {
  do_test select1-11.12 {
    execsql2 {
      SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)
    }
  } {a 1 b 2}
  do_test select1-11.13 {
    execsql2 {
      SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3
    }
  } {a 1 b 2}
  do_test select1-11.14 {
    execsql2 {
      SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'
    }
  } {a 1 b 2 max(a) 3 max(b) 4}
  do_test select1-11.15 {
    execsql2 {
      SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
    }
  } {max(a) 3 max(b) 4 a 1 b 2}
}
do_test select1-11.16 {
  execsql2 {
    SELECT y.* FROM t3 as y, t4 as z
  }
} {a 1 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);
  }
} {}

ifcapable compound {
do_test select1-12.5 {
  execsql {
    SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;
  }
} {1 2 3 4}

do_test select1-12.6 {
  execsql {
    SELECT 3, 4 UNION SELECT * FROM t3;
  }
} {1 2 3 4}
} ;# ifcapable compound

ifcapable subquery {
  do_test select1-12.7 {
    execsql {
      SELECT * FROM t3 WHERE a=(SELECT 1);
    }
  } {1 2}
  do_test select1-12.8 {
    execsql {
      SELECT * FROM t3 WHERE a=(SELECT 2);
    }
  } {}
}

ifcapable {compound && subquery} {
  do_test select1-12.9 {
    execsql2 {
      SELECT x FROM (
        SELECT a AS x, b AS y FROM t3 UNION SELECT a,b FROM t4 ORDER BY a,b
      ) ORDER BY x;
    }
  } {x 1 x 3}
  do_test select1-12.10 {
    execsql2 {
      SELECT z.x FROM (
        SELECT a AS x,b AS y FROM t3 UNION SELECT a, b FROM t4 ORDER BY a,b
      ) AS 'z' ORDER BY x;
    }
  } {x 1 x 3}
} ;# ifcapable compound


# Check for a VDBE stack growth problem that existed at one point.
#
ifcapable subquery {
  do_test select1-13.1 {
    execsql {
      BEGIN;
      create TABLE abc(a, b, c, PRIMARY KEY(a, b));
      INSERT INTO abc VALUES(1, 1, 1);
    }
    for {set i 0} {$i<10} {incr i} {
      execsql {
        INSERT INTO abc SELECT a+(select max(a) FROM abc), 
            b+(select max(a) FROM abc), c+(select max(a) FROM abc) FROM abc;
      }
    }
    execsql {COMMIT}
  
    # This used to seg-fault when the problem existed.
    execsql {
      SELECT count(
        (SELECT a FROM abc WHERE a = NULL AND b >= upper.c) 
      ) FROM abc AS upper;
    }
  } {0}
}

finish_test