moving to beaker devel
On Tue Nov 10 19:03:03 2009, kbaker(a)redhat.com wrote:
> > > Does it handle empty string, whitespace string and NULL? >
> > I often got caught by these various ways of representing nothing.
> >
> > Before it was just checking for a an empty string/int So for
> > the 'is', If the value was whitespace, it would just do a
> > regular SQL query with = '' Now it will do 'IS NULL', which
> > will actually work with our outer join where the right table
> > has null values :-)
> >
> > Also for the 'is not', now if the value is blank than it will
> > do 'IS NOT NULL' (which again, will now work with our outer
> > joins). Also with 'is not', if we do say ['is not']
['foo']
> > the SQL will be != 'foo' OR IS NULL.
>
> <warning>: wandering off into the weeds.</warning>
>
> What do I do if I want to search for the empty string ''? Or for
> whitespace ' '?
>
> not saying it's a major issue, just if you considered it. As
> applications age and values get added/deleted they can fall off
> the radar. Consider
>
> time-0 - field created as NULL
> time-1 - field set to 'Blah'
> time-2 - field cleared to ''
>
> After time-0 searching for field IS NULL will return the record
> but after time-2 it will not. Depends on the application on what
> NULL versus empty string versus whitespace ' ' means. I guess
> I'm trying to warn against mapping undefined, empty string and
> whitespace to NULL at the db level. It may be safer to say (field
> IS NULL OR field = '') or provide UI to differentiate what you're
> searching for
>
> field IS NULL
> field IS NOT NULL
> field IS empty string
> field IS not empty string
You're right about the first part. It's a 2 line fix. Silly oversight
on my behalf.
In regards to the UI aspect of it. Firstly, will there be real
world cases of needing to look for say a System that has an empty
CPU/Processor entry vs not having a CPU/Processor entry at all?
Also, representing the NULL in the value is hard. We have an
operation field, and then a value field (which is just a text
field). How do we differentiate between the user wanting NULL or
wanting empty string in the textfield? Empty string is easy, the
textfield remains blank. For NULL though, would they just type NULL
in the textfield, and what if someone actually wanted to search
for a field which contained the string 'NULL' ? It could be error
prone and confusing.
If there isn't going to be a compelling use case for it, it may
not be worth the added complexity.
Yeh, this conversation is getting a little out there so I'll attempt to
wrap it up. :)
If the application is the only thing writing to the database then it can
ensure that my example would never happen. So it would become
time-0 - field created as NULL # created
time-1 - field set to 'Blah' # modified
time-2 - field set to NULL # cleared
Then you can keep the UI & coding simple by assuming that NULL is not
represented by empty strings or white space.
--
Kevin Baker
Manager Engineering Operations, Westford +1 978 392 2442