Querying Binary datatype column from sqlline

[dinesh kumar <di...@gmail.com>]

Hi All,
 I have create a table records with the following definition

CREATE TABLE records
(
    dth INTEGER NOT NULL,
    dt BIGINT,
    cid INTEGER NOT NULL,
    dsc TINYINT NOT NULL,
    aid SMALLINT NOT NULL,
    ps TINYINT NOT NULL,
    jid BIGINT NOT NULL,
    mk binary(15) NOT NULL,
    CONSTRAINT records_pk
    PRIMARY KEY (dth, cid, dsc, aid, ps, jid, mk)
) SALT_BUCKETS = 255;

I have about 70 million records in the table. I use Java to write to
write to the table.

Now I am trying to query some of the message using the field mk (please
note that it is a binary field of length 15 bytes).
I have been trying to query the field using sqlline command line. I want to
figure out if a record with mk value ('BRYEAQYbIhbbwFX3ufb4' - Base64
encoded array) exists in the table.

I used the following sql command

*select mk from records where mk = ('BRYEAQYbIhbbwFX3ufb4', 'BINARY');*

This does not return any records and it finished in less than a second. I
got suspicious and try to see the query plan using explain

*explain  select mk from records where mk = ('BRYEAQYbIhbbwFX3ufb4',
'BINARY');*
+------------------------------------------+
|                   PLAN                   |
+------------------------------------------+
| *DEGENERATE SCAN OVER RECORDS *            |
+------------------------------------------+
1 row selected (0.015 seconds)

Why is the query result in a degenerate scan?

I tried different combinations and the following took some seconds to
execute

*select mk from records where mk like ('05160401061b2216dbc055f7b9f6f8',
'BINARY');*
+-----------------+
|       MK        |
+-----------------+
+-----------------+
No rows selected (*4.175 seconds*)

This is the query plan of the query

*explain  select mk from records where mk like
('05160401061b2216dbc055f7b9f6f8', 'BINARY');*
+------------------------------------------+
|                   PLAN                   |
+------------------------------------------+
| CLIENT 256-CHUNK PARALLEL 256-WAY FULL SCAN OVER RECORDS |
|     SERVER FILTER BY FIRST KEY ONLY AND MK LIKE
('05160401061b2216dbc055f7b9f6f8', 'BINARY') |
| CLIENT MERGE SORT                        |
+------------------------------------------+
3 rows selected (0.019 seconds)

Why does like work here?

Also what is the correct way to query a binary field using command line
SQL? What I am doing wrong?

Please help

Thanks,
Dinesh