Hacker News Reader

Comment by jwitthuhn | Hacker News Reader

This is missing my favorite oracle quirk: Empty strings are equivalent to null. Trying to insert an empty string into a NOT NULL column will fail, which took me a while to understand the first time I saw it happen.

View on HN

> Empty strings are equivalent to null.

No, they are not. Empty strings are completely different from null, and if you go returning them or testing for equality, everything will break by random some single-digit percent of the time. The same for concatenating, taking the length or iterating.

I imagine there's some deterministic procedure to decide what leads to an empty string and what leads to null. The one thing I know is that if you insert it on a table, you will always get null.

Fascinating. Of course it would be too easy if they were strictly the same!

It seems like reading the tale of a greek programmer cursed by the gods to work with madness itself.

One of oracle quirks for me is varchar2. What happened to varchar1? Nobody knows.

TIMESTAMP WITH LOCAL TIMEZONE. Never again.

Let me guess, did you get hit with more than one 3:00am?

Let's not forget that Oracle DB uses the system timezone as the datatype storage (not sure what other database do to be honest) so unless you were running your DB machine as UTC by default their timestamp storage would be ambiguous for DST shift and that's their default. Why wouldn't they store everything in UTC and use the system timezone for parse/formatting I have no clue.

I've been pushing to run ALL servers as UTC for a few decades now... always store/transmit date+time as UTC as well. If it's mapped to a locale, pair it with that locale... let the client translate to/from local.

Oh sorry, are you looking to insert 4001 characters in your varchar2? Buckle up kids, things are gonna get rough.

Or maybe 3999 chars, of which a couple are two-byte. More fun.

Oracle supports up to 32k VARCHARs since 12.1 released back in 2013.

It just needs to be enabled: https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-093...

varchar1 was varchar. I should say "is", since varchar is still supported, they just recommend varchar2 since varchar "might change" (probably not...)

That one actually changed already. If you follow the new recommendations, varchar is as good as varchar2.

Good to know! I haven't had to touch an Oracle database in almost 15 years, but one never knows.

So how do you insert an empty string into a NOT NULL column? Please don't tell me that it's not possible.

its...

....

sorry

not possible (can't store empty string in a NULL col either)

for reals

Just store the string "empty" and hope there are no nihilists who've changed their names to match. Simple!

Use vertical tab, or another whitespace or field separator character, like EOF etc. Generally something a modern keyboard won't be entering, and won't generally be transmitted over the wire.

Or just prefix everything so that your string domain doesn't shrink? Then you don't have to worry what your users enter.

Real programmers encode emptiness as ZZZ...

(I once worked maintaining a MUD that used internal memory management and marked block terminals (which were unnecessary since it stored the length of blocks it had allocated) with ZZZ)

ZZZ? EEK!

First you accept that null and an empty string are the same thing. Then you insert a null.

But they are not. Empty string means "we know this is empty", Null means "We don't know".

For example, I have a second name. Some people don't have second names. And some records we might not even know if such exists. Null means "unknown". Empty string cannot be equal to null.

Somebody hasn't learned how to love big brother yet.

Bad software punishes you for doing things right.

It's like how at one time, "0" was not yet invented and had to be invented and explained, and it was a revolution for math. It's a quantity just like 5 or 244, but for when the amount happens to be none.

We need that again, another new 0 concept to add to 0, to distinguish between "set-to-0" and "not-yet-set".

Maybe 2 new concepts, since null is also different from 0. 0 is a value, null is the absense of a value.

Not just as an idiom or implementation detail in a programming language, but as a general concept that may be used anywhere in life.

Without it, we have exactly these confusions and ambiguities and differences of opinion about how to do something or what something means or what something should mean.

Obviously you prefix every string with a space and when reading, strip the leading space. Then " " is your empty string. ;)

For real though, no idea. I'm glad that I never had to touch Oracle.

Either make the column nullable, or apply local workarounds and folklore. E.g. I've seen "person" records with surname = '.' in certain databases, for people who don't have one.

Honestly that's bad db design anyways. If empty string is a valid value then you don't want NOT NULL anyways. This is a table structure constraint, not a variable declaration. NOT EMPTY would have been a better syntax imo but that's a whole can of worms.

Empty string means "we know its blank" null means "we don't know".

When it comes to datatyping, (the whole point of data types in a db), null is its own datatype, so forcing the allowance of nulls to get blank strings is kinda stupid and only causes software/application level bugs.

An empty string is a perfectly valid neutral element of the free monoid over the alphabet in question. It's also not a NULL value for this reason. If a NOT NULL column of strings allows for all strings except for a very important one, then that's a very weirdly special-cased design of the RDBMS.

It was years ago I first came across this, after I'd already spent a lot of time with MySQL and MSSQL, and some time with PostgreSQL and Firebase. But I still remember having a massive WTF moment!

Reply on news.ycombinator.com