assertEquals("SELECT Col1 WHERE YEAR(Col1) = 2008", new Query().select(col1).where("YEAR(Col1) = 2008")
.toString());
}
public void testToSqlWhereItem() throws Exception {
MutableColumn col1 = new MutableColumn("Col1", ColumnType.VARCHAR);
SelectItem selectItem = new SelectItem(col1);
FilterItem c = new FilterItem(selectItem, OperatorType.DIFFERENT_FROM, null);
assertEquals("Col1 IS NOT NULL", c.toString());
try {
c = new FilterItem(selectItem, OperatorType.GREATER_THAN, null);
fail("Exception should have been thrown");
} catch (IllegalArgumentException e) {
assertEquals("Can only use EQUALS or DIFFERENT_FROM operator with null-operand", e.getMessage());
}
c = new FilterItem(selectItem, OperatorType.DIFFERENT_FROM, "foo");
assertEquals("Col1 <> 'foo'", c.toString());
c = new FilterItem(selectItem, OperatorType.DIFFERENT_FROM, "'bar'");
// this will be rewritten so it's not an issue even though it look like
// it needs an escape-char
assertEquals("Col1 <> ''bar''", c.toSql());
c = new FilterItem(selectItem, OperatorType.DIFFERENT_FROM, "foo's bar");
// the same applies here
assertEquals("Col1 <> 'foo's bar'", c.toSql());
col1.setType(ColumnType.FLOAT);
c = new FilterItem(selectItem, OperatorType.EQUALS_TO, 423);
assertEquals("Col1 = 423", c.toString());
c = new FilterItem(selectItem, OperatorType.EQUALS_TO, 423426235423.42);
assertEquals("Col1 = 423426235423.42", c.toString());
c = new FilterItem(selectItem, OperatorType.EQUALS_TO, true);
assertEquals("Col1 = 1", c.toString());
Column timeColumn = new MutableColumn("TimeCol", ColumnType.TIME);
selectItem = new SelectItem(timeColumn);
c = new FilterItem(selectItem, OperatorType.GREATER_THAN, "02:30:05.000");
assertEquals("TimeCol > TIME '02:30:05'", c.toString());
Column dateColumn = new MutableColumn("DateCol", ColumnType.DATE);
c = new FilterItem(new SelectItem(dateColumn), OperatorType.GREATER_THAN, "2000-12-31");
assertEquals("DateCol > DATE '2000-12-31'", c.toString());
}