AryaWu/sqlite

1# 2015-06-022#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.  The12# focus of this file is type affinity in comparison operations.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix affinity218 19do_execsql_test affinity2-100 {20  CREATE TABLE t1(21    xi INTEGER,22    xr REAL,23    xb BLOB,24    xn NUMERIC,25    xt TEXT26  );27  INSERT INTO t1(rowid,xi,xr,xb,xn,xt) VALUES(1,1,1,1,1,1);28  INSERT INTO t1(rowid,xi,xr,xb,xn,xt) VALUES(2,'2','2','2','2','2');29  INSERT INTO t1(rowid,xi,xr,xb,xn,xt) VALUES(3,'03','03','03','03','03');30 31} {}32do_execsql_test affinity2-110 {33  SELECT xi, typeof(xi) FROM t1 ORDER BY rowid;34} {1 integer 2 integer 3 integer}35do_execsql_test affinity2-120 {36  SELECT xr, typeof(xr) FROM t1 ORDER BY rowid;37} {1.0 real 2.0 real 3.0 real}38do_execsql_test affinity2-130 {39  SELECT xb, typeof(xb) FROM t1 ORDER BY rowid;40} {1 integer 2 text 03 text}41do_execsql_test affinity2-140 {42  SELECT xn, typeof(xn) FROM t1 ORDER BY rowid;43} {1 integer 2 integer 3 integer}44do_execsql_test affinity2-150 {45  SELECT xt, typeof(xt) FROM t1 ORDER BY rowid;46} {1 text 2 text 03 text}47 48do_execsql_test affinity2-200 {49  SELECT rowid, xi==xt, xi==xb, xi==+xt FROM t1 ORDER BY rowid;50} {1 1 1 1 2 1 1 1 3 1 1 1}51do_execsql_test affinity2-210 {52  SELECT rowid, xr==xt, xr==xb, xr==+xt FROM t1 ORDER BY rowid;53} {1 1 1 1 2 1 1 1 3 1 1 1}54do_execsql_test affinity2-220 {55  SELECT rowid, xn==xt, xn==xb, xn==+xt FROM t1 ORDER BY rowid;56} {1 1 1 1 2 1 1 1 3 1 1 1}57 58do_execsql_test affinity2-300 {59  SELECT rowid, xt==+xi, xt==xi, xt==xb FROM t1 ORDER BY rowid;60} {1 1 1 0 2 1 1 1 3 0 1 1}61 62#-------------------------------------------------------------------------63do_execsql_test 400 {64  CREATE TABLE ttt(c0, c1);65  CREATE INDEX ii ON ttt(CAST(c0 AS NUMERIC)); 66  INSERT INTO ttt VALUES('abc', '-1');67}68do_execsql_test 410 {69  SELECT * FROM ttt WHERE CAST(c0 AS NUMERIC) > c1 GROUP BY rowid; 70} {abc -1}71do_execsql_test 420 {72  SELECT * FROM ttt INDEXED BY ii WHERE CAST(c0 AS NUMERIC) > c1 GROUP BY rowid;73} {abc -1}74 75do_execsql_test 430 {76  CREATE TABLE t3(a, b, c INTEGER);77  CREATE INDEX t3ac ON t3(a, c-1);78  INSERT INTO t3 VALUES(1, 1, 1);79  INSERT INTO t3 VALUES(2, 1, 0);80  INSERT INTO t3 VALUES(3, 1, 1);81  INSERT INTO t3 VALUES(4, 1, 0);82  INSERT INTO t3 VALUES(5, 1, 1);83}84do_execsql_test 440 {85  SELECT * FROM t3 WHERE c='0' ORDER BY a;86} {2 1 0 4 1 0}87 88# 2019-08-22 ticket https://sqlite.org/src/info/d99f1ffe836c591ac57f89# False positive in sqlite3ExprNeedsNoAffinityChange()90#91do_execsql_test 500 {92  DROP TABLE IF EXISTS t0;93  CREATE TABLE t0(c0 TEXT UNIQUE, c1);94  INSERT INTO t0(c0) VALUES (-1);95  SELECT quote(- x'ce'), quote(t0.c0), quote(- x'ce' >= t0.c0) FROM t0;96} {0 '-1' 1}97do_execsql_test 501 {98  SELECT * FROM t0 WHERE - x'ce' >= t0.c0;99} {-1 {}}100do_execsql_test 502 {101  SELECT quote(+-+x'ce'), quote(t0.c0), quote(+-+x'ce' >= t0.c0) FROM t0;102} {0 '-1' 1}103do_execsql_test 503 {104  SELECT * FROM t0 WHERE +-+x'ce' >= t0.c0;105} {-1 {}}106do_execsql_test 504 {107  SELECT quote(- 'ce'), quote(t0.c0), quote(- 'ce' >= t0.c0) FROM t0;108} {0 '-1' 1}109do_execsql_test 505 {110  SELECT * FROM t0 WHERE - 'ce' >= t0.c0;111} {-1 {}}112do_execsql_test 506 {113  SELECT quote(+-+'ce'), quote(t0.c0), quote(+-+'ce' >= t0.c0) FROM t0;114} {0 '-1' 1}115do_execsql_test 507 {116  SELECT * FROM t0 WHERE +-+'ce' >= t0.c0;117} {-1 {}}118 119# 2019-08-30 ticket https://sqlite.org/src/info/40812aea1fde9594120#121# Due to some differences in floating point computations, these tests do not122# work under valgrind.123#124if {![info exists ::G(valgrind)]} {125  do_execsql_test 600 {126    DROP TABLE IF EXISTS t0;127    CREATE TABLE t0(c0 REAL UNIQUE);128    INSERT INTO t0(c0) VALUES (3175546974276630385);129    SELECT 3175546974276630385 < c0 FROM t0;130  } {1}131  do_execsql_test 601 {132    SELECT 1 FROM t0 WHERE 3175546974276630385 < c0;133  } {1}134}135 136finish_test137