SQLSyntaxErrorException: ORA-00936: missing expression

Originally by desmond27

I have been trying to add a filter to an SQLContainer which is connected to an Oracle database via a TableQuery. When I trigger the filter (TextField's TextChangeEvent) I get the following execeptions:

java.lang.RuntimeException: Failed to update item set size.
	at com.vaadin.data.util.sqlcontainer.SQLContainer.updateCount(SQLContainer.java:1181)
	at com.vaadin.data.util.sqlcontainer.SQLContainer.size(SQLContainer.java:403)
	at com.vaadin.ui.Table.containerItemSetChange(Table.java:4521)
	at com.vaadin.data.util.sqlcontainer.SQLContainer.fireContentsChange(SQLContainer.java:1633)
	at com.vaadin.data.util.sqlcontainer.SQLContainer.refresh(SQLContainer.java:910)
	at com.vaadin.data.util.sqlcontainer.SQLContainer.refresh(SQLContainer.java:892)
	at com.vaadin.data.util.sqlcontainer.SQLContainer.addContainerFilter(SQLContainer.java:550)

-***REDACTED****

Caused by: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
	at com.vaadin.data.util.sqlcontainer.query.TableQuery.executeQuery(TableQuery.java:529)
	at com.vaadin.data.util.sqlcontainer.query.TableQuery.getCount(TableQuery.java:210)
	at com.vaadin.data.util.sqlcontainer.SQLContainer.updateCount(SQLContainer.java:1170)
	... 47 more

Upon debugging I have discovered that in order to get the rowcount of the table the OracleGenerator is generating a query with an empty WHERE clause. The generated query in question is as follows:

SELECT COUNT(*) AS "rowcount" FROM (SELECT * FROM USERDETAILS WHERE )

I have tried to execute this query in the SQL console and I get the same error message as mentioned in the SQLSyntaxErrorException above. The query works normally after removing the WHERE clause.

This issue is a show stopper for those using an Oracle database and require to implement filters for the SQLContainer connected to the same.

Imported from https://dev.vaadin.com/ issue #19403

Start with OracleGenerator and the count path in com.vaadin.data.util.sqlcontainer.query.TableQuery.getCount(), then trace how SQLContainer.addContainerFilter() supplies the WHERE clause. Reproduce the filtered count against Oracle and verify that generated SQL no longer contains an empty WHERE clause and that the filter count succeeds.