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