0

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.

Peter Boughton
  • 110,170
  • 32
  • 120
  • 176
Jared Beck
  • 16,796
  • 9
  • 72
  • 97
  • 2
    not sure if this helps, but if you dump your `rs` query a = `1`, so you can do `where a = 1`, but that doesn't answer your actual question (and doesn't make much sense, honestly). I'm also on 10 and not 9.0.1. FWIW railo4 stores the value as `true` and the query works with `a = true`, but also bombs on `a is true`. – Matt Busche Aug 26 '13 at 03:11
  • 2
    Typically `IS` is only used for testing `null` values. As Matt mentioned, a simple equality test should work ie either `where a = true` or `where a = 1`. Of course you could also use cfqueryparam `where ` – Leigh Aug 26 '13 at 03:30
  • @MattBusche - You should post that as an answer. Apparently, bit values are treated as `1/0` internally, so the answer is exactly what you said ie Use `where a = 1` (or if you prefer to use a literal `true`, use cfqueryparam). – Leigh Aug 26 '13 at 05:39
  • @Leigh Thanks for the confirmation. I've posted this as an answer. – Matt Busche Aug 26 '13 at 13:10

1 Answers1

4

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.

Matt Busche
  • 14,216
  • 5
  • 36
  • 61