Thunderstone Support Forums

There is a record in my database that, depending on how I query for it, is either there or it's not. When I issue the following query I get no results returned:

select PATN_WKU FROM PATN WHERE PATN_WKU = '7301016';

However, when I issue the following query I do get a result:

select id, PATN_WKU FROM PATN WHERE PATN_TTL LIKE 'Human transferase family members and uses thereof';

I've rebuilt the index on PATN_TTL several times. It's perplexing

Try rebuilding the index on PATN_WKU.

I failed to mention that I did that, too. No change

What type is PATN_WKU? You are querying as a string, is it possible there is a space or other character you wouldn't see otherwise?

Hmm. PATN_WKU is varchar. When the row is returned (via the second query) there does not appear to be any spaces or anything else odd in the string. Before or aft.

If I change the criteria of the first query to something like:

PATN_WKU > '7301014' AND PATN_WKU < '7301019'

I get the numbers 7301014 through 7301019 EXCEPT FOR 7301016. IOW, it does not appear to be in the data. I would expect to see it even if there were *trailing* characters that were extraneous, right?

Although maybe it's some unprintable leading character without a displayable form (not sure what that means?!?)

How would I test for that?

If you are using tsql do the queries then using the -c option to tsql will put each value on a line by itself, and piping the output into cat -vte will show normally unprintable characters, e.g.

tsql -c "select id, PATN_WKU FROM PATN WHERE PATN_TTL LIKE 'Human transferase family members and uses thereof';" | cat -vte

you can also use indexaccess to query the index directly to see what data is in it.

select id, '{'+PATN_WKU+'}' FROM PATN WHERE PATN_TTL LIKE 'Human transferase family members and uses thereof';

Will put {} around the value so you can see anything extra.

Is this windows or *nix? Tsql or Vortex?

That simply returns {7301016}.

This is a Linux installation, with both Vortex and tsql. That is, I first noticed the anomolous results in our Vortex app's output, and I've been doing some testing via a tsql command prompt.

Do john's thing with cat -vte to see if there are any special characters.

tsql -c "select id, '{'+PATN_WKU+'}' FROM PATN WHERE PATN_TTL LIKE 'Human transferase family members and uses thereof';" | cat -vte