Bug warning: DAO 3.6 FindFirst with Like operator

Microsoft acknowledges a bug with this, but the bug is worse than they say.

It turns out that if you use the Like operator without a wildcard on a
snapshot type recordset, all sorts of wierd, undesirable things can happen.
This situation can arise when you need something like a list of match
conditions, some of which are exact matches.

According to Microsoft, the bug is that it will treat all expressions without
wildcards as if they are followed by *. In fact, I found out that any single
character expression such as "B" will match everything! Basically, what this
comes down to is that you should never use FindFirst with Like on a Snapshot
recordset unless you are certain the expression -will- contain at least one
wildcard character.

Steve Jorgensen <nospam@nospam.nospam> wrote in
news:d1fn00tfpe0r4q8cuja6l51rd21rm7tevl@4ax.com:
[color=blue]
> Microsoft acknowledges a bug with this, but the bug is worse than they
> say.
>
> It turns out that if you use the Like operator without a wildcard on a
> snapshot type recordset, all sorts of wierd, undesirable things can
> happen. This situation can arise when you need something like a list of
> match conditions, some of which are exact matches.
>
> According to Microsoft, the bug is that it will treat all expressions
> without wildcards as if they are followed by *. In fact, I found out
> that any single character expression such as "B" will match everything!
> Basically, what this comes down to is that you should never use
> FindFirst with Like on a Snapshot recordset unless you are certain the
> expression -will- contain at least one wildcard character.[/color]

What do you want it to do when you ask it to:
..FindFirst "fldDescription LIKE 'B'"

I wonder how may of us would shrug our shoulders and say, "MS cannot
predict every peculiarity of coding!", and how many of us would say,
"Bug!" in reponse to this.

I would say the first of the two.

I note that ADO
(
..CursorType = adOpenStatic
..LockType = adLockReadOnly
)
does not find any records for:
..Find "fldDescription LIKE 'B'"
and moves the record pointer to .EOF
(quite properly IMO)
unless there is a record with the value
"B" (only)
in the searched field
in which case it
"finds" that record.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

nospam@nospam.nospam (Steve Jorgensen) wrote in
<d1fn00tfpe0r4q8cuja6l51rd21rm7tevl@4ax.com>:
[color=blue]
>Microsoft acknowledges a bug with this, but the bug is worse than
>they say.
>
>It turns out that if you use the Like operator without a wildcard
>on a snapshot type recordset, all sorts of wierd, undesirable
>things can happen. This situation can arise when you need
>something like a list of match conditions, some of which are exact
>matches.[/color]

I don't quite understand the use of LIKE without an asterisk.
Wouldn't it be equivalent to "=" ?

I use BuildCriteria for constructing all my WHERE clauses, so I
never code to cover both exactl and LIKE, which is the only
scenario I can think of where you'd use LIKE without the asterisk.
[color=blue]
>According to Microsoft, the bug is that it will treat all
>expressions without wildcards as if they are followed by *. In
>fact, I found out that any single character expression such as "B"
>will match everything! Basically, what this comes down to is that
>you should never use FindFirst with Like on a Snapshot recordset
>unless you are certain the expression -will- contain at least one
>wildcard character.[/color]

Well, D'oh!

Why would anyone make a practice of using LIKE except with a
wildcard, except coding laziness?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

On Mon, 19 Jan 2004 18:09:30 GMT, dXXXfenton@bway.net.invalid (David W.
Fenton) wrote:
[color=blue]
>nospam@nospam.nospam (Steve Jorgensen) wrote in
><d1fn00tfpe0r4q8cuja6l51rd21rm7tevl@4ax.com>:
>[color=green]
>>Microsoft acknowledges a bug with this, but the bug is worse than
>>they say.
>>
>>It turns out that if you use the Like operator without a wildcard
>>on a snapshot type recordset, all sorts of wierd, undesirable
>>things can happen. This situation can arise when you need
>>something like a list of match conditions, some of which are exact
>>matches.[/color]
>
>I don't quite understand the use of LIKE without an asterisk.
>Wouldn't it be equivalent to "=" ?[/color]

Yes, exactly. In my case, I have a list of rules for how to import from
columns in an import table that can vary from month to month. For some rules,
I know the exact name, and for some rules, I know an invariant part of the
name. I have a table of rules where the match condition is a Like expression,
and if the match is an exact field name, there are no wildcard characters.
This should be a valid thing to want to do, right?
[color=blue]
>I use BuildCriteria for constructing all my WHERE clauses, so I
>never code to cover both exactl and LIKE, which is the only
>scenario I can think of where you'd use LIKE without the asterisk.
>[color=green]
>>According to Microsoft, the bug is that it will treat all
>>expressions without wildcards as if they are followed by *. In
>>fact, I found out that any single character expression such as "B"
>>will match everything! Basically, what this comes down to is that
>>you should never use FindFirst with Like on a Snapshot recordset
>>unless you are certain the expression -will- contain at least one
>>wildcard character.[/color]
>
>Well, D'oh!
>
>Why would anyone make a practice of using LIKE except with a
>wildcard, except coding laziness?[/color]

See above. Like should act like "=" for expressions with no wildcards. Why
should I not expect to take advantage of this fact when I have a list of
criteria, some exact, and some not? As far as I'm concerned, coding laziness
of this sort can be a -good- thing. Use the functionality that -should- be
built in instead of having to write your own code that duplicates that. Now,
I admit, I've recently been advocating doing otherwise when it improves code
clarity and designs out common programmer errors, but this is not one of those
cases.

On 19 Jan 2004 12:40:42 GMT, Lyle Fairfield <MissingAddress@Invalid.Com>
wrote:
[color=blue]
>Steve Jorgensen <nospam@nospam.nospam> wrote in
>news:d1fn00tfpe0r4q8cuja6l51rd21rm7tevl@4ax.com :
>[color=green]
>> Microsoft acknowledges a bug with this, but the bug is worse than they
>> say.
>>
>> It turns out that if you use the Like operator without a wildcard on a
>> snapshot type recordset, all sorts of wierd, undesirable things can
>> happen. This situation can arise when you need something like a list of
>> match conditions, some of which are exact matches.
>>
>> According to Microsoft, the bug is that it will treat all expressions
>> without wildcards as if they are followed by *. In fact, I found out
>> that any single character expression such as "B" will match everything!
>> Basically, what this comes down to is that you should never use
>> FindFirst with Like on a Snapshot recordset unless you are certain the
>> expression -will- contain at least one wildcard character.[/color]
>
>What do you want it to do when you ask it to:
>.FindFirst "fldDescription LIKE 'B'"
>
>I wonder how may of us would shrug our shoulders and say, "MS cannot
>predict every peculiarity of coding!", and how many of us would say,
>"Bug!" in reponse to this.
>
>I would say the first of the two.[/color]

Really? I would say that commercial code should include unit tests that would
prevent this sort of thing. In fact, this is one of the first tests I would
write since it's a boundary condition. Also, I don't think it's that
peculiar. I was trying to take advantage of the fact that a Like comparison
with no wildcards is the same as an "=" because I have a list of conditions,
some exact, and some partial. Why should I not expect let Like take care of
the distinction for me?

I do agree that this is not the most aggregious error you could make, and the
work-arounds are not hard. I just thought a warning was in order.

In any case, I just switched to a Dynaset for the easy work-around.
[color=blue]
>
>I note that ADO
>(
>.CursorType = adOpenStatic
>.LockType = adLockReadOnly
>)
>does not find any records for:
>.Find "fldDescription LIKE 'B'"
>and moves the record pointer to .EOF
>(quite properly IMO)
>unless there is a record with the value
>"B" (only)
>in the searched field
>in which case it
>"finds" that record.[/color]

I'm aware that ADO does not have the problem, which is expected. In the case
of an MDB front-end to an MDB, though, I still prefer to stick with DAO.

"Steve Jorgensen" <nospam@nospam.nospam> wrote...
[color=blue]
> This should be a valid thing to want to do, right?[/color]

Generally, no. Since such a query would not be optimized properly anyway.
Better to make the code a big more specific o be faster (and the fact that
it will avoid bugs is the bonus feature of the approach!).
[color=blue]
> As far as I'm concerned, coding laziness of this sort can be a -good-[/color]
thing.

Without testing and finding out you have to work a bit harder here? I do not
think so. :-)


--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.

On Mon, 19 Jan 2004 15:16:29 -0800, "Michael \(michka\) Kaplan [MS]"
<michkap@online.microsoft.com> wrote:
[color=blue]
>"Steve Jorgensen" <nospam@nospam.nospam> wrote...
>[color=green]
>> This should be a valid thing to want to do, right?[/color]
>
>Generally, no. Since such a query would not be optimized properly anyway.
>Better to make the code a big more specific o be faster (and the fact that
>it will avoid bugs is the bonus feature of the approach!).
>[color=green]
>> As far as I'm concerned, coding laziness of this sort can be a -good-[/color]
>thing.
>
>Without testing and finding out you have to work a bit harder here? I do not
>think so. :-)[/color]

Clearly, you turn out to be right, but I would not have thought that for
"Like" to work properly for the case of no wildcards was an unsafe assumption.
Also, since "Like" optimizes to a bounded range search when it begins with a
constant (OK, I know that's for querying, not filtering), that's also optimal
for the case of no wildcard since it will be a bounded range with upper and
lower bounds equal.

Also, since I was trying to filter a medium-small recordset in memory, I
wasn't too concerned about performance optimization.

nospam@nospam.nospam (Steve Jorgensen) wrote in
<63eo001h78p7qgckkjcuhg6ie33vaip9bk@4ax.com>:
[color=blue]
>I'm aware that ADO does not have the problem, which is expected.
>In the case of an MDB front-end to an MDB, though, I still prefer
>to stick with DAO.[/color]

I wouldn't count it continuing to work in ADO, either.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:43ro00tp5pnmmdlchlslqkvjs4nejhhkv1@4ax.com...

(snip)
[color=blue]
> I would not have thought that for "Like" to work properly
> for the case of no wildcards was an unsafe assumption.[/color]


For me, it's a matter of not making >any< assumptions beyond what is
explicitly stated in the language documentation.

Unfortunately I don't have Access here to check. But if the documentation
for Like says that it is used with wildcards - and the syntax shows it being
used with wildcards - and the examples show it being used wth wildcards -
and there is no explicit statement that you can use it >without< wildcards -
then, there really is no justification for making the assumption in
question.

Of course, you could test it, to see if it worked the way you assumed. But
even if it did, how safe would it be, to rely upon that? If a behaviour is
not explicitly stated in the language documentation, it could easily change
in the next version, because of internal changes (for example).

To take another (better) example, it's like assuming that an "IF ... AND ...
AND ..." statement will be evaluated left-to-right, or in "short circuit"
fashion, in a language where the documentation does not explicitly state
those things. Even if you did some testing to find out how a particular
implementation worked, there is no guarantee that any future version and/or
other implementation, would work the same way.

So, my rule is - "No Assumptions!"

TC

On Tue, 20 Jan 2004 13:03:10 +1030, "TC" <a@b.c.d> wrote:
[color=blue]
>
>"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
>news:43ro00tp5pnmmdlchlslqkvjs4nejhhkv1@4ax.com.. .
>
>(snip)
>[color=green]
>> I would not have thought that for "Like" to work properly
>> for the case of no wildcards was an unsafe assumption.[/color]
>
>
>For me, it's a matter of not making >any< assumptions beyond what is
>explicitly stated in the language documentation.[/color]

But what I was couting on was the documented behavior. Microsoft acknowledges
that the current behavior is a bug.
[color=blue]
>Unfortunately I don't have Access here to check. But if the documentation
>for Like says that it is used with wildcards - and the syntax shows it being
>used with wildcards - and the examples show it being used wth wildcards -
>and there is no explicit statement that you can use it >without< wildcards -
>then, there really is no justification for making the assumption in
>question.[/color]

It says that a any non-wildcard characters will be matched exactly.
[color=blue]
>So, my rule is - "No Assumptions!"[/color]

I agree that one should not make assumptions about behavior that's not
documented. In this case, though, the documentation is clear enough,
microsoft simply didn't test their code well enough. The definition of Like
doesn't say that it will work as documented so long as at least one of the
characters is a wildcard, otherwise all bets are off.

Ok, I shouldn't have commented without reading the Help. I'll do that
tonight, & comment back if necessary :-)

TC
(off for the day)


"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:906p009l7hgcmcqe1joppgg8ehtiq3aghv@4ax.com...[color=blue]
> On Tue, 20 Jan 2004 13:03:10 +1030, "TC" <a@b.c.d> wrote:
>[color=green]
> >
> >"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
> >news:43ro00tp5pnmmdlchlslqkvjs4nejhhkv1@4ax.com.. .
> >
> >(snip)
> >[color=darkred]
> >> I would not have thought that for "Like" to work properly
> >> for the case of no wildcards was an unsafe assumption.[/color]
> >
> >
> >For me, it's a matter of not making >any< assumptions beyond what is
> >explicitly stated in the language documentation.[/color]
>
> But what I was couting on was the documented behavior. Microsoft[/color]
acknowledges[color=blue]
> that the current behavior is a bug.
>[color=green]
> >Unfortunately I don't have Access here to check. But if the documentation
> >for Like says that it is used with wildcards - and the syntax shows it[/color][/color]
being[color=blue][color=green]
> >used with wildcards - and the examples show it being used wth wildcards -
> >and there is no explicit statement that you can use it >without<[/color][/color]
wildcards -[color=blue][color=green]
> >then, there really is no justification for making the assumption in
> >question.[/color]
>
> It says that a any non-wildcard characters will be matched exactly.
>[color=green]
> >So, my rule is - "No Assumptions!"[/color]
>
> I agree that one should not make assumptions about behavior that's not
> documented. In this case, though, the documentation is clear enough,
> microsoft simply didn't test their code well enough. The definition of[/color]
Like[color=blue]
> doesn't say that it will work as documented so long as at least one of the
> characters is a wildcard, otherwise all bets are off.[/color]