Description Oli Schmid 2011-08-12 04:39:43 EDT

When using a LongArrayHolder as bind in SQL.select the SQL service is generating corrupt SQL.

Reproducible: Always

Steps to Reproduce:
Use the following sample code somewhere on a server side service.

LongArrayHolder uids = new LongArrayHolder();

	SQL.selectInto(
		"SELECT 1 FROM DUAL " +
	  "UNION " +
	  "SELECT 2 FROM DUAL " +
	  "INTO :uids "
	  , new NVPair("uids", uids)
	);
	
	SQL.select(
		"SELECT 'works' FROM DUAL " +
		"WHERE 1 NOT IN (:uids) " +
	  , new NVPair("uids", uids)
	);

When having only one or no value in the LongArrayHolder the SQL call works well.

	LongArrayHolder uids = new LongArrayHolder();
	
	SQL.selectInto(
		"SELECT 1 FROM DUAL " +
	  "INTO :uids "
	  , new NVPair("uids", uids)
	);
	
	SQL.select(
		"SELECT 'works' FROM DUAL " +
		"WHERE 1 NOT IN (:uids) " +
	  , new NVPair("uids", uids)
	);
	
Two working workarounds are the use of folloing statments:

	SQL.select(
		"SELECT 'works' FROM DUAL " +
		"WHERE 1 NOT IN :uids " +
	  , new NVPair("uids", uids)
	);
	
or

	SQL.select(
		"SELECT 'works' FROM DUAL " +
		"WHERE 1 <> :uids " +
	  , new NVPair("uids", uids)
	);	

In terms of SQL the two workarounds are syntactically wrong.