Steve, Because your schema has a union, the SQL engine doesn’t really know how to interpret the data. So it interprets it as a “Java Object.” Essentially, it could be anything. But you can’t compare just anything to true - you need to compare a boolean to true. So you need to tell the SQL engine that the value you’re looking at is, in fact, a boolean.
You can do that with a simple CAST() function in your SQL: SELECT * FROM FLOWFILE WHERE CAST(flag_s AS BOOLEAN) = true That should give you what you’re looking for. Also worth nothing - you mentioned that you’re using ConvertRecord and UpdateRecord before QueryRecord. 99% of the time, you should not be using ConvertRecord in conjunction with any other Record processor. Because the Record processors like UpdateRecord allow you to use any Record Reader, it doesn’t make sense to convert the data first using ConvertRecord - it’s just extra overhead. And, in fact, you may be able to eliminated the UpdateRecord, as well, as just use the SQL within QueryRecord to perform the transformation needed on the fly, rather than having another step to update the data, which requires reading the data, parsing it, updating it, serializing the data, writing the data. This may not be possible, depends on what you’re updating. But QueryRecord does support RecordPath expressions so it’s worth considering. Thanks -Mark On Mar 15, 2022, at 8:35 AM, [email protected]<mailto:[email protected]> wrote: I am having a play with QueryRecord to do some filtering but I have run across this problem. I have a schema for my records which includes a union type, so the relevant part of the schema is { "type":"record", "namespace":"blah", "name":"SimpleTraffic", "fields":[ {"name":"src_address","type":"string"}, {"name":"flag_s","type":["int","boolean"]} ] } This is because I am processing CSV records that look this, where 1 is true and 0 is false. 192.168.0.1,1 Into JSON that looks like this, using a ConvertRecord and an Update Record. {"src_address":"192.168.0.1","flag_s":true} Then I create a QueryRecord so I can filter out the cases where the flag is false. So I use this query. select * from flowfile where flag_s = true But I get this error org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '=' to arguments of type '<JAVATYPE(CLASS JAVA.LANG.OBJECT)> = <BOOLEAN>' Is this because the type is a Union type and the Calcite processor cannot work out which subtype it should be? Can I do anything to persuade the query to use an operator or a function on this field to make it usable? I have tried casting to Boolean or Char but no success. Or do I need to use two separate “before” and “after” schemas to eliminate the union? Regards Steve Hindmarch
