tracker issue : CF-4054576

select a category, or use search below
(searches all categories and all time range)
Title:

cfquery query of queries count(*) returns nothing when there are 0 rows

| View in Tracker

Status/Resolution/Reason: Closed/Won't Fix/

Reporter/Name(from Bugbase): Kyle DeBoth / Kyle DeBoth (Kyle DeBoth)

Created: 09/11/2015

Components: Language, General

Versions: 11.0

Failure Type:

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Major / Few users will encounter

Locale/System: English / Windows 7 64-bit

Vote Count: 7

Problem Description:
When using count (*) in cfquery, if the number of rows in the query result is 0, a empty query result is returned. This is in contrast to the way that a database would handle this (returning 0). Cfquery should be consistent with sql databases such as Oracle, SQL Server, and DB2.

Steps to Reproduce:
Use cfquery for a query of queries to return a count and write a query that will return zero rows.

Actual Result:
Nothing is returned.

Expected Result:
Returns 0.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4054576

External Customer Info:
External Company:  
External Customer Name: Kyle DeBoth
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments:

Hi Adobe and Kyle, Verified this on CF11 Update 5 (build 11,0,05,293506). Repro: 1) The returned query is correct: <cfquery name="q" datasource="cfartgallery"> SELECT * FROM art WHERE artID = 1 </cfquery> <cfquery name="q" dbtype="query"> SELECT COUNT(*) AS theCount FROM q WHERE artID = 1 </cfquery> <cfdump var="#q#" format="text"> result: ----------- query CACHED: false EXECUTIONTIME: 0 SQL: SELECT COUNT(*) AS theCount FROM q WHERE artID = 1 RESULTSET: [Record # 1] theCount: 1 ----------- 2) The returned query is incorrect/empty: <cfquery name="q" datasource="cfartgallery"> SELECT * FROM art WHERE artID = 1 </cfquery> <cfquery name="q" dbtype="query"> SELECT COUNT(*) AS theCount FROM q WHERE artID = 0 </cfquery> <cfdump var="#q#" format="text"> result: ----------- query CACHED: false EXECUTIONTIME: 0 SQL: SELECT COUNT(*) AS theCount FROM q WHERE artID = 0 RESULTSET: ----------- That 2nd (incorrect) example's result is missing this part: ----------- [Record # 1] theCount: 0 ----------- Thanks!, -Aaron
Comment by External U.
5903 | September 11, 2015 12:56:15 PM GMT
+1 ...........................
Vote by External U.
5911 | September 11, 2015 02:34:53 PM GMT
+1 ......................
Vote by External U.
5912 | September 11, 2015 04:53:00 PM GMT
+1 Vote must be between 25 and 4000 charactersVote must be between 25 and 4000 characters
Vote by External U.
5913 | September 12, 2015 05:55:14 PM GMT
I'd appreciate some feedback on why this should never be fixed. Returning nothing is not the expected value of a count(*) in any SQL language I am familiar with, why should ColdFusion's attempt at SQL function differently?
Comment by External U.
5904 | September 25, 2015 07:04:20 AM GMT
We had fixed this in some earlier release but the fix was reverted because of the backward compatibility issues.
Comment by Rupesh K.
5905 | September 25, 2015 07:14:23 AM GMT
Thanks for the reply Rupesh. Would it be possible to add an attribute to the cfquery tag i.e. actuallyFunctionsLikeSQL="true" that a user could add to get the expected SQL result? If a user doesn't add anything to the tag, cfquery functions the way it always has. I guess my opinion is that backwards compatibatily shouldn't be a reason not to fix something that's obviously broken.
Comment by External U.
5906 | September 25, 2015 07:21:32 AM GMT
I'm keen to know what real-world backwards compatibility issues you encountered, Rupesh.
Comment by External U.
5907 | September 25, 2015 08:24:04 AM GMT
I am wondering about that too but seeing so many reverts because of backward compatibility issues, I am not surprised. This was fixed and then reverted way back in CF 7/8 days.
Comment by Rupesh K.
5908 | September 25, 2015 08:30:33 AM GMT
OK, fair enough. There's perhaps no point in sinking too much time into it. I s'pose the "unexpected behaviour" should just be documented though?
Comment by External U.
5909 | September 25, 2015 08:39:09 AM GMT
I 2nd Adam's suggestion that this needs to be documented. There should probably be a note in the help docs for this if the issue is not going to be fixed, since the cfquery count(*) functions differently from what a user would expect. http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html
Comment by External U.
5910 | September 29, 2015 03:13:12 PM GMT
Hi Adobe, Would you be able to please re-open/re-investigate? Rupesh was never able to identify the backward-compat scenarios preventing this ticket's fix. See? He said "I am wondering about that too". Could you try to identify exactly what *real world* _problem(s)_ this ticket's fix would cause? Count() is expected to return integer, not empty string. It currently returns empty string if no matching rows found. Thanks!, -Aaron
Comment by Aaron N.
29043 | June 14, 2018 10:10:46 PM GMT
If somebody has a use case where they want a function called COUNT to return something other than a number, that's fine, but they don't get to dictate language design. You can't have "" things. This never should have been reverted.
Comment by Samuel K.
29044 | June 15, 2018 12:16:38 AM GMT
This is just allowing lazy programmers to keep their slippers on and hold everyone else back from a consistent interface to a core function. You may have got away with reverting it in a point release but a full brand new shiny release is expected to have breaking changes (generally for the better). Please revisit this.
Comment by Doug C.
29055 | June 15, 2018 08:34:13 AM GMT
Proposed solution to maintain back compat AND have it behave consistently with all other SQL implementations: have a config flag you can set at the server level that will determine how this will behave. This approach of using server config flags is used with other language features, and would solve the issue for every concerned party. (And please - make the default the expected (returns a 0), rather than the strange empty string behavior.) As an alternative, simply introduce it as a breaking change (as Doug suggests) in the next major version and note it in the release notes or upgrade guide. This would be especially appropriate if no one can find any reported real world case where it's an issue. If whoever's code the fix broke in the 7/8 days is still on a live server and being maintained, the maintainers are by now very comfortable with major version upgrades and making changes to their code to accommodate.
Comment by j a.
29057 | June 15, 2018 02:24:58 PM GMT