github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/tkt-a7debbe0.test (about)

     1  # 2019 September 10
     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. In particular,
    12  # that problems related to ticket a7debbe0ad1 have been fixed.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  set testprefix tkt-a7debbe0
    18  
    19  foreach tn {1 2} {
    20    reset_db
    21    if {$tn==1} {
    22      # Disable the flattener
    23      optimization_control db query-flattener 0
    24    } else {
    25      # Enable the flattener
    26      optimization_control db query-flattener 1
    27    }
    28  
    29    do_execsql_test $tn.1.0 {
    30      CREATE TABLE t0(xyz INTEGER);
    31      INSERT INTO t0(xyz) VALUES(456);
    32      CREATE VIEW v2(a, B) AS 
    33          SELECT 'a', 'B' COLLATE NOCASE FROM t0;
    34      CREATE TABLE t2(a, B COLLATE NOCASE);
    35      INSERT INTO t2 VALUES('a', 'B');
    36      CREATE VIEW v3(a, B) AS
    37          SELECT 'a' COLLATE BINARY, 'B' COLLATE NOCASE FROM t0;
    38  
    39      CREATE VIEW v4(a, B) AS
    40          SELECT 'a', +CAST('B' COLLATE NOCASE AS TEXT) FROM t0;
    41  
    42      CREATE VIEW v5(a, B) AS
    43          SELECT 'a', ('B' COLLATE NOCASE) || '' FROM t0;
    44    }
    45  
    46    # Table t2 and views v2 through v5 should all be equivalent.
    47    do_execsql_test $tn.1.1.1 { SELECT a   >= B FROM t2;         } 1
    48    do_execsql_test $tn.1.1.2 { SELECT 'a' >= 'B' COLLATE NOCASE } 0
    49    do_execsql_test $tn.1.1.3 { SELECT a   >= B FROM v2          } 1
    50    do_execsql_test $tn.1.1.4 { SELECT a   >= B FROM v3          } 1
    51    do_execsql_test $tn.1.1.5 { SELECT a   >= B FROM v4          } 1
    52    do_execsql_test $tn.1.1.6 { SELECT a   >= B FROM v5          } 1
    53  
    54    do_execsql_test $tn.1.2.1 { SELECT B   < a FROM t2           } 0
    55    do_execsql_test $tn.1.2.2 { SELECT 'B' COLLATE NOCASE < 'a'  } 0
    56    do_execsql_test $tn.1.2.3 { SELECT B   < a FROM v2           } 0
    57    do_execsql_test $tn.1.2.4 { SELECT B   < a FROM v3           } 0
    58    do_execsql_test $tn.1.2.5 { SELECT a  < B FROM v4           } 0
    59    do_execsql_test $tn.1.2.6 { SELECT a  < B FROM v5           } 0
    60  
    61    #-------------------------------------------------------------------------
    62    do_execsql_test $tn.2.0 {
    63      CREATE TABLE t5(a, b COLLATE NOCASE);
    64      INSERT INTO t5 VALUES(1, 'XYZ');
    65    }
    66  
    67    # Result should be 0, as column "xyz" from the sub-query has implicit
    68    # collation sequence BINARY.
    69    do_execsql_test $tn.2.1 {
    70      SELECT xyz==b FROM ( SELECT a, 'xyz' AS xyz FROM t5 ), t5;
    71    } {0}
    72  
    73    # Result should be 1, as literal 'xyz' has no collation sequence, so
    74    # the comparison uses the implicit collation sequence of the RHS - NOCASE.
    75    do_execsql_test $tn.2.2 {
    76      SELECT 'xyz'==b FROM ( SELECT a, 'xyz' AS xyz FROM t5 ), t5;
    77    } {1}
    78  
    79    #-----------------------------------------------------------------------
    80    # The test case submitted with the ticket.
    81    #
    82    do_execsql_test $tn.3.0 {
    83      DROP TABLE t0;
    84      DROP VIEW v2;
    85  
    86      CREATE TABLE t0(c0);
    87      INSERT INTO t0(c0) VALUES('');
    88      CREATE VIEW v2(c0, c1) AS 
    89          SELECT 'B' COLLATE NOCASE, 'a' FROM t0 ORDER BY t0.c0;
    90      SELECT SUM(count) FROM (
    91        SELECT v2.c1 BETWEEN v2.c0 AND v2.c1 as count FROM v2
    92      );
    93    } 1
    94  
    95    # The result is 1, as the collation used is the implicit collation sequence
    96    # of v2.c1 - BINARY.
    97    do_execsql_test $tn.3.1 {
    98      SELECT v2.c1 BETWEEN v2.c0 AND v2.c1 as count FROM v2;
    99    } 1
   100  }
   101  
   102  finish_test