gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/affinity2.test (about)

     1  # 2015-06-02
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # 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.  The
    12  # focus of this file is type affinity in comparison operations.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  set testprefix affinity2
    18  
    19  do_execsql_test affinity2-100 {
    20    CREATE TABLE t1(
    21      xi INTEGER,
    22      xr REAL,
    23      xb BLOB,
    24      xn NUMERIC,
    25      xt TEXT
    26    );
    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  } {}
    32  do_execsql_test affinity2-110 {
    33    SELECT xi, typeof(xi) FROM t1 ORDER BY rowid;
    34  } {1 integer 2 integer 3 integer}
    35  do_execsql_test affinity2-120 {
    36    SELECT xr, typeof(xr) FROM t1 ORDER BY rowid;
    37  } {1.0 real 2.0 real 3.0 real}
    38  do_execsql_test affinity2-130 {
    39    SELECT xb, typeof(xb) FROM t1 ORDER BY rowid;
    40  } {1 integer 2 text 03 text}
    41  do_execsql_test affinity2-140 {
    42    SELECT xn, typeof(xn) FROM t1 ORDER BY rowid;
    43  } {1 integer 2 integer 3 integer}
    44  do_execsql_test affinity2-150 {
    45    SELECT xt, typeof(xt) FROM t1 ORDER BY rowid;
    46  } {1 text 2 text 03 text}
    47  
    48  do_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}
    51  do_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}
    54  do_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  
    58  do_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  #-------------------------------------------------------------------------
    63  do_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  }
    68  do_execsql_test 410 {
    69    SELECT * FROM ttt WHERE CAST(c0 AS NUMERIC) > c1 GROUP BY rowid; 
    70  } {abc -1}
    71  do_execsql_test 420 {
    72    SELECT * FROM ttt INDEXED BY ii WHERE CAST(c0 AS NUMERIC) > c1 GROUP BY rowid;
    73  } {abc -1}
    74  
    75  do_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  }
    84  do_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/d99f1ffe836c591ac57f
    89  # False positive in sqlite3ExprNeedsNoAffinityChange()
    90  #
    91  do_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}
    97  do_execsql_test 501 {
    98    SELECT * FROM t0 WHERE - x'ce' >= t0.c0;
    99  } {-1 {}}
   100  do_execsql_test 502 {
   101    SELECT quote(+-+x'ce'), quote(t0.c0), quote(+-+x'ce' >= t0.c0) FROM t0;
   102  } {0 '-1' 1}
   103  do_execsql_test 503 {
   104    SELECT * FROM t0 WHERE +-+x'ce' >= t0.c0;
   105  } {-1 {}}
   106  do_execsql_test 504 {
   107    SELECT quote(- 'ce'), quote(t0.c0), quote(- 'ce' >= t0.c0) FROM t0;
   108  } {0 '-1' 1}
   109  do_execsql_test 505 {
   110    SELECT * FROM t0 WHERE - 'ce' >= t0.c0;
   111  } {-1 {}}
   112  do_execsql_test 506 {
   113    SELECT quote(+-+'ce'), quote(t0.c0), quote(+-+'ce' >= t0.c0) FROM t0;
   114  } {0 '-1' 1}
   115  do_execsql_test 507 {
   116    SELECT * FROM t0 WHERE +-+'ce' >= t0.c0;
   117  } {-1 {}}
   118   
   119  # 2019-08-30 ticket https://www.sqlite.org/src/info/40812aea1fde9594
   120  #
   121  # Due to some differences in floating point computations, these tests do not
   122  # work under valgrind.
   123  #
   124  if {![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  
   136  finish_test