adding a concatenated field in an SQL query breaks LIKE

[Dr. Hawkins]

On Sat, Jul 12, 2014 at 6:11 PM, Peter Haworth <pete at lcsql.com> wrote:

> SQLite is case sensitive so "bank" is not the same as "Bank". You can fix
> that either by defining the column containing "bank" to be COLLATE NOCASE
> or use the UPPER function around the last concatenation, then LIKE
> '%BANK%'.
>

It doesn't seem to be once LiveCode is done with it :)

Having "alder" in my entry field  gives the query

SELECT uniqDna, cname, cadr, reason, asset FROM vader_darth______001_dna
WHERE ((cadr NOT NULL) AND (chrType<>'K') AND ((cname ||cadr  || reason)
LIKE '%alder%'  )) ORDER BY cname ASC, cadr ASC;

Which yields

808    Alderan Nobility    Hidden quite well,
thank you!    Guarantee of deposits at Bank of Aldaran

(which is the correct result).

"ban" produces query

SELECT uniqDna, cname, cadr, reason, asset FROM vader_darth______001_dna
WHERE ((cadr NOT NULL) AND (chrType<>'K') AND ((cname ||cadr  || reason)
LIKE '%alde%'  )) ORDER BY cname ASC, cadr ASC;

and result  (vtab replaced with double-cr)

808    Alderan Nobility    Hidden quite well,
thank you!    Guarantee of deposits at Bank of Aldaran

592    Bank of America    4161 Piedmont Pkwy
Greensboro, NC  27410-8110    through
credit card

601    Bank of America    attn: Bankrutpcy
NC4-105-0314
Box 26012
Greesnboro, NC  27420-6012    Suddenly, on a dark and stormy night, Audrey
dreamed of Seymore    Forest Moon of Endor

596    Citi Cards    Centralized Bankruptcy
Box 20483
Kansas City, MO  6419    through
credit card

595    Merrick Bank    Box 5000
Draper, UT  8240    through
credit card

However, when I add one more concatenated field for the LIKE to search
through, I get the query (which seems correct)

SELECT uniqDna, cname, cadr, reason, asset FROM vader_darth______001_dna
WHERE ((cadr NOT NULL) AND (chrType<>'K') AND ((cname ||cadr  || reason)
LIKE '%alde%'  )) ORDER BY cname ASC, cadr ASC;

But I get only

601    Bank of America    attn: Bankrutpcy
NC4-105-0314
Box 26012
Greesnboro, NC  27420-6012    Suddenly, on a dark and stormy night, Audrey
dreamed of Seymore    Forest Moon of Endor

(the third result from the other Query).

There are no indices created; the table in memory comes from

    "CREATE TABLE " & dhtbl_dna  & \
   " (uniqDna integer primary key , usr VARCHAR(20), tstmp TEXT, "  & \
   the stCrDbStr of stack "dna" & " , ch BOOLEAN, scr TEXT );"  & cr

(where the stCrDbStr is just a bunch of name/type pairs generated elsewhere)

What I'm seeing is that by concatenating one more field (reason) to the
prior concatenation of three fields, (correct) matches which previously
occurred no longer match.  (reason & asset are VARCHAR 254 & 154, in case
that matters)

It comes down to A matching long string B as a substring, but not once B
gets longer.
-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462