Dear Support!

I'd like to know something about index using in query.

Sometimes I need to handle the NULL values as somehow to show them in one filter (otherwise they would be invisible).
I used COALESCE function for it.

where coalesce(NeedToTransport, False) = True

or

where coalesce(BasicPrice, 0) > 100

I read in more docs that most of RDBMS cannot use indexes on functions.
But coalesce with static constant might be an exception, because NULLs could be replaced in index search to use range logic.

What's the technic used by EDB's query engine?

Or I need to forget the indexes when I want to use coalesce?

Thanks for the information!

Also here the execution plan will help you.

Uli

Uli


I thought I'd have a look and ElevateDB does a rowscan if you use coalesce.

<rant on>

I've found yet another reason to hate the fact that in SQL  emptystring <> NULL

</rant off>


Roy Lambert

Roy

<rant on>
I've found yet another reason to hate the fact that in SQL  emptystring <> NULL
</rant off>

For context here, in RoySQL EmptyStr and NULL would be the same thing. This means that if you have a typical Customers table and want to do some routine personal detail checks for missing values you might run a query like:

SELECT * FROM CUSTOMERS WHERE IsValidEMAIL(EmailAddress) and Surname IS NULL

...and use the result to generate some emails.

Not a problem in standard SQL as you generate a list of customers with a missing surname, however with RoySQL you get the reply:

"For the 500th time I am BURMESE! I don't have a surname, have never had a surname and will never have a surname!!! Stop asking me the same stupid, racist, culturally imperialistic question every month. If your other products are as useless as your database systems seem to be I shall be finding another supplier."

Your actual data is correct of course. The person entering it correctly set the surname field to EmptyStr since this data item is positively known to be not applicable to this customer. However RoySQL is unable to distinguish between unknown/missing and not applicable/non-existent so you have to start building in special "flag" fields or insert bogus "magic" surnames like "<Not Applicable>" with predictably laughable consequences for your other mailings when someone forgets to filter them for special handling.

EmptyStr is: "I positively know this to be an empty value", NULL is: "I have no idea what this value is or should be". They are not the same thing and standard SQL recognises this fact.

Eryk

Eryk Bottomley wrote:

> For context here, in RoySQL EmptyStr and NULL would be the same
> thing.

Interesting that some cultures don't have a surname. What do they have
instead? Surely some sort of family name must be used?

EDB does of course have the option to treat empty string as NULL, but
it defaults off. In simple code it is convenient, but I think it is
better to have them separate, as you suggest. Gotta learn the new ways.


--

Matthew Jones

Matthew

> Interesting that some cultures don't have a surname. What do they have
> instead? Surely some sort of family name must be used?

Traditionally (and consequently still today in the case of cultural nationalists) a Burmese name is not just a single name but a single syllable.

The first Prime Minister of modern Burma/Myanmar was U Nu and the third Secretary General of the United Nations was U Thant. In both cases "U" is a honorific like "Mr", not really part of the name.

Where it is essential to conform to western preconceptions Burmese may manufacture a multi-part name by bolting on bits of their ancestor's names[1] but there is no defined structure for this, several may exist at once[2] and they can pretty much be changed at will.

Eryk

[1] Aung San Suu Kyi is probably the most famous contemporary Burmese in the west but that is a manufactured name taken by bolting her Grandmother's (Suu) and Mother's (Kyi) name onto the full name of her Father (Aung San). Prefixing her Father's name is political and there is nothing traditional about the adopting a Grandmother/Mother combination. Without the politics and western preconceptions her name would likely be Daw Suu where Daw is a honorific along the lines of "Lady" or "Madame".

[2] In English terms:
John David's Son
John Sarah's Son
John Colin's Grandson

...might all simultaneously and quite correctly refer to exactly the same person.

Eryk

><rant on>
>I've found yet another reason to hate the fact that in SQL emptystring <> NULL
></rant off>
>
>For context here, in RoySQL EmptyStr and NULL would be the same thing. This means that if you have a typical Customers table and want to do some routine personal detail checks for missing values you might run a query like:
>
>SELECT * FROM CUSTOMERS WHERE IsValidEMAIL(EmailAddress) and Surname IS NULL
>
>..and use the result to generate some emails.
>
>Not a problem in standard SQL as you generate a list of customers with a missing surname, however with RoySQL you get the reply:
>
>"For the 500th time I am BURMESE! I don't have a surname, have never had a surname and will never have a surname!!! Stop asking me the same stupid, racist, culturally imperialistic question every month. If your other products are as useless as your database systems seem to be I shall be finding another supplier."
>
>Your actual data is correct of course. The person entering it correctly set the surname field to EmptyStr since this data item is positively known to be not applicable to this customer. However RoySQL is unable to distinguish between unknown/missing and not applicable/non-existent so you have to start building in special "flag" fields or insert bogus "magic" surnames like "<Not Applicable>" with predictably laughable consequences for your other mailings when someone forgets to filter them for special handling.
>
>EmptyStr is: "I positively know this to be an empty value", NULL is: "I have no idea what this value is or should be". They are not the same thing and standard SQL recognises this fact.

I have to give you a good mark for coming up with a justification I haven't seen before

Surname validation is something I've had to research 3 times, once I can't remember what it was for, once for a fleet management system and once for data deduping for the British Gas sale. Interestingly enough this issue didn't appear then, or on any other of the commercial systems I've been involved with over the years. My commisaerations on your having worked on such a systems.

I apologise for using Wikipedia as a source but it is fascinating:

---------------------------------------------------------------------------------------------------------------------------------------
Burmese names lack the serial quality of most modern names. The Burmese people have no customary patronymic or matronymic system and thus there is no surname at all. In Burmese culture, people can change their name at will, often with no government oversight, to reflect a change in the course of their lives. Also, many Burmese names use an honorific, given at some point in life, as an integral part of the name. However, in modern Burma many of these traditions are changing.
---------------------------------------------------------------------------------------------------------------------------------------

How did your system address this issue? Single name CLOB column so they can have as long a name as they wanted (or as short obviously)? How did you notify users on screen (or on printout) of the difference between NULL and emptystring?

Over the years I've been moaning about this bit of SQL (I know I don't have deep enough pockets to get it changed) I've only ever had two people respond with genuine systems, possibly you're the third.

The first one was on these newsgroups and I think it was David Cornelius (could be wrong). He was using wether a column was null to say if the answer to a question had been entered or not. Unfortunately he'd provided no means to reset any entry to null so rather than tracking wether or not an answer had been given he was tracking wether or not ANYONE had ever typed ANYTHING into that field. SO not a particularly good example. The system would have been better designed with a boolean column to indicate its status.

The second was the infamous "we need to know if this individual has a middle name or not". After my asking the question it appeared that again it didn't have the option to reset a column to null. This was being used as part of a legal process to track people defaulting on contracts. Interesting. So someone giving a middle name on occasion A and not on occasion B was two different people. Wonderful.

I'm really hoping that you're is a real example and has been designed correctly because I'd love to believe that there is a justification for null <> emptystring and all of the additional messing around that has to be done because of it.

So tell me that in the system you're referring to above that the user can't just "oh that's not a surname" and backspace it out leaving an empty string rather than a null, and that they have a mechanism other than typing it in and then deleting it to create an emptystring for those instances where its know not to exist.

Also while you're at it please tell me what's wrong with special "flag" fields? Isn't it better to have something there with a definitive meaning. eg this person is Burmese and doesn't need a surname (but one would be nice if we could get it) rather than "oh I just can't be bothered to enter anything here - its easier and the system won't care"!

I await your reply.

Roy Lambert

Matthew


>EDB does of course have the option to treat empty string as NULL, but
>it defaults off. In simple code it is convenient, but I think it is
>better to have them separate, as you suggest. Gotta learn the new ways.

How long do you think it will be before you start defaulting strings to ''?

My solution was to mod TEDBTable to set empty strings to null before they hit the disk.

Roy Lambert

Roy Lambert wrote:

> How long do you think it will be before you start defaulting strings
> to ''?

You already showed me the light with defaults, so "never"! 8-)

--

Matthew Jones

Eryk Bottomley wrote:

> Where it is essential to conform to western preconceptions Burmese
> may manufacture a multi-part name by bolting on bits of their
> ancestor's names[1] but there is no defined structure for this,
> several may exist at once[2] and they can pretty much be changed at
> will.

I wonder if this is just a thing that is part of "developing
civilisation" or some sort of "world imperialisation".


> [2] In English terms:
> John David's Son

Indeed, this is something we went through as we grew into larger
communities.

--

Matthew Jones