Unexpected CFQUERY behaviour

Description

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`)
VALUES
(1,'Page 1'),
(2,'Page 2'),
(3,'Page 3'),
(4,'Page 4'),
(5,'Page 5'),
(6,'Page 6'),
(7,'Page 7'),
(8,'Page 8'),
(9,'Page 9'),
(10,'Page 10');

The following query returns no results because the value attribute of cfqueryparam is blank.

<cfquery name="test" datasource="db">
SELECT *
FROM pages
WHERE page_id NOT IN (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="" list="true">)
LIMIT 100
</cfquery>
<cfdump var="#test#">

I would expected all records to be returned.

The debug output shows the query as:

SELECT *
FROM pages
WHERE page_id NOT IN ([CF_SQL_INTEGER])
LIMIT 100

Environment

None

Activity

Show:
Igal Sapir
October 13, 2019, 5:12 AM

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.

Igal Sapir
October 13, 2019, 4:09 AM

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.

Igal Sapir
October 13, 2019, 2:08 AM

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

Michael Offner
February 19, 2018, 10:00 AM

next step here is to create an example that uses the MySQL driver directly, to take Lucee out of the game.

Michael Offner
February 19, 2018, 9:55 AM

"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.

Assignee

Michael Offner

Reporter

Simon Bingham

Priority

Minor

Labels

None

Fix versions

None

Affects versions