METAMODEL-1165: Fixed - added default_table alias table
[metamodel.git] / core / src / test / java / org / apache / metamodel / query / FilterItemTest.java
1 /**
2 * Licensed to the Apache Software Foundation (ASF) under one
3 * or more contributor license agreements. See the NOTICE file
4 * distributed with this work for additional information
5 * regarding copyright ownership. The ASF licenses this file
6 * to you under the Apache License, Version 2.0 (the
7 * "License"); you may not use this file except in compliance
8 * with the License. You may obtain a copy of the License at
9 *
10 * http://www.apache.org/licenses/LICENSE-2.0
11 *
12 * Unless required by applicable law or agreed to in writing,
13 * software distributed under the License is distributed on an
14 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 * KIND, either express or implied. See the License for the
16 * specific language governing permissions and limitations
17 * under the License.
18 */
19 package org.apache.metamodel.query;
20
21 import java.util.ArrayList;
22 import java.util.Arrays;
23 import java.util.LinkedList;
24 import java.util.List;
25 import java.util.stream.Collectors;
26
27 import org.apache.metamodel.DataContext;
28 import org.apache.metamodel.MetaModelException;
29 import org.apache.metamodel.QueryPostprocessDataContext;
30 import org.apache.metamodel.data.CachingDataSetHeader;
31 import org.apache.metamodel.data.DataSet;
32 import org.apache.metamodel.data.DataSetHeader;
33 import org.apache.metamodel.data.DefaultRow;
34 import org.apache.metamodel.data.InMemoryDataSet;
35 import org.apache.metamodel.data.Row;
36 import org.apache.metamodel.data.SimpleDataSetHeader;
37 import org.apache.metamodel.schema.Column;
38 import org.apache.metamodel.schema.ColumnType;
39 import org.apache.metamodel.schema.MutableColumn;
40 import org.apache.metamodel.schema.MutableSchema;
41 import org.apache.metamodel.schema.MutableTable;
42 import org.apache.metamodel.schema.Table;
43 import org.apache.metamodel.schema.TableType;
44
45 import com.google.common.collect.Lists;
46
47 import junit.framework.TestCase;
48
49 public class FilterItemTest extends TestCase {
50
51 public void testExpressionBasedFilter() throws Exception {
52 FilterItem filterItem = new FilterItem("foobar");
53 assertEquals("foobar", filterItem.getExpression());
54
55 try {
56 filterItem.evaluate(null);
57 fail("Exception should have been thrown");
58 } catch (Exception e) {
59 assertEquals("Expression-based filters cannot be manually evaluated", e.getMessage());
60 }
61
62 Column col1 = new MutableColumn("Col1", ColumnType.VARCHAR);
63 assertEquals("SELECT Col1 WHERE foobar", new Query().select(col1).where(filterItem).toString());
64
65 assertEquals("SELECT Col1 WHERE YEAR(Col1) = 2008", new Query().select(col1).where("YEAR(Col1) = 2008")
66 .toString());
67 }
68
69 public void testToSqlWhereItem() throws Exception {
70 MutableColumn col1 = new MutableColumn("Col1", ColumnType.VARCHAR);
71 SelectItem selectItem = new SelectItem(col1);
72 FilterItem c = new FilterItem(selectItem, OperatorType.DIFFERENT_FROM, null);
73 assertEquals("Col1 IS NOT NULL", c.toString());
74
75 try {
76 c = new FilterItem(selectItem, OperatorType.GREATER_THAN, null);
77 fail("Exception should have been thrown");
78 } catch (IllegalArgumentException e) {
79 assertEquals("Can only use EQUALS or DIFFERENT_FROM operator with null-operand", e.getMessage());
80 }
81
82 c = new FilterItem(selectItem, OperatorType.DIFFERENT_FROM, "foo");
83 assertEquals("Col1 <> 'foo'", c.toString());
84
85 c = new FilterItem(selectItem, OperatorType.DIFFERENT_FROM, "'bar'");
86
87 // this will be rewritten so it's not an issue even though it look like
88 // it needs an escape-char
89 assertEquals("Col1 <> ''bar''", c.toSql());
90
91 c = new FilterItem(selectItem, OperatorType.DIFFERENT_FROM, "foo's bar");
92 // the same applies here
93 assertEquals("Col1 <> 'foo's bar'", c.toSql());
94
95 col1.setType(ColumnType.FLOAT);
96 c = new FilterItem(selectItem, OperatorType.EQUALS_TO, 423);
97 assertEquals("Col1 = 423", c.toString());
98
99 c = new FilterItem(selectItem, OperatorType.EQUALS_TO, 423426235423.42);
100 assertEquals("Col1 = 423426235423.42", c.toString());
101
102 c = new FilterItem(selectItem, OperatorType.EQUALS_TO, true);
103 assertEquals("Col1 = 1", c.toString());
104
105 c = new FilterItem(selectItem, OperatorType.GREATER_THAN_OR_EQUAL, 123);
106 assertEquals("Col1 >= 123", c.toString());
107
108 c = new FilterItem(selectItem, OperatorType.LESS_THAN_OR_EQUAL, 123);
109 assertEquals("Col1 <= 123", c.toString());
110
111 Column timeColumn = new MutableColumn("TimeCol", ColumnType.TIME);
112 selectItem = new SelectItem(timeColumn);
113 c = new FilterItem(selectItem, OperatorType.GREATER_THAN, "02:30:05.000");
114 assertEquals("TimeCol > TIME '02:30:05'", c.toString());
115
116 Column dateColumn = new MutableColumn("DateCol", ColumnType.DATE);
117 c = new FilterItem(new SelectItem(dateColumn), OperatorType.GREATER_THAN, "2000-12-31");
118 assertEquals("DateCol > DATE '2000-12-31'", c.toString());
119 }
120
121 public void testToStringTimeStamp() throws Exception {
122 Column timestampColumn = new MutableColumn("TimestampCol", ColumnType.TIMESTAMP);
123 FilterItem c = new FilterItem(new SelectItem(timestampColumn), OperatorType.LESS_THAN,
124 "2000-12-31 02:30:05.007");
125 assertEquals("TimestampCol < TIMESTAMP '2000-12-31 02:30:05'", c.toString());
126
127 c = new FilterItem(new SelectItem(timestampColumn), OperatorType.LESS_THAN, "2000-12-31 02:30:05");
128 assertEquals("TimestampCol < TIMESTAMP '2000-12-31 02:30:05'", c.toString());
129
130 Column dateColumn = new MutableColumn("DateCol", ColumnType.DATE);
131 c = new FilterItem(new SelectItem(timestampColumn), OperatorType.GREATER_THAN, new SelectItem(dateColumn));
132 assertEquals("TimestampCol > DateCol", c.toString());
133 }
134
135 public void testEvaluateStrings() throws Exception {
136 Column col1 = new MutableColumn("Col1", ColumnType.VARCHAR);
137 Column col2 = new MutableColumn("Col2", ColumnType.VARCHAR);
138 SelectItem s1 = new SelectItem(col1);
139 SelectItem s2 = new SelectItem(col2);
140 SelectItem[] selectItems = new SelectItem[] { s1, s2 };
141 SimpleDataSetHeader header = new SimpleDataSetHeader(selectItems);
142 Row row;
143 FilterItem c;
144
145 row = new DefaultRow(header, new Object[] { "foo", "bar" });
146 c = new FilterItem(s1, OperatorType.DIFFERENT_FROM, s2);
147 assertTrue(c.evaluate(row));
148
149 row = new DefaultRow(header, new Object[] { "aaa", "bbb" });
150 c = new FilterItem(s1, OperatorType.GREATER_THAN, s2);
151 assertFalse(c.evaluate(row));
152
153 c = new FilterItem(s1, OperatorType.LESS_THAN, s2);
154 assertTrue(c.evaluate(row));
155
156 row = new DefaultRow(header, new Object[] { "aaa", "aaa" });
157 c = new FilterItem(s1, OperatorType.EQUALS_TO, s2);
158 assertTrue(c.evaluate(row));
159
160 c = new FilterItem(s1, OperatorType.LIKE, s2);
161 row = new DefaultRow(header, new Object[] { "foobar", "fo%b%r" });
162 assertTrue(c.evaluate(row));
163
164 row = new DefaultRow(header, new Object[] { "foobbdbafsdfr", "fo%b%r" });
165 assertTrue(c.evaluate(row));
166 }
167
168 public void testEvaluateNull() throws Exception {
169 Column col1 = new MutableColumn("Col1", ColumnType.INTEGER);
170 Column col2 = new MutableColumn("Col2", ColumnType.DECIMAL);
171 SelectItem s1 = new SelectItem(col1);
172 SelectItem s2 = new SelectItem(col2);
173 List<SelectItem> selectItems = Lists.newArrayList( s1, s2 );
174 CachingDataSetHeader header = new CachingDataSetHeader(selectItems);
175
176 FilterItem c = new FilterItem(s1, OperatorType.EQUALS_TO, null);
177
178 Row row = new DefaultRow(header, new Object[] { 1, 1 });
179 assertFalse(c.evaluate(row));
180 row = new DefaultRow(header, new Object[] { null, 1 });
181 assertTrue(c.evaluate(row));
182
183 c = new FilterItem(s1, OperatorType.EQUALS_TO, 1);
184
185 row = new DefaultRow(header, new Object[] { 1, 1 });
186 assertTrue(c.evaluate(row));
187 row = new DefaultRow(header, new Object[] { null, 1 });
188 assertFalse(c.evaluate(row));
189
190 c = new FilterItem(s1, OperatorType.DIFFERENT_FROM, 5);
191
192 row = new DefaultRow(header, new Object[] { 1, 1 });
193 assertTrue(c.evaluate(row));
194 row = new DefaultRow(header, new Object[] { null, 1 });
195 assertTrue(c.evaluate(row));
196
197 c = new FilterItem(s1, OperatorType.GREATER_THAN, s2);
198
199 row = new DefaultRow(header, new Object[] { 5, 1 });
200 assertTrue(c.evaluate(row));
201 row = new DefaultRow(header, new Object[] { null, 1 });
202 assertFalse(c.evaluate(row));
203 row = new DefaultRow(header, new Object[] { 1, null });
204 assertFalse(c.evaluate(row));
205
206 c = new FilterItem(s1, OperatorType.GREATER_THAN_OR_EQUAL, s2);
207 row = new DefaultRow(header, new Object[] { 5, 1 });
208 assertTrue(c.evaluate(row));
209 row = new DefaultRow(header, new Object[] { 1, 5 });
210 assertFalse(c.evaluate(row));
211 row = new DefaultRow(header, new Object[] { 5, 5 });
212 assertTrue(c.evaluate(row));
213 row = new DefaultRow(header, new Object[] { null, 1 });
214 assertFalse(c.evaluate(row));
215 row = new DefaultRow(header, new Object[] { 1, null });
216 assertFalse(c.evaluate(row));
217
218 c = new FilterItem(s1, OperatorType.LESS_THAN_OR_EQUAL, s2);
219 row = new DefaultRow(header, new Object[] { 1, 5 });
220 assertTrue(c.evaluate(row));
221 row = new DefaultRow(header, new Object[] { 5, 1 });
222 assertFalse(c.evaluate(row));
223 row = new DefaultRow(header, new Object[] { 1, 1 });
224 assertTrue(c.evaluate(row));
225 row = new DefaultRow(header, new Object[] { null, 1 });
226 assertFalse(c.evaluate(row));
227 row = new DefaultRow(header, new Object[] { 1, null });
228 assertFalse(c.evaluate(row));
229
230 c = new FilterItem(s1, OperatorType.EQUALS_TO, s2);
231 row = new DefaultRow(header, new Object[] { 1, null });
232 assertFalse(c.evaluate(row));
233 row = new DefaultRow(header, new Object[] { null, null });
234 assertTrue(c.evaluate(row));
235 }
236
237 public void testEvaluateDates() throws Exception {
238 Column col1 = new MutableColumn("Col1", ColumnType.DATE);
239 SelectItem s1 = new SelectItem(col1);
240 CachingDataSetHeader header = new CachingDataSetHeader(Lists.newArrayList(s1));
241
242 long currentTimeMillis = System.currentTimeMillis();
243 FilterItem c = new FilterItem(s1, OperatorType.LESS_THAN, new java.sql.Date(currentTimeMillis));
244
245 Row row = new DefaultRow(header, new Object[] { new java.sql.Date(currentTimeMillis) });
246 assertFalse(c.evaluate(row));
247 row = new DefaultRow(header, new Object[] { new java.sql.Date(currentTimeMillis + 10000000) });
248 assertFalse(c.evaluate(row));
249 row = new DefaultRow(header, new Object[] { new java.sql.Date(currentTimeMillis - 10000000) });
250 assertTrue(c.evaluate(row));
251 }
252
253 public void testEvaluateBooleans() throws Exception {
254 Column col1 = new MutableColumn("Col1", ColumnType.BIT);
255 SelectItem s1 = new SelectItem(col1);
256 SelectItem[] selectItems = new SelectItem[] { s1 };
257 DataSetHeader header = new SimpleDataSetHeader(selectItems);
258
259 FilterItem c = new FilterItem(s1, OperatorType.EQUALS_TO, true);
260
261 Row row = new DefaultRow(header, new Object[] { true });
262 assertTrue(c.evaluate(row));
263 row = new DefaultRow(header, new Object[] { false });
264 assertFalse(c.evaluate(row));
265
266 c = new FilterItem(s1, OperatorType.EQUALS_TO, false);
267 row = new DefaultRow(header, new Object[] { true });
268 assertFalse(c.evaluate(row));
269 row = new DefaultRow(header, new Object[] { false });
270 assertTrue(c.evaluate(row));
271
272 c = new FilterItem(s1, OperatorType.GREATER_THAN, false);
273 row = new DefaultRow(header, new Object[] { true });
274 assertTrue(c.evaluate(row));
275 row = new DefaultRow(header, new Object[] { false });
276 assertFalse(c.evaluate(row));
277 }
278
279 /**
280 * Tests that the following (general) rules apply to the object:
281 * <p/>
282 * <li>the hashcode is the same when run twice on an unaltered object</li>
283 * <li>if o1.equals(o2) then this condition must be true: o1.hashCode() ==
284 * 02.hashCode()
285 */
286 public void testEqualsAndHashCode() throws Exception {
287 Column col1 = new MutableColumn("Col1", ColumnType.BIT);
288
289 FilterItem c1 = new FilterItem(new SelectItem(col1), OperatorType.EQUALS_TO, true);
290 FilterItem c2 = new FilterItem(new SelectItem(col1), OperatorType.EQUALS_TO, true);
291 assertEquals(c1, c2);
292 assertEquals(c1.hashCode(), c2.hashCode());
293
294 c2 = new FilterItem(new SelectItem(col1), OperatorType.GREATER_THAN, true);
295 assertFalse(c1.equals(c2));
296 assertFalse(c1.hashCode() == c2.hashCode());
297
298 Column col2 = new MutableColumn("Col2", ColumnType.VARBINARY);
299 c2 = new FilterItem(new SelectItem(col2), OperatorType.EQUALS_TO, true);
300 assertFalse(c1.equals(c2));
301 assertFalse(c1.hashCode() == c2.hashCode());
302 }
303
304 public void testOrFilterItem() throws Exception {
305 Column col1 = new MutableColumn("Col1", ColumnType.VARCHAR);
306
307 SelectItem s1 = new SelectItem(col1);
308 FilterItem c1 = new FilterItem(s1, OperatorType.EQUALS_TO, "foo");
309 FilterItem c2 = new FilterItem(s1, OperatorType.EQUALS_TO, "bar");
310 FilterItem c3 = new FilterItem(s1, OperatorType.EQUALS_TO, "foobar");
311
312 FilterItem filter = new FilterItem(c1, c2, c3);
313 assertEquals("(Col1 = 'foo' OR Col1 = 'bar' OR Col1 = 'foobar')", filter.toString());
314
315 DataSetHeader header = new SimpleDataSetHeader(new SelectItem[] { s1 });
316
317 assertTrue(filter.evaluate(new DefaultRow(header, new Object[] { "foo" })));
318 assertTrue(filter.evaluate(new DefaultRow(header, new Object[] { "bar" })));
319 assertTrue(filter.evaluate(new DefaultRow(header, new Object[] { "foobar" })));
320
321 assertFalse(filter.evaluate(new DefaultRow(header, new Object[] { "foob" })));
322 }
323
324 public void testAndFilterItem() throws Exception {
325 Column col1 = new MutableColumn("Col1", ColumnType.VARCHAR);
326
327 SelectItem s1 = new SelectItem(col1);
328 FilterItem c1 = new FilterItem(s1, OperatorType.LIKE, "foo%");
329 FilterItem c2 = new FilterItem(s1, OperatorType.LIKE, "%bar");
330 FilterItem c3 = new FilterItem(s1, OperatorType.DIFFERENT_FROM, "foobar");
331
332 FilterItem filter = new FilterItem(LogicalOperator.AND, c1, c2, c3);
333 assertEquals("(Col1 LIKE 'foo%' AND Col1 LIKE '%bar' AND Col1 <> 'foobar')", filter.toString());
334
335 CachingDataSetHeader header = new CachingDataSetHeader(Lists.newArrayList(s1));
336 assertTrue(filter.evaluate(new DefaultRow(header, new Object[] { "foo bar" })));
337 assertTrue(filter.evaluate(new DefaultRow(header, new Object[] { "foosenbar" })));
338 assertFalse(filter.evaluate(new DefaultRow(header, new Object[] { "foo" })));
339 assertFalse(filter.evaluate(new DefaultRow(header, new Object[] { "hello world" })));
340 assertFalse(filter.evaluate(new DefaultRow(header, new Object[] { "foobar" })));
341 }
342
343 // Ticket #410
344 public void testOrFilterItemWithoutSelectingActualItmes() throws Exception {
345
346 // define the schema
347 final MutableSchema schema = new MutableSchema("s");
348 MutableTable table = new MutableTable("persons", TableType.TABLE, schema);
349 schema.addTable(table);
350 final Column col1 = new MutableColumn("name", ColumnType.VARCHAR, table, 1, true);
351 final Column col2 = new MutableColumn("role", ColumnType.VARCHAR, table, 2, true);
352 final Column col3 = new MutableColumn("column_number", ColumnType.INTEGER, table, 3, true);
353 table.addColumn(col1);
354 table.addColumn(col2);
355 table.addColumn(col3);
356
357 Query q = new Query();
358 q.select(col3);
359 q.from(col1.getTable());
360
361 SelectItem selectItem1 = new SelectItem(col1);
362 SelectItem selectItem2 = new SelectItem(col2);
363
364 FilterItem item1 = new FilterItem(selectItem1, OperatorType.EQUALS_TO, "kasper");
365 FilterItem item2 = new FilterItem(selectItem2, OperatorType.EQUALS_TO, "user");
366
367 q.where(new FilterItem(item1, item2));
368
369 assertEquals(
370 "SELECT persons.column_number FROM s.persons WHERE (persons.name = 'kasper' OR persons.role = 'user')",
371 q.toString());
372
373 DataContext dc = new QueryPostprocessDataContext() {
374
375 @Override
376 public DataSet materializeMainSchemaTable(Table table, List<Column> columns, int maxRows) {
377 // we expect 3 columns to be materialized because the query has column references in both SELECT and WHERE clause
378 assertEquals(3, columns.size());
379 assertEquals("column_number", columns.get(0).getName());
380 assertEquals("name", columns.get(1).getName());
381 assertEquals("role", columns.get(2).getName());
382
383 DataSetHeader header = new CachingDataSetHeader(Lists.newArrayList(col1, col2, col3).stream()
384 .map(SelectItem::new)
385 .collect(Collectors.toList()));
386 List<Row> rows = new LinkedList<Row>();
387 rows.add(new DefaultRow(header, new Object[] { "foo", "bar", 1 }));
388 rows.add(new DefaultRow(header, new Object[] { "kasper", "developer", 2 }));
389 rows.add(new DefaultRow(header, new Object[] { "admin", "admin", 3 }));
390 rows.add(new DefaultRow(header, new Object[] { "elikeon", "user", 4 }));
391 rows.add(new DefaultRow(header, new Object[] { "someuser", "user", 5 }));
392 rows.add(new DefaultRow(header, new Object[] { "hmm", "what-the", 6 }));
393
394 return new InMemoryDataSet(header, rows);
395 }
396
397 @Override
398 protected String getMainSchemaName() throws MetaModelException {
399 return "s";
400 }
401
402 @Override
403 protected MutableSchema getMainSchema() throws MetaModelException {
404 return schema;
405 }
406 };
407
408 DataSet result = dc.executeQuery(q);
409 List<Object[]> objectArrays = result.toObjectArrays();
410 assertEquals(3, objectArrays.size());
411 assertEquals(2, objectArrays.get(0)[0]);
412 assertEquals(4, objectArrays.get(1)[0]);
413 assertEquals(5, objectArrays.get(2)[0]);
414 }
415
416 public void testInOperandSql() throws Exception {
417 SelectItem selectItem = new SelectItem(new MutableColumn("foo", ColumnType.VARCHAR, null, 1, null, null, true,
418 null, false, null));
419 Object operand = new String[] { "foo", "bar" };
420 assertEquals("foo IN ('foo' , 'bar')", new FilterItem(selectItem, OperatorType.IN, operand).toSql());
421
422 operand = Arrays.asList("foo", "bar", "baz");
423 assertEquals("foo IN ('foo' , 'bar' , 'baz')", new FilterItem(selectItem, OperatorType.IN, operand).toSql());
424
425 operand = "foo";
426 assertEquals("foo IN ('foo')", new FilterItem(selectItem, OperatorType.IN, operand).toSql());
427
428 operand = new ArrayList<Object>();
429 assertEquals("foo IN ()", new FilterItem(selectItem, OperatorType.IN, operand).toSql());
430 }
431
432 public void testNotInOperandSql() throws Exception {
433 SelectItem selectItem = new SelectItem("foo", "foo");
434 Object operand = new String[] { "foo", "bar" };
435 assertEquals("foo NOT IN ('foo' , 'bar')", new FilterItem(selectItem, OperatorType.NOT_IN, operand).toSql());
436
437 operand = Arrays.asList("foo", "bar", "baz");
438 assertEquals("foo NOT IN ('foo' , 'bar' , 'baz')", new FilterItem(selectItem, OperatorType.NOT_IN, operand).toSql());
439
440 operand = "foo";
441 assertEquals("foo NOT IN ('foo')", new FilterItem(selectItem, OperatorType.NOT_IN, operand).toSql());
442
443 operand = new ArrayList<Object>();
444 assertEquals("foo NOT IN ()", new FilterItem(selectItem, OperatorType.NOT_IN, operand).toSql());
445 }
446
447 public void testNotLikeOperandSql() throws Exception {
448 Column column = new MutableColumn("foo");
449 SelectItem selectItem = new SelectItem(column);
450 String operand = "%foo";
451 assertEquals("foo NOT LIKE '%foo'", new FilterItem(selectItem, OperatorType.NOT_LIKE, operand).toSql());
452
453 operand = "foo%";
454 assertEquals("foo NOT LIKE 'foo%'", new FilterItem(selectItem, OperatorType.NOT_LIKE, operand).toSql());
455
456 operand = "%foo%foo%";
457 assertEquals("foo NOT LIKE '%foo%foo%'", new FilterItem(selectItem, OperatorType.NOT_LIKE, operand).toSql());
458 }
459
460 public void testInOperandEvaluate() throws Exception {
461 SelectItem selectItem = new SelectItem(new MutableColumn("foo", ColumnType.VARCHAR, null, 1, null, null, true,
462 null, false, null));
463 Object operand = new String[] { "foo", "bar" };
464
465 FilterItem filterItem = new FilterItem(selectItem, OperatorType.IN, operand);
466 DataSetHeader header = new CachingDataSetHeader(Lists.newArrayList(selectItem));
467
468 assertTrue(filterItem.evaluate(new DefaultRow(header, new Object[] { "foo" })));
469 assertTrue(filterItem.evaluate(new DefaultRow(header, new Object[] { "bar" })));
470 assertFalse(filterItem.evaluate(new DefaultRow(header, new Object[] { "foobar" })));
471 }
472 }