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 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} 43do_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} 47do_execsql_test affinity3-120 { 48 SELECT id, (apr / 100), typeof(apr) apr_type FROM v2; 49} {1 0.12 real 2 0.1201 real} 50do_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} 54do_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# 61do_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} 79do_execsql_test affinity3-220 { 80 SELECT * FROM data JOIN mzed USING(id); 81} {1 abc a 4 xyz e} 82 83do_execsql_test affinity3-250 { 84 PRAGMA automatic_index=OFF; 85 SELECT * FROM data JOIN idmap USING(id); 86} {1 abc a 4 xyz e} 87do_execsql_test affinity3-260 { 88 SELECT * FROM data JOIN mzed USING(id); 89} {1 abc a 4 xyz e} 90 91finish_test 92