Logical comparison of boolean literals in coldfusion query of queries?

Question

How does one compare a boolean column with a boolean literal?

The ColdFusion Query of Queries user guide claims to support comparison of boolean literals.

Test conditional

This conditional tests whether a Boolean expression is True, False, or Unknown. Syntax

cond_test ::= expression [IS [NOT] {TRUE | FALSE | UNKNOWN} ]

However, in ColdFusion 9.0.1 this code

<cfscript>
  rs = QueryNew('a', 'Bit');
  QueryAddRow(rs);
  rs.a[1] = true;
</cfscript>
<cfquery name="rs2" dbtype="query">
  select * from rs where a IS TRUE;
</cfquery>

produces the following error

java.sql.SQLException: <br><b>Query Of Queries syntax error.</b><br>
Encountered "a IS TRUE.
Incorrect conditional expression, 
Expected one of [like|null|between|in|comparison] condition, 

    at coldfusion.sql.imq.jdbcStatement.parseSQL(jdbcStatement.java:566)
    at coldfusion.sql.imq.jdbcStatement.fetchResult(jdbcStatement.java:536)
    at coldfusion.sql.imq.jdbcStatement.execute(jdbcStatement.java:131)
    at coldfusion.sql.Executive.executeQuery(Executive.java:1374)
    at coldfusion.sql.SqlImpl.execute(SqlImpl.java:345)
    at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:915)
    at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:590)
    at cfqoq2ecfm1569474275.runPage(/Users/jared/git/pf/tafkan/test/temp/qoq.cfm:6)

I've found an ugly workaround (not shown), but if there's a cleaner way, I'd like to know.

Answer 1

When you dump rs it shows a = 1, so you need to use WHERE a = 1 or WHERE a = <cfqueryparam value="true" cfsqltype="cf_sql_bit"/>

In Railo the value is stored as true and not a 1/0 value, but using a cfqueryparam will fix your issue either way.