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