Mailing List Archive


Back to the month index Back to the list index
Brendan Quinn (brendan@nexus.sofcom.com.au)
Mon, 27 Jan 1997 17:22:42 +1100 (EST) 

From: Brendan Quinn <brendan@nexus.sofcom.com.au>
Message-Id: <199701270622.RAA09350@nexus.sofcom.com.au>
Subject: [mSQL] BUG REPORT: msql 2.0b3 CLIKE error (with fix)
Date: Mon, 27 Jan 1997 17:22:42 +1100 (EST)

Hi Bambi and everybody,

Sorry if this has been addressed before but I'm not subscribed to the list.
>From my quick search of the archives it wasn't mentioned.

Here at Sofcom we found a bug in the CLIKE pattern matching algorithm, which
we have fixed.

THE PROBLEM:

The problem concerns CLIKE patterns which contains multiple trailing %
characters. This is most commonly encountered as a search such as
" WHERE field CLIKE '%%' ", when the field in question is the null string.
For example, a "WHERE field CLIKE %$keyword%" where $keyword is empty.
The % operator is defined as matching zero or more characters, so any number
of %'s should match the null string.

The most simple mSQL instructions to see the bug are as follows:

    create table blah ( field1 char(20), field2 char(20)) \g

    insert into blah values ('blah1', '') \g
    insert into blah values ('blah2', '') \g
    insert into blah values ('blah3', '') \g
    insert into blah values ('blah4', '') \g

    select * from blah where field1 like '%blah%' and field2 clike '%%' \g

The select should return all four records, but instead it returns none.

THE SOLUTION:

This was my favourite type of bug: easy to find once you know what's wrong.
Here's a context diff of the offending file, /msql-2.0-B3/src/msql/types.c:

*** buggytypes.c Tue Jan 14 02:16:16 1997
--- types.c Mon Jan 27 16:41:03 1997
***************
*** 182,192 ****
                  {
                          if (*cp1==0)
                          {
! if (*cp2 == 0 ||
! (*cp2 == '%' && *(cp2+1) == 0))
! {
                                          return(1);
- }
                          }
                          return(0);
                  }
--- 182,191 ----
                  {
                          if (*cp1==0)
                          {
! while (*cp2 == '%')
! cp2++;
! if (*cp2 == 0)
                                          return(1);
                          }
                          return(0);
                  }

And that's all there is to it!

Hope it helps you all! Good luck with the rest of the work on the server, it
has proven invaluable for us here!

Yours,

Brendan Quinn, Systems Programmer (brendan@sofcom.com) and
Jason Watson, Programmer and Network Admin (jase@pobox.com)
Sofcom Internet Publishers 

--
Brendan Quinn                  |       brendan@sofcom.com
Sofcom Distributors Pty Ltd    | 
Level 1, 399 Riversdale Rd     |    Phone:  +61 3 9882 3811
Hawthorn East 3123             |    Fax:    +61 3 9882 7619
Victoria, AUSTRALIA            |     http://www.sofcom.com
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com.  Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!