Unexpected CFQUERY behaviour
This seems to be a bug and is certainly an inconsistency against ACF.
Given the following table:
CREATE TABLE `pages` (
`page_id` int(11) NOT NULL AUTO_INCREMENT,
`page_title` varchar(150) DEFAULT NULL,
PRIMARY KEY (`page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `pages` (`page_id`, `page_title`)
The following query returns no results because the value attribute of cfqueryparam is blank.
<cfquery name="test" datasource="db">
WHERE page_id NOT IN (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="" list="true">)
I would expected all records to be returned.
The debug output shows the query as:
WHERE page_id NOT IN ([CF_SQL_INTEGER])
What should happen if you pass <cfqueryparam list="true" null="true" type="integer">? IMO it should throw an error, same as if an empty value is passed as a list.
NeitherIN (NULL) nor NOT IN (NULL)matches any rows, so why ever use it? As a kill switch for the query so that it fails? Seems like a “bad hack” to me.
NULL matches nothing, so passing NULL for the list returns 0 rows for both IN (NULL) and NOT IN (NULL).
It really doesn’t make sense to pass NULL IMO.
The behavior in Postgres and SQL Server is consistent in the sense that NOT IN (NULL) returns no rows and NOT IN ('') returns all rows:
That syntax doesn’t work with MySQL but I’ve confirmed the same behavior of NOT IN
next step here is to create an example that uses the MySQL driver directly, to take Lucee out of the game.
"NOT IN () actually results in an error in MySQL so maybe ACF and Lucee are both wrong for not throwing an exception." This is handled by the driver coming from mySQL itself, so ACF/Lucee cannot be wrong on this.
The MySQL driver is the only one can be wrong on this.