tracker issue : CF-4054576

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.

+1 ...........................

Vote by External U.

+1 ......................

Vote by External U.

+1 Vote must be between 25 and 4000 charactersVote must be between 25 and 4000 characters

Vote by External U.

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.

We had fixed this in some earlier release but the fix was reverted because of the backward compatibility issues.

Comment by Rupesh K.

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.

I'm keen to know what real-world backwards compatibility issues you encountered, Rupesh.

Comment by External U.

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.

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.

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.

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.

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.

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.

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.