svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.sqlite / org.gvsig.sqlite.provider / src / test / resources / org / gvsig / sqlite / dal / resultSetForSetProvider.sql @ 47787
History | View | Annotate | Download (9.45 KB)
1 |
|
---|---|
2 |
-- rem ResultSetForSetProvider SQL
|
3 |
|
4 |
-- normalize-spaces true
|
5 |
-- strip-start true
|
6 |
-- remove-nl true
|
7 |
-- replace-nl-by-space true
|
8 |
-- trim-end true
|
9 |
|
10 |
-- begin testSimple
|
11 |
SELECT "test"."ID", |
12 |
"test"."Byte", |
13 |
"test"."Bool1", |
14 |
"test"."Long", |
15 |
"test"."Timestamp", |
16 |
"test"."Date", |
17 |
"test"."Time", |
18 |
"test"."Bool2", |
19 |
"test"."String", |
20 |
"test"."Bool3", |
21 |
"test"."Double", |
22 |
"test"."Bool4", |
23 |
"test"."Float", |
24 |
"test"."Bool5", |
25 |
"test"."Decimal", |
26 |
"test"."Geometry" |
27 |
FROM "test" |
28 |
ORDER BY "test"."ID" ASC |
29 |
-- end testSimple
|
30 |
|
31 |
-- begin testSimpleGroup
|
32 |
SELECT MIN("test"."ID") AS "ID", |
33 |
MAX("test"."Byte") AS "Byte", |
34 |
NULL AS "Bool1", |
35 |
"test"."Long", |
36 |
NULL AS "Timestamp", |
37 |
NULL AS "Date", |
38 |
NULL AS "Time", |
39 |
NULL AS "Bool2", |
40 |
NULL AS "String", |
41 |
NULL AS "Bool3", |
42 |
SUM("test"."Double") AS "Double", |
43 |
NULL AS "Bool4", |
44 |
NULL AS "Float", |
45 |
NULL AS "Bool5", |
46 |
NULL AS "Decimal", |
47 |
NULL AS "Geometry" |
48 |
FROM "test" |
49 |
GROUP BY "test"."Long" |
50 |
ORDER BY "test"."Long" ASC NULLS LAST |
51 |
-- end testSimpleGroup
|
52 |
|
53 |
-- begin testSubselect
|
54 |
SELECT "test"."ID", |
55 |
"test"."Byte", |
56 |
"test"."Bool1", |
57 |
"test"."Long", |
58 |
"test"."Timestamp", |
59 |
"test"."Date", |
60 |
"test"."Time", |
61 |
"test"."Bool2", |
62 |
"test"."String", |
63 |
"test"."Bool3", |
64 |
"test"."Double", |
65 |
"test"."Bool4", |
66 |
"test"."Float", |
67 |
"test"."Bool5", |
68 |
"test"."Decimal", |
69 |
"test"."Geometry", |
70 |
EXISTS( SELECT "countries"."ISO2" |
71 |
FROM "countries" |
72 |
WHERE (("test"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) LIMIT 1 ) AS "EXISTS62a964cd7bc24f409b97c03b9170408d" |
73 |
FROM "test" |
74 |
WHERE EXISTS( SELECT "countries"."ISO2" |
75 |
FROM "countries" |
76 |
WHERE (("test"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) LIMIT 1 ) |
77 |
ORDER BY "test"."ID" ASC |
78 |
-- end testSubselect
|
79 |
|
80 |
-- begin testSubselect2
|
81 |
SELECT "test"."ID", |
82 |
"test"."Byte", |
83 |
"test"."Bool1", |
84 |
"test"."Long", |
85 |
"test"."Timestamp", |
86 |
"test"."Date", |
87 |
"test"."Time", |
88 |
"test"."Bool2", |
89 |
"test"."String", |
90 |
"test"."Bool3", |
91 |
"test"."Double", |
92 |
"test"."Bool4", |
93 |
"test"."Float", |
94 |
"test"."Bool5", |
95 |
"test"."Decimal", |
96 |
"test"."Geometry", |
97 |
EXISTS( SELECT "countries"."Long" FROM "countries" WHERE (("test"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) LIMIT 1 ) AS "EXISTS62a964cd7bc24f409b97c03b9170408d" |
98 |
FROM "test" |
99 |
WHERE EXISTS( SELECT "countries"."Long" |
100 |
FROM "countries" |
101 |
WHERE (("test"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) |
102 |
LIMIT 1 )
|
103 |
ORDER BY "test"."ID" ASC |
104 |
-- end testSubselect2
|
105 |
|
106 |
-- begin testGroupAndSubselect
|
107 |
SELECT MIN("test"."ID") AS "ID", |
108 |
MAX("test"."Byte") AS "Byte", |
109 |
NULL AS "Bool1", |
110 |
"test"."Long", |
111 |
NULL AS "Timestamp", |
112 |
NULL AS "Date", |
113 |
NULL AS "Time", |
114 |
NULL AS "Bool2", |
115 |
NULL AS "String", |
116 |
NULL AS "Bool3", |
117 |
SUM("test"."Double") AS "Double", |
118 |
NULL AS "Bool4", |
119 |
NULL AS "Float", |
120 |
NULL AS "Bool5", |
121 |
NULL AS "Decimal", |
122 |
NULL AS "Geometry" |
123 |
FROM "test" |
124 |
WHERE EXISTS( SELECT "countries"."ISO2" |
125 |
FROM "countries" |
126 |
WHERE (("test"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) |
127 |
LIMIT 1 )
|
128 |
GROUP BY "test"."Long" |
129 |
ORDER BY "test"."Long" ASC NULLS LAST |
130 |
-- end testGroupAndSubselect
|
131 |
|
132 |
-- begin testConstantColumnPrimaryKey
|
133 |
SELECT "test"."ID", |
134 |
"test"."Byte", |
135 |
"test"."Bool1", |
136 |
"test"."Long", |
137 |
"test"."Timestamp", |
138 |
"test"."Date", |
139 |
"test"."Time", |
140 |
"test"."Bool2", |
141 |
"test"."String", |
142 |
"test"."Bool3", |
143 |
"test"."Double", |
144 |
"test"."Bool4", |
145 |
"test"."Float", |
146 |
"test"."Bool5", |
147 |
"test"."Decimal", |
148 |
"test"."Geometry" |
149 |
FROM "test" |
150 |
ORDER BY "test"."ID" ASC |
151 |
-- end testConstantColumnPrimaryKey
|
152 |
|
153 |
-- begin testComputedAttribute
|
154 |
SELECT "test"."ID", |
155 |
"test"."Byte", |
156 |
"test"."Bool1", |
157 |
"test"."Long", |
158 |
"test"."Timestamp", |
159 |
"test"."Date", |
160 |
"test"."Time", |
161 |
"test"."Bool2", |
162 |
"test"."String", |
163 |
"test"."Bool3", |
164 |
"test"."Double", |
165 |
"test"."Bool4", |
166 |
"test"."Float", |
167 |
"test"."Bool5", |
168 |
"test"."Decimal", |
169 |
"test"."Geometry", |
170 |
("test"."ID" * 2) AS "Compu1" |
171 |
FROM "test" |
172 |
ORDER BY "test"."ID" ASC |
173 |
-- end testComputedAttribute
|
174 |
|
175 |
-- begin testComputedAttribute2
|
176 |
SELECT "test"."ID", |
177 |
"test"."Byte", |
178 |
"test"."Bool1", |
179 |
"test"."Long", |
180 |
"test"."Timestamp", |
181 |
"test"."Date", |
182 |
"test"."Time", |
183 |
"test"."Bool2", |
184 |
"test"."String", |
185 |
"test"."Bool3", |
186 |
"test"."Double", |
187 |
"test"."Bool4", |
188 |
"test"."Float", |
189 |
"test"."Bool5", |
190 |
"test"."Decimal", |
191 |
"test"."Geometry", |
192 |
("test"."ID" * 2) AS "Compu1", |
193 |
(("test"."Long" + 10) + ("test"."ID" * 2)) AS "Compu2" |
194 |
FROM "test" |
195 |
ORDER BY "test"."ID" ASC |
196 |
-- end testComputedAttribute2
|
197 |
|
198 |
-- begin testComputedExtraColumn
|
199 |
SELECT "test"."ID", |
200 |
"test"."Byte", |
201 |
"test"."Bool1", |
202 |
"test"."Long", |
203 |
"test"."Timestamp", |
204 |
"test"."Date", |
205 |
"test"."Time", |
206 |
"test"."Bool2", |
207 |
"test"."String", |
208 |
"test"."Bool3", |
209 |
"test"."Double", |
210 |
"test"."Bool4", |
211 |
"test"."Float", |
212 |
"test"."Bool5", |
213 |
"test"."Decimal", |
214 |
"test"."Geometry", |
215 |
("test"."ID" * 2) AS "Compu1", |
216 |
(("test"."Long" + 10) + ("test"."ID" * 2)) AS "Extra1" |
217 |
FROM "test" |
218 |
ORDER BY "test"."ID" ASC |
219 |
-- end testComputedExtraColumn
|
220 |
|
221 |
-- begin testComputedExtraColumn2
|
222 |
SELECT "test"."ID", |
223 |
"test"."Byte", |
224 |
"test"."Bool1", |
225 |
"test"."Long", |
226 |
"test"."Timestamp", |
227 |
"test"."Date", |
228 |
"test"."Time", |
229 |
"test"."Bool2", |
230 |
"test"."String", |
231 |
"test"."Bool3", |
232 |
"test"."Double", |
233 |
"test"."Bool4", |
234 |
"test"."Float", |
235 |
"test"."Bool5", |
236 |
"test"."Decimal", |
237 |
"test"."Geometry", |
238 |
("test"."ID" * 2) AS "Extra1", |
239 |
(("test"."Long" + 10) + ("test"."ID" * 2)) AS "Extra2" |
240 |
FROM "test" |
241 |
ORDER BY "test"."ID" ASC |
242 |
-- end testComputedExtraColumn2
|
243 |
|
244 |
-- begin testComputedExtraColumnWithWhere
|
245 |
SELECT "test"."ID", |
246 |
"test"."Byte", |
247 |
"test"."Bool1", |
248 |
"test"."Long", |
249 |
"test"."Timestamp", |
250 |
"test"."Date", |
251 |
"test"."Time", |
252 |
"test"."Bool2", |
253 |
"test"."String", |
254 |
"test"."Bool3", |
255 |
"test"."Double", |
256 |
"test"."Bool4", |
257 |
"test"."Float", |
258 |
"test"."Bool5", |
259 |
"test"."Decimal", |
260 |
"test"."Geometry", |
261 |
("test"."ID" * 2) AS "Compu1", |
262 |
(("test"."Long" + 10) + ("test"."ID" * 2)) AS "Extra1" |
263 |
FROM "test" |
264 |
WHERE ((("test"."Long" + 10) + ("test"."ID" * 2)) > 10) |
265 |
ORDER BY "Extra1" ASC NULLS LAST, |
266 |
"test"."ID" ASC |
267 |
-- end testComputedExtraColumnWithWhere
|
268 |
|
269 |
-- begin testGroupByComputed
|
270 |
SELECT MIN("test"."ID") AS "ID", |
271 |
MAX("test"."Byte") AS "Byte", |
272 |
NULL AS "Bool1", |
273 |
"test"."Long", |
274 |
NULL AS "Timestamp", |
275 |
NULL AS "Date", |
276 |
NULL AS "Time", |
277 |
NULL AS "Bool2", |
278 |
NULL AS "String", |
279 |
NULL AS "Bool3", |
280 |
SUM("test"."Double") AS "Double", |
281 |
NULL AS "Bool4", |
282 |
NULL AS "Float", |
283 |
NULL AS "Bool5", |
284 |
NULL AS "Decimal", |
285 |
NULL AS "Geometry", |
286 |
("test"."ID" * 2) AS "Compu1", |
287 |
SUM(("test"."Long" + 300)) AS "Compu2", |
288 |
SUM(1) AS "Compu3", |
289 |
(("test"."Long" + 10) + ("test"."ID" * 2)) AS "Extra1", |
290 |
SUM(((20 + "test"."Byte") + ("test"."ID" * 2))) AS "Extra2" |
291 |
FROM "test" |
292 |
GROUP BY "test"."Long", |
293 |
(("test"."Long" + 10) + ("test"."ID" * 2)), |
294 |
("test"."ID" * 2) |
295 |
ORDER BY "test"."Long" ASC NULLS LAST, |
296 |
(("test"."Long" + 10) + ("test"."ID" * 2)) ASC NULLS LAST, |
297 |
("test"."ID" * 2) ASC NULLS LAST |
298 |
-- end testGroupByComputed
|
299 |
|
300 |
-- begin testGroupByAndOrderByComputed
|
301 |
SELECT MIN("test"."ID") AS "ID", |
302 |
MAX("test"."Byte") AS "Byte", |
303 |
NULL AS "Bool1", |
304 |
"test"."Long", |
305 |
NULL AS "Timestamp", |
306 |
NULL AS "Date", |
307 |
NULL AS "Time", |
308 |
NULL AS "Bool2", |
309 |
NULL AS "String", |
310 |
NULL AS "Bool3", |
311 |
SUM("test"."Double") AS "Double", |
312 |
NULL AS "Bool4", |
313 |
NULL AS "Float", |
314 |
NULL AS "Bool5", |
315 |
NULL AS "Decimal", |
316 |
NULL AS "Geometry", |
317 |
("test"."ID" * 2) AS "Compu1", |
318 |
SUM(("test"."Long" + 300)) AS "Compu2", |
319 |
SUM(1) AS "Compu3", |
320 |
(("test"."Long" + 10) + ("test"."ID" * 2)) AS "Extra1", |
321 |
SUM(((20 + "test"."Byte") + ("test"."ID" * 2))) AS "Extra2", |
322 |
NULL AS "Extra3" |
323 |
FROM "test" |
324 |
GROUP BY "test"."Long", |
325 |
(("test"."Long" + 10) + ("test"."ID" * 2)), |
326 |
("test"."ID" * 2) |
327 |
ORDER BY (("Long" + 10) + ("ID" * 2)) ASC NULLS LAST, |
328 |
SUM(((20 + "Byte") + ("ID" * 2))) ASC NULLS LAST |
329 |
-- end testGroupByAndOrderByComputed
|
330 |
|
331 |
-- begin testSimpleAggregateAndOrder
|
332 |
SELECT MIN("test"."ID") AS "ID", |
333 |
MAX("test"."Byte") AS "Byte", |
334 |
NULL AS "Bool1", |
335 |
NULL AS "Long", |
336 |
NULL AS "Timestamp", |
337 |
NULL AS "Date", |
338 |
NULL AS "Time", |
339 |
NULL AS "Bool2", |
340 |
NULL AS "String", |
341 |
NULL AS "Bool3", |
342 |
SUM("test"."Double") AS "Double", |
343 |
NULL AS "Bool4", |
344 |
NULL AS "Float", |
345 |
NULL AS "Bool5", |
346 |
NULL AS "Decimal", |
347 |
NULL AS "Geometry", |
348 |
SUM(("test"."Long" + 10)) AS "Extra1", |
349 |
NULL AS "Extra2" |
350 |
FROM "test" |
351 |
ORDER BY MIN("test"."ID") ASC, |
352 |
SUM(("Long" + 10)) ASC NULLS LAST |
353 |
-- end testSimpleAggregateAndOrder
|