Perhaps I don't know something about SQL, or perhaps something strange is going on.

I have a large-ish table (definition below) It contains 300k records.

ALL The following SQL statements return 260 result rows:

SELECT * FROM People WHERE FullName = '  '
--note the WHERE is a single space.
SELECT * FROM People WHERE FullName = '   '
--note the WHERE is 2 spaces.
SELECT * FROM People WHERE FullName = '    '
--note the WHERE is 3 spaces.

These WHERE statements are NOT the same (in my opinion!) . I am using an exact match ( = sign) not a like, and multiple spaces are not the same as a single space.

Is this a bug?

--Part of the definition of the People table:

CREATE TABLE "People"
(
"ID" INTEGER DEFAULT UID() NOT NULL,
"FirstName" VARCHAR(40) COLLATE "ANSI",
"NickName" VARCHAR(40) COLLATE "ANSI",
"LastName" VARCHAR(40) COLLATE "ANSI",
"FullName" VARCHAR(80) COLLATE "ANSI" GENERATED ALWAYS AS CAST(COALESCE(FirstName + ' ', '')
      + COALESCE(LastName + ' ', '')
      + COALESCE(' (' +NickName + ')', '') as VARCHAR(80))
)

Adam


I'm not sure if your opening statement is correct or not

At least part of the  problem is in your column definition

"FullName" VARCHAR(80) COLLATE "ANSI" GENERATED ALWAYS AS CAST(COALESCE(FirstName + ' ', '')
      + COALESCE(LastName + ' ', '')
      + COALESCE(' (' +NickName + ')', '') as VARCHAR(80))

Taking the first name as an example:

COALESCE(FirstName + ' ', '')

Either you end up with FirstName plus a space or an emptystring, ditto for the other two elements. I have no idea what adding three emptystrings together produces but testing in EDBManager it tests true against any number of spaces - but not a character. Hence my confusion about the state of your SQL knowledge!

Looking online a definition for VARCHAR is no help (https://dev.mysql.com/doc/refman/8.0/en/char.html) however a question of Stack Overflow (https://stackoverflow.com/questions/1399844/sql-server-2008-empty-string-vs-space) shows you are not alone.

I have a vague memory of something about RTRIM and comparison years ago - if I find it I'll let you know

Roy Lambert

Adam


I can't find the thread I was thinking about - it was fairly soon after the start of ElevateDB and, I think, concerned storing trailing spaces on VARCHAR columns and not being able to find things in SQL (or Delphi) if you didn't have the trailing space. You can probably guess which side of the don't store trailing spaces argument I was on. I think I remember the compromise was the one referred to in the StqckOverflow question - RTRIM both sides of the comparison. This would explain your results since you would end up comparing emptyspace with emptyspace.


Roy Lambert

Adam


I did a bit more digging and it looks like this became the "standards" approach from ElevateDB 1.02

If you want to read up about things have a look at these posts


From: Charles Tyson
Subject: CHAR vs VARCHAR fields in query parameters
Date: Sat, 29 Dec 2018 04:16:11 -0500
Lines: 15
Message-ID: <F96BF3F4-C620-47AF-95CD-0AEF2D0EA6F9@news.elevatesoft.com>
Newsgroups: elevatesoft.public.elevatedb.sql
Path: www.elevatesoft.com

Message-ID: <05AF11E2-B255-45DF-AB99-4AC78DE5728D@news.elevatesoft.com>
Date: Tue, 6 Mar 2007 18:21:13 +0000
NewsGroups: elevatesoft.public.elevatedb.general
Followup-To: elevatesoft.public.elevatedb.general
From: Roy Lambert <roy.lambert@skynet.co.uk>
Reply-To: Roy Lambert <roy.lambert@skynet.co.uk>
Return-Path:  <roy.lambert@skynet.co.uk>
Subject: VARCHAR and trailing blanks
X-Newsreader: TMaN from Roy Lambert - File Version: 1.0.0.0 N
Path:news.elevatesoft.com!news.elevatesoft.com
Lines: 2

Path: news.elevatesoft.com
Message-ID: <F9E80486-7F4C-4453-93D4-39F538A13105@news.elevatesoft.com>
From: "Ole Willy Tuv" <owtuv@online.no>
Newsgroups: elevatesoft.public.elevatedb.general
Subject: Comparison problem/bug
Date: Sat, 10 Mar 2007 12:39:46 +0100

Message-ID: <EED0A840-1D61-487B-8DEA-82793F850E1E@news.elevatesoft.com>
Date: Mon, 21 Jan 2008 17:02:15 +0000
NewsGroups: elevatesoft.public.elevatedb.general
Followup-To: elevatesoft.public.elevatedb.general
From: Roy Lambert <roy.lambert@skynet.co.uk>
Reply-To: Roy Lambert <roy.lambert@skynet.co.uk>
Return-Path:  <roy.lambert@skynet.co.uk>
Subject: VARCHARS & TRIMing

Roy Lambert

Roy

Thank you so much for these responses. Really helpful!