Repository navigation
Expand file tree
/
Copy pathaffinity3.test
More file actions
147 lines (125 loc) · 3.5 KB
/
Copy pathaffinity3.test
File metadata and controls
147 lines (125 loc) · 3.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
==== affinity3-100
CREATE TABLE customer (id INT PRIMARY KEY);
CREATE TABLE apr (id INT PRIMARY KEY, apr REAL);
CREATE VIEW v1 AS
SELECT c.id, i.apr
FROM customer c
LEFT JOIN apr i ON i.id=c.id;
CREATE VIEW v1rj AS
SELECT c.id, i.apr
FROM apr i
RIGHT JOIN customer c ON i.id=c.id;
CREATE VIEW v2 AS
SELECT c.id, v1.apr
FROM customer c
LEFT JOIN v1 ON v1.id=c.id;
CREATE VIEW v2rj AS
SELECT c.id, v1.apr
FROM v1 RIGHT JOIN customer c ON v1.id=c.id;
CREATE VIEW v2rjrj AS
SELECT c.id, v1rj.apr
FROM v1rj RIGHT JOIN customer c ON v1rj.id=c.id;
INSERT INTO customer (id) VALUES (1);
INSERT INTO apr (id, apr) VALUES (1, 12);
INSERT INTO customer (id) VALUES (2);
INSERT INTO apr (id, apr) VALUES (2, 12.01);
----
stmt 0 ok
stmt 46 ok
stmt 97 ok
stmt 191 ok
stmt 288 ok
stmt 381 ok
stmt 475 ok
stmt 577 err 1 -1 620 no such table: customer
stmt 620 err 1 -1 664 no such table: apr
stmt 664 err 1 -1 704 no such table: customer
stmt 704 err 1 -1 751 no such table: apr
==== affinity3-110
PRAGMA automatic_index=ON;
SELECT id, (apr / 100), typeof(apr) apr_type FROM v1;
----
stmt 0 ok
stmt 29 err 1 -1 86 no such table: v1
==== affinity3-111
PRAGMA automatic_index=ON;
SELECT id, (apr / 100), typeof(apr) apr_type FROM v1rj;
----
stmt 0 ok
stmt 29 err 1 -1 88 no such table: v1rj
==== affinity3-120
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2;
----
stmt 0 err 1 -1 57 no such table: v2
==== affinity3-121
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rj;
----
stmt 0 err 1 -1 59 no such table: v2rj
==== affinity3-122
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rjrj;
----
stmt 0 err 1 -1 61 no such table: v2rjrj
==== affinity3-130
PRAGMA automatic_index=OFF;
SELECT id, (apr / 100), typeof(apr) apr_type FROM v1;
----
stmt 0 ok
stmt 30 err 1 -1 87 no such table: v1
==== affinity3-131
SELECT id, (apr / 100), typeof(apr) apr_type FROM v1rj;
----
stmt 0 err 1 -1 59 no such table: v1rj
==== affinity3-140
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2;
----
stmt 0 err 1 -1 57 no such table: v2
==== affinity3-141
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rj;
----
stmt 0 err 1 -1 59 no such table: v2rj
==== affinity3-142
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2rjrj;
----
stmt 0 err 1 -1 61 no such table: v2rjrj
==== affinity3-200
CREATE TABLE map_integer (id INT, name);
INSERT INTO map_integer VALUES(1,'a');
CREATE TABLE map_text (id TEXT, name);
INSERT INTO map_text VALUES('4','e');
CREATE TABLE data (id TEXT, name);
INSERT INTO data VALUES(1,'abc');
INSERT INTO data VALUES('4','xyz');
CREATE VIEW idmap as
SELECT * FROM map_integer
UNION SELECT * FROM map_text;
CREATE TABLE mzed AS SELECT * FROM idmap;
----
stmt 0 ok
stmt 43 err 1 -1 84 no such table: map_integer
stmt 84 ok
stmt 125 err 1 -1 165 no such table: map_text
stmt 165 ok
stmt 202 err 1 -1 238 no such table: data
stmt 238 err 1 -1 276 no such table: data
stmt 276 ok
stmt 367 err 1 -1 411 no such table: idmap
==== affinity3-210
PRAGMA automatic_index=ON;
SELECT * FROM data JOIN idmap USING(id);
----
stmt 0 ok
stmt 29 err 1 -1 72 no such table: data
==== affinity3-220
SELECT * FROM data JOIN mzed USING(id);
----
stmt 0 err 1 -1 42 no such table: data
==== affinity3-250
PRAGMA automatic_index=OFF;
SELECT * FROM data JOIN idmap USING(id);
----
stmt 0 ok
stmt 30 err 1 -1 73 no such table: data
==== affinity3-260
SELECT * FROM data JOIN mzed USING(id);
----
stmt 0 err 1 -1 42 no such table: data