In 2.29b1, this query works, finding 12 matches (UNAME is a person's name e.g. 'POUND JOHN'):

select cliuid, fileno, uname from clients
where uname like 'PO%'

But when I changed 'PO%' to a parameter -- where uname like :x1 -- the query completes but never finds any matches. The value of parameter x1 was correctly set to 'PO%'.

However, the query will find one match if the parameter holds the full name...
NameQuery.ParamByName( 'x1' ).AsString := 'POUND JOHN%'

After hours of puzzlement, I noticed that UNAME was defined as CHAR(40).  I changed it to VARCHAR(40) and the parameterized query works, finding all matches for x1 set to 'PO%'.

Is it expected behavior that a literal "like 'PO%'" should behave differently from "like :x1" for CHAR fields?  

I guess I will spend tomorrow ruthlessly purging CHAR fields from my tables!

Charles


I never really saw the point in CHAR, pretty much all my string fields are VARCHAR (OK I do have a few 1 character fields used as indicators or flags).

Here's a wild guess as to why it fails:

UNAME was CHAR(40) that means that any UNAMEs less than 40 characters long are right padded with spaces.

AsString does not map exactly to either VARCHAR or CHAR so ElevateDB has to coerce the input to match the type of the column its comparing to.

What you're comparing using wildcards therefore is 'POUND JOHN..............................' to 'PO%.....................................' (using dots as spaces so you can see them)

I'm probably totally wrong and Tim will shoot me down in flames but its my best guess.

I hate loath and detest trailing blanks - I'm even less keen of leading blanks and don't get me started on nulls!

Roy Lambert

Thanks, Roy.  If it matters for further diagnosis, this is an ANSI table and the test program was written in Delphi 2007.

Charles


We're going to have to wait for Tim to shake off the fun an frivolity of Christmas to give us the real explanation.

Roy Lambert

Charles,

<< After hours of puzzlement, I noticed that UNAME was defined as CHAR(40).  I changed it to VARCHAR(40) and the parameterized query works, finding all matches for x1 set to 'PO%'.

Is it expected behavior that a literal "like 'PO%'" should behave differently from "like :x1" for CHAR fields? >>

Yes, CHAR columns are *fixed* character columns, so they behave as though all trailing space (up to the max length of the column) are filled with spaces (#32):

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=String_Types

"A string value with a fixed number of characters. If the length of the string value is not specified, then a length of 1 is used. The maximum length is 1024 characters. When assigning a value to a CHAR type value that is smaller in length than the specified length, the value being assigned will be padded with spaces to the specified length. For example, if you have a column defined as:

MyColumn CHAR(20)

If you were to assign the value 'Test' to the column, then the MyColumn column would contain the value 'Test'+<16 Spaces> after the assignment."

After changing to VARCHAR columns, you're going to want to trim any spaces that may exist in the existing column values.

Finally, this is all part of the SQL standard and a throwback to earlier times, so don't blame me...

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

I get that CHAR fields are stored differently in the table.  My question is why one query of a CHAR field succeeds, and the other fails.  Both succeed if the queried field is VARCHAR.

Success = select * from Test where charstring like 'E%'
Failure = select * from Test where charstring like :x1
   (where parameter x1 is set to 'E%'

Here's a screenshot of the test program:

Thanks,
Charles Tyson

Charles,

<< I get that CHAR fields are stored differently in the table.  My question is why one query of a CHAR field succeeds, and the other fails. >>

*All* CHAR values are handled the same way in terms of storage and coercion into the defined size of the CHAR column, and parameters are no different (the default parameter type is derived from the left-hand side of the operation).  Therefore, in your case the parameter is being coerced into this:

'E%<38 spaces>

which doesn't match any of the row values.

In terms of LIKE and CHAR columns, this doesn't make a lot of sense.  But, in general, CHAR columns don't make a lot of sense and are, like I indicated before, not very useful except in a very small number of cases.

Tim Young
Elevate Software
www.elevatesoft.com

FWIW I have found this conversation most enlightening.

Tim


>In terms of LIKE and CHAR columns, this doesn't make a lot of sense. But, in general, CHAR columns don't make a lot of sense and are, like I indicated before, not very useful except in a very small number of cases.

I almost agree with you but I'd say leading and trailing spaces are the things that don't make sense. Its very similar to my dislike of NULL - computers can see the difference but us poor mortals can't.


Also when I become master of the universe I'm going to ban duplicate spaces in all key fields!!!!!!

Roy