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 | 47539 | jjdelcerro | |
---|---|---|---|
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 | 47579 | fdiaz | 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 | 47539 | jjdelcerro | -- end testSimple
|
30 | |||
31 | -- begin testSimpleGroup
|
||
32 | 47579 | fdiaz | SELECT MIN("test"."ID") AS "ID", |
33 | MAX("test"."Byte") AS "Byte", |
||
34 | 47539 | jjdelcerro | NULL AS "Bool1", |
35 | 47579 | fdiaz | "test"."Long", |
36 | 47539 | jjdelcerro | NULL AS "Timestamp", |
37 | NULL AS "Date", |
||
38 | NULL AS "Time", |
||
39 | NULL AS "Bool2", |
||
40 | NULL AS "String", |
||
41 | NULL AS "Bool3", |
||
42 | 47579 | fdiaz | SUM("test"."Double") AS "Double", |
43 | 47539 | jjdelcerro | NULL AS "Bool4", |
44 | NULL AS "Float", |
||
45 | NULL AS "Bool5", |
||
46 | NULL AS "Decimal", |
||
47 | NULL AS "Geometry" |
||
48 | 47579 | fdiaz | FROM "test" |
49 | GROUP BY "test"."Long" |
||
50 | ORDER BY "test"."Long" ASC NULLS LAST |
||
51 | 47539 | jjdelcerro | -- end testSimpleGroup
|
52 | |||
53 | -- begin testSubselect
|
||
54 | 47579 | fdiaz | 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 | 47580 | fdiaz | 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 | 47579 | fdiaz | ORDER BY "test"."ID" ASC |
78 | 47539 | jjdelcerro | -- end testSubselect
|
79 | |||
80 | -- begin testSubselect2
|
||
81 | 47579 | fdiaz | 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 | 47787 | jjdelcerro | EXISTS( SELECT "countries"."Long" FROM "countries" WHERE (("test"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) LIMIT 1 ) AS "EXISTS62a964cd7bc24f409b97c03b9170408d" |
98 | 47579 | fdiaz | FROM "test" |
99 | 47787 | jjdelcerro | WHERE EXISTS( SELECT "countries"."Long" |
100 | 47539 | jjdelcerro | FROM "countries" |
101 | 47579 | fdiaz | WHERE (("test"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) |
102 | 47580 | fdiaz | LIMIT 1 )
|
103 | 47579 | fdiaz | ORDER BY "test"."ID" ASC |
104 | 47539 | jjdelcerro | -- end testSubselect2
|
105 | |||
106 | -- begin testGroupAndSubselect
|
||
107 | 47579 | fdiaz | SELECT MIN("test"."ID") AS "ID", |
108 | MAX("test"."Byte") AS "Byte", |
||
109 | 47539 | jjdelcerro | NULL AS "Bool1", |
110 | 47579 | fdiaz | "test"."Long", |
111 | 47539 | jjdelcerro | NULL AS "Timestamp", |
112 | NULL AS "Date", |
||
113 | NULL AS "Time", |
||
114 | NULL AS "Bool2", |
||
115 | NULL AS "String", |
||
116 | NULL AS "Bool3", |
||
117 | 47579 | fdiaz | SUM("test"."Double") AS "Double", |
118 | 47539 | jjdelcerro | NULL AS "Bool4", |
119 | NULL AS "Float", |
||
120 | NULL AS "Bool5", |
||
121 | NULL AS "Decimal", |
||
122 | NULL AS "Geometry" |
||
123 | 47579 | fdiaz | FROM "test" |
124 | 47580 | fdiaz | WHERE EXISTS( SELECT "countries"."ISO2" |
125 | 47539 | jjdelcerro | FROM "countries" |
126 | 47579 | fdiaz | WHERE (("test"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) |
127 | 47580 | fdiaz | LIMIT 1 )
|
128 | 47579 | fdiaz | GROUP BY "test"."Long" |
129 | ORDER BY "test"."Long" ASC NULLS LAST |
||
130 | 47539 | jjdelcerro | -- end testGroupAndSubselect
|
131 | |||
132 | -- begin testConstantColumnPrimaryKey
|
||
133 | 47579 | fdiaz | 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 | 47539 | jjdelcerro | -- end testConstantColumnPrimaryKey
|
152 | |||
153 | -- begin testComputedAttribute
|
||
154 | 47579 | fdiaz | 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 | 47539 | jjdelcerro | -- end testComputedAttribute
|
174 | |||
175 | -- begin testComputedAttribute2
|
||
176 | 47579 | fdiaz | 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 | 47539 | jjdelcerro | -- end testComputedAttribute2
|
197 | |||
198 | -- begin testComputedExtraColumn
|
||
199 | 47579 | fdiaz | 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 | 47539 | jjdelcerro | -- end testComputedExtraColumn
|
220 | |||
221 | -- begin testComputedExtraColumn2
|
||
222 | 47579 | fdiaz | 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 | 47539 | jjdelcerro | -- end testComputedExtraColumn2
|
243 | |||
244 | -- begin testComputedExtraColumnWithWhere
|
||
245 | 47579 | fdiaz | 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 | 47539 | jjdelcerro | ORDER BY "Extra1" ASC NULLS LAST, |
266 | 47579 | fdiaz | "test"."ID" ASC |
267 | 47539 | jjdelcerro | -- end testComputedExtraColumnWithWhere
|
268 | |||
269 | -- begin testGroupByComputed
|
||
270 | 47579 | fdiaz | SELECT MIN("test"."ID") AS "ID", |
271 | MAX("test"."Byte") AS "Byte", |
||
272 | 47539 | jjdelcerro | NULL AS "Bool1", |
273 | 47579 | fdiaz | "test"."Long", |
274 | 47539 | jjdelcerro | NULL AS "Timestamp", |
275 | NULL AS "Date", |
||
276 | NULL AS "Time", |
||
277 | NULL AS "Bool2", |
||
278 | NULL AS "String", |
||
279 | NULL AS "Bool3", |
||
280 | 47579 | fdiaz | SUM("test"."Double") AS "Double", |
281 | 47539 | jjdelcerro | NULL AS "Bool4", |
282 | NULL AS "Float", |
||
283 | NULL AS "Bool5", |
||
284 | NULL AS "Decimal", |
||
285 | NULL AS "Geometry", |
||
286 | 47579 | fdiaz | ("test"."ID" * 2) AS "Compu1", |
287 | SUM(("test"."Long" + 300)) AS "Compu2", |
||
288 | 47539 | jjdelcerro | SUM(1) AS "Compu3", |
289 | 47579 | fdiaz | (("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 | 47539 | jjdelcerro | -- end testGroupByComputed
|
299 | |||
300 | -- begin testGroupByAndOrderByComputed
|
||
301 | 47579 | fdiaz | SELECT MIN("test"."ID") AS "ID", |
302 | MAX("test"."Byte") AS "Byte", |
||
303 | 47539 | jjdelcerro | NULL AS "Bool1", |
304 | 47579 | fdiaz | "test"."Long", |
305 | 47539 | jjdelcerro | NULL AS "Timestamp", |
306 | NULL AS "Date", |
||
307 | NULL AS "Time", |
||
308 | NULL AS "Bool2", |
||
309 | NULL AS "String", |
||
310 | NULL AS "Bool3", |
||
311 | 47579 | fdiaz | SUM("test"."Double") AS "Double", |
312 | 47539 | jjdelcerro | NULL AS "Bool4", |
313 | NULL AS "Float", |
||
314 | NULL AS "Bool5", |
||
315 | NULL AS "Decimal", |
||
316 | NULL AS "Geometry", |
||
317 | 47579 | fdiaz | ("test"."ID" * 2) AS "Compu1", |
318 | SUM(("test"."Long" + 300)) AS "Compu2", |
||
319 | 47539 | jjdelcerro | SUM(1) AS "Compu3", |
320 | 47579 | fdiaz | (("test"."Long" + 10) + ("test"."ID" * 2)) AS "Extra1", |
321 | SUM(((20 + "test"."Byte") + ("test"."ID" * 2))) AS "Extra2", |
||
322 | 47539 | jjdelcerro | NULL AS "Extra3" |
323 | 47579 | fdiaz | FROM "test" |
324 | GROUP BY "test"."Long", |
||
325 | (("test"."Long" + 10) + ("test"."ID" * 2)), |
||
326 | ("test"."ID" * 2) |
||
327 | 47539 | jjdelcerro | 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 | 47579 | fdiaz | SELECT MIN("test"."ID") AS "ID", |
333 | MAX("test"."Byte") AS "Byte", |
||
334 | 47539 | jjdelcerro | 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 | 47579 | fdiaz | SUM("test"."Double") AS "Double", |
343 | 47539 | jjdelcerro | NULL AS "Bool4", |
344 | NULL AS "Float", |
||
345 | NULL AS "Bool5", |
||
346 | NULL AS "Decimal", |
||
347 | NULL AS "Geometry", |
||
348 | 47579 | fdiaz | SUM(("test"."Long" + 10)) AS "Extra1", |
349 | 47539 | jjdelcerro | NULL AS "Extra2" |
350 | 47579 | fdiaz | FROM "test" |
351 | ORDER BY MIN("test"."ID") ASC, |
||
352 | 47539 | jjdelcerro | SUM(("Long" + 10)) ASC NULLS LAST |
353 | -- end testSimpleAggregateAndOrder |