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 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# Ticket https://www.sqlite.org/src/info/91e2e8ba6ff2e2 (2011-09-19) 22# Automatic index causes undesired type conversions 23# 24do_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} 56do_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} 60do_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} 64do_execsql_test affinity3-120 { 65 SELECT id, (apr / 100), typeof(apr) apr_type FROM v2; 66} {1 0.12 real 2 0.1201 real} 67do_execsql_test affinity3-121 { 68 SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rj; 69} {1 0.12 real 2 0.1201 real} 70do_execsql_test affinity3-122 { 71 SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rjrj; 72} {1 0.12 real 2 0.1201 real} 73do_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} 77do_execsql_test affinity3-131 { 78 SELECT id, (apr / 100), typeof(apr) apr_type FROM v1rj; 79} {1 0.12 real 2 0.1201 real} 80do_execsql_test affinity3-140 { 81 SELECT id, (apr / 100), typeof(apr) apr_type FROM v2; 82} {1 0.12 real 2 0.1201 real} 83do_execsql_test affinity3-141 { 84 SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rj; 85} {1 0.12 real 2 0.1201 real} 86do_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# 93do_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} 111do_execsql_test affinity3-220 { 112 SELECT * FROM data JOIN mzed USING(id); 113} {1 abc a 4 xyz e} 114 115do_execsql_test affinity3-250 { 116 PRAGMA automatic_index=OFF; 117 SELECT * FROM data JOIN idmap USING(id); 118} {1 abc a 4 xyz e} 119do_execsql_test affinity3-260 { 120 SELECT * FROM data JOIN mzed USING(id); 121} {1 abc a 4 xyz e} 122 123finish_test 124