Problem with index or data or both - mystery

barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

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
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Problem with index or data or both - mystery

Post by mark »

Try rebuilding the index on PATN_WKU.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

I failed to mention that I did that, too. No change
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Problem with index or data or both - mystery

Post by John »

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?
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

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?
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

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

How would I test for that?
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Problem with index or data or both - mystery

Post by John »

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.
John Turnbull
Thunderstone Software
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Problem with index or data or both - mystery

Post by mark »

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?
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

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.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Problem with index or data or both - mystery

Post by mark »

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
Post Reply