Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

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:

Zac Spitzer 31 July 2023 at 21:00

this has already been changed in 6.0

Matt Dyer 31 July 2023 at 20:49

I tested on the latest 5.4 version that was available on commandbox.

Matt Dyer 31 July 2023 at 20:48

Even running a query like this without the list attribute and a blank value seems to produce that strange result with [CF_SQL_INTEGER] in the result query.

For example:

This query will run and produce a result set with no records.

Maybe there should be, or is a separate issue for this?

It seems like there is something wrong with the type validation if it excepts a blank value for an integer. I tried with query of queries, but doing it there gives an error.

Igal Sapir 13 October 2019 at 05:12

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 13 October 2019 at 04:09

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.

Details

Assignee

Reporter

Priority

Labels

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Affects versions

Created 1 February 2018 at 16:20
Updated 31 July 2023 at 21:01