""Ann W. Harrison"" <aharrison@...> wrote in message
news:5.1.0.14.2.20020304111619.02ce6bb0@......

>
> The problem is actually that it converts strings to numbers
> rather than vice-versa. Very hard to preserve leading zeros
> in that conversion. The conversion fails, of course, if it
> finds any character other than a digit or a decimal point.
> It could fail, I suppose, if it were converting from a string
> to a number and the number had leading zeros.

The problem is an inconsistency between CVT and its precendence rules v/s
NAV and its own behavior.
Without an index, CVT2_compare is called. The precedence rules are inside
CVT2.C in a static table named compare_priority. Basically, string is
converted to one of the integral types, they are converted to floating
point, floating point is converted to date/time and date/time is converted
into blob. In other words:
'abc' = 1 => compare as int
1 = 1.99 => compare as float
1 = current_date => compare as date, fails
'10.10.10' = current_date => compare as date
1.87 = current_date => compare as date, fails
some_date = blob => compare as blob_id (it doesn't make sense, but...).

With heterogeneous comparisons, CVT2_compare() will call CVT_move() to
convert the lowest priority type to the highest priority type. This is where
CVT_move() uses an arsenal of tricks, including the easiest ones to try like
converting a string to int or double.

However, the index walking module (NAV) and BTR (b-tree management) have its
own "idiotincrasy" and work in their own way.

SQL> create table idxwalk(a char(2) not null unique);
SQL> insert into idxwalk values('01');
SQL> commit;
SQL> select a from idxwalk where a = 1;
SQL> NOTHING HERE

And the engine used PLAN (IDXWALK INDEX (RDB$2)).

In this case, BTR_find_leaf() fails miserable while trying to find a
starting point in the index values where to start the match. I doesn't
realize that '01' may be compared to 1. It's fooled by the length. What's
interesting is that the prior call, BTR_find_page(), does the proper
conversion from string to number or vice-versa. For curious people:

while (TRUE)
if (p == key_end)
goto done;
else if (q == node_end || *p > *q)
break;
else if (*p++ < *q++)
goto done;

The expression *p > *q
becomes true, because *p is 1 (as character, it was converted from the
integer 1 in the WHERE clause) but *q is zero, the leading zero from '01'
that I did in the insert.

Here you can see clearly that B-tree management doesn't show respect for the
precedence rules. It should have tried to match an integer, not force
anything to text. As I said, it's again the precedence rules for natural
comparisons done through CVT2. This shortcoming should be considered a bug,
IMHO.

By "simply" forcing the required logic inside the block shown above to jump
to the "done" tag, I got:

SQL> select a from idxwalk where a = 1;
PLAN (IDXWALK INDEX (RDB$2)).

A
======
01

Of course, I knew what I was doing with the debugger, because I knew what I
wanted to produce, but in the general case it may not be so easy to solve.

C. (With a debugger, happier than a dog with a bone.)
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing