Criteria, Nulls and DATETIME fields

[clough42]

I'm using a SmartGWT 2.5 ListGrid with a JSON data source, and I'm having difficulties with the way RelativeDate AdvancedCriteria filters deal with null values in DATETIME fields.

When I create a filter to match "dateTime < +0D", it filters out future dates, as I expect, but it does not filter out null dateTime values. A similar SQL query does filter out null dateTime values. This makes it very difficult for me to allow the ListGrid to switch seamlessly to client-side filtering, because the server-side and client-side filters yield different results.

Is there an easy way to make my "dateTime < +0D" filters exclude null values? Is this a defect? Do I have to add a clause for "dateTime != null"?

Here is code the demonstrates the issue:

public class SettingsViewImpl extends VLayout implements SettingsView {
	public SettingsViewImpl(ClientFactory clientFactory) {

		Button filterButton = new Button("Filter");
		Button unFilterButton = new Button("UnFilter");

		final ListGrid countryGrid = new ListGrid();
		countryGrid.setWidth(400);
		countryGrid.setHeight(224);
		countryGrid.setShowAllRecords(true);
		countryGrid.setDataSource(CountryDS.getInstance());
		countryGrid.setAutoFetchData(true);

		filterButton.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				AdvancedCriteria criteria = new AdvancedCriteria();
				criteria.setAttribute("fieldName", "independence");
				criteria.setAttribute("operator", OperatorId.LESS_THAN);
				criteria.setAttribute("value", new RelativeDate("+0D"));
				countryGrid.setCriteria(criteria);
			}
		});

		unFilterButton.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				countryGrid.setCriteria(null);
			}
		});

		addMember(filterButton);
		addMember(unFilterButton);
		addMember(countryGrid);
	}

	@Override
	public void setPresenter(Presenter listener) {
	}

	@Override
	public void displayErrorMessage(String message) {
		GWT.log("SettingsViewImpl ERROR: " + message);
	}

	private static class CountryDS extends DataSource {

		private static CountryDS instance = null;

		public static CountryDS getInstance() {
			if (instance == null) {
				instance = new CountryDS("countryDS_JSON");
			}
			return instance;
		}

		public CountryDS(String id) {
			setID(id);
			setDataFormat(DSDataFormat.JSON);
			DataSourceField countryNameField = new DataSourceField(
					"countryName", FieldType.TEXT, "Country");
			DataSourceField independenceField = new DataSourceField(
					"independence", FieldType.DATETIME, "Independence");
			setFields(countryNameField, independenceField);
			setDataURL("countryData.json");
		}
	}

}

And here is JSON data:

[
{
    "countryName":"Country A",
    "independence":"2011-06-07T12:02:56"
},
{
    "countryName":"Country B",
    "independence":"2021-06-10T12:02:56"
},
{
    "countryName":"Country C"
}
]

When I start up this code, I see all three country records (A, B and C). When I click the Filter button, I expect to see only Country A. I expect Country C to be filtered out because it has a null independence value, but it appears in the list.

[Isomorphic]

We're intentionally deviating from SQL's null handling here because it's surprising to users that specifying any criteria on a field (even criteria that should match anything) will eliminate all null records.

If in a particular use case you want to eliminate nulls, just use the isNotNull operator in an "and" expression.