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

     1  # 2017-01-16
     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  #
    12  # Test cases for bugs:
    13  #
    14  #    https://www.sqlite.org/src/info/91e2e8ba6ff2e2
    15  #    https://www.sqlite.org/src/info/7ffd1ca1d2ad4ecf
    16  #
    17  
    18  set testdir [file dirname $argv0]
    19  source $testdir/tester.tcl
    20  
    21  # Ticket https://www.sqlite.org/src/info/91e2e8ba6ff2e2 (2011-09-19)
    22  # Automatic index causes undesired type conversions
    23  #
    24  do_execsql_test affinity3-100 {
    25    CREATE TABLE customer (id INT PRIMARY KEY);
    26    CREATE TABLE apr (id INT PRIMARY KEY, apr REAL);
    27    
    28    CREATE VIEW v1 AS
    29    SELECT c.id, i.apr
    30    FROM customer c
    31    LEFT JOIN apr i ON i.id=c.id;
    32    
    33    CREATE VIEW v2 AS
    34    SELECT c.id, v1.apr
    35    FROM customer c
    36    LEFT JOIN v1 ON v1.id=c.id;
    37    
    38    INSERT INTO customer (id) VALUES (1);
    39    INSERT INTO apr (id, apr) VALUES (1, 12);
    40    INSERT INTO customer (id) VALUES (2);
    41    INSERT INTO apr (id, apr) VALUES (2, 12.01);
    42  }
    43  do_execsql_test affinity3-110 {
    44    PRAGMA automatic_index=ON;
    45    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
    46  } {1 0.12 real 2 0.1201 real}
    47  do_execsql_test affinity3-120 {
    48    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;
    49  } {1 0.12 real 2 0.1201 real}
    50  do_execsql_test affinity3-130 {
    51    PRAGMA automatic_index=OFF;
    52    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
    53  } {1 0.12 real 2 0.1201 real}
    54  do_execsql_test affinity3-140 {
    55    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;
    56  } {1 0.12 real 2 0.1201 real}
    57  
    58  # Ticket https://www.sqlite.org/src/info/7ffd1ca1d2ad4ecf  (2017-01-16)
    59  # Incorrect affinity when using automatic indexes 
    60  #
    61  do_execsql_test affinity3-200 {
    62    CREATE TABLE map_integer (id INT, name);
    63    INSERT INTO map_integer VALUES(1,'a');
    64    CREATE TABLE map_text (id TEXT, name);
    65    INSERT INTO map_text VALUES('4','e');
    66    CREATE TABLE data (id TEXT, name);
    67    INSERT INTO data VALUES(1,'abc');
    68    INSERT INTO data VALUES('4','xyz');
    69    CREATE VIEW idmap as
    70        SELECT * FROM map_integer
    71        UNION SELECT * FROM map_text;
    72    CREATE TABLE mzed AS SELECT * FROM idmap;
    73  }
    74  
    75  #do_execsql_test affinity3-210 {
    76    #PRAGMA automatic_index=ON;
    77    #SELECT * FROM data JOIN idmap USING(id);
    78  #} {1 abc a 4 xyz e}
    79  do_execsql_test affinity3-220 {
    80    SELECT * FROM data JOIN mzed USING(id);
    81  } {1 abc a 4 xyz e}
    82  
    83  do_execsql_test affinity3-250 {
    84    PRAGMA automatic_index=OFF;
    85    SELECT * FROM data JOIN idmap USING(id);
    86  } {1 abc a 4 xyz e}
    87  do_execsql_test affinity3-260 {
    88    SELECT * FROM data JOIN mzed USING(id);
    89  } {1 abc a 4 xyz e}
    90  
    91  finish_test