gitlab.com/CoiaPrant/sqlite3@v1.19.1/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 v1rj AS
    34    SELECT c.id, i.apr
    35    FROM apr i
    36    RIGHT JOIN customer c ON i.id=c.id;
    37    
    38    CREATE VIEW v2 AS
    39    SELECT c.id, v1.apr
    40    FROM customer c
    41    LEFT JOIN v1 ON v1.id=c.id;
    42    
    43    CREATE VIEW v2rj AS
    44    SELECT c.id, v1.apr
    45    FROM v1 RIGHT JOIN customer c ON v1.id=c.id;
    46    
    47    CREATE VIEW v2rjrj AS
    48    SELECT c.id, v1rj.apr
    49    FROM v1rj RIGHT JOIN customer c ON v1rj.id=c.id;
    50    
    51    INSERT INTO customer (id) VALUES (1);
    52    INSERT INTO apr (id, apr) VALUES (1, 12);
    53    INSERT INTO customer (id) VALUES (2);
    54    INSERT INTO apr (id, apr) VALUES (2, 12.01);
    55  }
    56  do_execsql_test affinity3-110 {
    57    PRAGMA automatic_index=ON;
    58    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
    59  } {1 0.12 real 2 0.1201 real}
    60  do_execsql_test affinity3-111 {
    61    PRAGMA automatic_index=ON;
    62    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1rj;
    63  } {1 0.12 real 2 0.1201 real}
    64  do_execsql_test affinity3-120 {
    65    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;
    66  } {1 0.12 real 2 0.1201 real}
    67  do_execsql_test affinity3-121 {
    68    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rj;
    69  } {1 0.12 real 2 0.1201 real}
    70  do_execsql_test affinity3-122 {
    71    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rjrj;
    72  } {1 0.12 real 2 0.1201 real}
    73  do_execsql_test affinity3-130 {
    74    PRAGMA automatic_index=OFF;
    75    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
    76  } {1 0.12 real 2 0.1201 real}
    77  do_execsql_test affinity3-131 {
    78    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1rj;
    79  } {1 0.12 real 2 0.1201 real}
    80  do_execsql_test affinity3-140 {
    81    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;
    82  } {1 0.12 real 2 0.1201 real}
    83  do_execsql_test affinity3-141 {
    84    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rj;
    85  } {1 0.12 real 2 0.1201 real}
    86  do_execsql_test affinity3-142 {
    87    SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rjrj;
    88  } {1 0.12 real 2 0.1201 real}
    89  
    90  # Ticket https://www.sqlite.org/src/info/7ffd1ca1d2ad4ecf  (2017-01-16)
    91  # Incorrect affinity when using automatic indexes 
    92  #
    93  do_execsql_test affinity3-200 {
    94    CREATE TABLE map_integer (id INT, name);
    95    INSERT INTO map_integer VALUES(1,'a');
    96    CREATE TABLE map_text (id TEXT, name);
    97    INSERT INTO map_text VALUES('4','e');
    98    CREATE TABLE data (id TEXT, name);
    99    INSERT INTO data VALUES(1,'abc');
   100    INSERT INTO data VALUES('4','xyz');
   101    CREATE VIEW idmap as
   102        SELECT * FROM map_integer
   103        UNION SELECT * FROM map_text;
   104    CREATE TABLE mzed AS SELECT * FROM idmap;
   105  }
   106  
   107  #do_execsql_test affinity3-210 {
   108    #PRAGMA automatic_index=ON;
   109    #SELECT * FROM data JOIN idmap USING(id);
   110  #} {1 abc a 4 xyz e}
   111  do_execsql_test affinity3-220 {
   112    SELECT * FROM data JOIN mzed USING(id);
   113  } {1 abc a 4 xyz e}
   114  
   115  do_execsql_test affinity3-250 {
   116    PRAGMA automatic_index=OFF;
   117    SELECT * FROM data JOIN idmap USING(id);
   118  } {1 abc a 4 xyz e}
   119  do_execsql_test affinity3-260 {
   120    SELECT * FROM data JOIN mzed USING(id);
   121  } {1 abc a 4 xyz e}
   122  
   123  finish_test