Select typeof (hex (Null)) Result;

Result
text

It should be Null.

Also, why despite of requests by large number of people around the world, the unhex() function is not available as core function?

It should be Null.

In the parlance of Wikipedia: "citation needed"

The docs clearly say it returns a string:

The hex() function interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal rendering of the content of that blob.

Also, why despite of requests by large number of people around the world,

Again: "citation needed". A search for "unhex" in this forum reveals exactly one relevant thread (not including this one).

... the unhex() function is not available as core function?

Keith succinctly explains why the hex transformation is not generically reversible in 25da10332dd9a1eb.

The result of hex(null) is not a null. It is an empty string.

SQLite version 3.39.3 2022-09-05 11:02:23
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select count(*) where hex(null) is null;
0
sqlite> select count(*) where hex(null) = '';
1

Interesting

Don't you think by analogy type of (hex(null) ) should also be null.

You're comparing apples and oranges though, IMO.

max() is polymorphic, in a way, since will return different types based on arguments.

While hex() by design always returns text, whatever its argument's type.

So it could have returned a null for a null input, but it's just as correct,
given the semantic of that function, to return an empty text value too.

If you really need the result to be null then just wrap it in a nullif():

SQLite version 3.39.3 2022-09-05 11:02:23
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select typeof(nullif(hex(null),''));
null
sqlite> select typeof(nullif(hex(42),''));
text

What about

typeof (upper(Null))

typeof (lower(Null))

typeof (length(Null))

These are not polymorphic functions. Typeof these is also NULL.

Thanks

I'm not sure where you're getting at, honestly.

All I'm saying is that both returning null and returning '' for hex(null) are reasonable choices.

I tend to agree that returning null might have been a better choice, for consistency's sake, as you pointed out.
But that's water under the bridge, as they say. A long time ago, the behavior of hex(null) was decided, and it's not going to change now.
That would be bad for backward-compatibility, obviously. So just learn to live with it? :)

These are better workouts, thanks.

You seem right, not changing the code will be better idea. I'll manage by queries given by David Raymond above.

Excuse me for raising the issue again but, this bug is irritating me and I think this must be fixed. Here are the logical points for reasoning.

1. Almost all functions and operations on Nulls yield Null.

2. There is no function in SQLite that can Cast Null as text but, this hex function is doing so.

Also I don't think that this is water under the bridge or water over the dam. It can be corrected by offering it as Pragma statements, for example:

Pragma legacy_hex = 0

For the reputation of SQLite, bugs must be fixed.

Excuse me for raising the issue again but, this bug is irritating me

A "bug" is incorrect behavior. What's irritating you is that the designers explicitly chose and documented behavior which is "differently correct" than your notion of correct.

For the reputation of SQLite, bugs must be fixed.

Bugs, yes, but not behaviors which work exactly as they are documented to. That is, by definition, not a bug.

Probably, illogical behaviour is a bug and documented bug still remains a bug.

What do you say about my two reasoning points?

It's not a bug. A bug is when you expect X but get Y. Eg, turn the steering wheel left to turn left, but the car turns right. If a function returns a textual value, this isn't a bug. If the function returned a numeric function or a date, then that would be a bug. You're getting text. No bug. If the hex function returns a textual value, the 'typeof' is always going to be text.

but, this hex function is doing so

It's not though. The function returns a textual value. So you're not getting the typeof a null as the function returns a textual value. The typeof a textual value is text. As its a textual value.

You can code around this, so unsure why it's still an issue?

What do you say about my two reasoning points?

That they are irrelevant because, as it was previously put, hex()'s behavior is water under the bridge. The only person who is known to be losing sleep over its documented, correct, and non-bug behavior is you. That's not justification enough to add a new pragma which has to be supported and maintained for the life of the project (officially until at least 2050). Given sqlite's unwavering requirement for 100% branch test coverage, every new line of C code has a maintenance burden attached to it which most projects do not have, and the personal preferences of a single user, or a disproportionately loud minority of users, do not justify that burden.

Almost all functions and operations on Nulls yield Null.

Yeah, and some don't.

There is no function in SQLite that can Cast Null as text but, this hex function is doing so.

The Hex function, like every function except "CAST" or "CONVERT" (in other SQL engines), doesn't cast values as anything.

When you use the function "HEX()" you specifically ask the engine to show you the bytes as a string of hexadecimal characters. If the bytes in the target value is 0x102F then you expect the text string '102f' back, and if there are no bytes in the target value, you expect the empty string '' back, but you never ever expect anything other than a text string back.

I use this function quite extensively to read bytes that I can transfer in text-based structures (like JSON), I would be quite irritated by it suddenly starting to return something other than a string showing the exact bytes (or lack thereof) for the target value (or non-value).

In Hex, a Zero byte will return '00', the only possible way to represent a null (or no-value) [Edit: as a hex string] is by showing no bytes, this is useful. Why are you intent to make a Text-function return anything other than text? The only reason some other functions HAVE to return null is that they have no other way to display "non-value" or "null-value" (whichever term you like best). HEX() does not have that problem, it has a very clear way to display those.

Your arguments amount to nothing more than "I don't understand why it's like this", but let's imagine you did have a good reason for wanting NULL returns, it still isn't how it works and is documented to work, and so it still isn't a bug. It also isn't a "documented bug" as you seem to suggest, it works as documented, meaning it is not a bug. Your contention seems to lean more towards "design flaw" - like you could argue cars would have better traction if they all had six wheels rather than four, and sure, you'd have a point, but there would be downsides to that too. Most importantly, you may call a 4-wheeled vehicle "a bad design" if you like, but you can't call it a bug - it is exactly how the designers intended it to be, documented it to be, and advertised it to be.

Also, why despite of requests by large number of people around the world, the unhex() function is not available as core function?

Quick curiosity questions:

1. Which engines support Unhex()?
2. In these engines, what is the resulting value of Unhex('') - i.e. getting the Unhex() value of an empty string?

Given sqlite's unwavering requirement for 100% branch test coverage, every new line of C code has a maintenance burden attached to it

OK

I use this function quite extensively to read bytes that I can transfer in text-based structures (like JSON)

OK

The unhex() issue doubt was resolved above.

life of the project (officially until at least 2050)

Curious! will SQLite not be maintained after 28 years from now?!

The official support is until 2050. You can sleep soundly that it'll be supported until 2050. That's the 'promise'. It may be maintained after that, but that's waaaaay off into the future - who knows what computers will be like then - may have to stick them up your nose or they'll need plums and apricots to work.

You might want to see this page. http://www3.sqlite.org/lts.html

life of the project (officially until at least 2050)

Curious! will SQLite not be maintained after 28 years from now?! They just are not promising at this time to maintain it after then. They are not saying that it WILL have support dropped.

Perhaps at that point, they will drop support for some ancient compatibilities. Maybe in the near future (like the next decade or so), we will get an announcement of the extension of support for "modern" SQLite, and a listing of features that will be dropped from support.

Or, perhaps in the "near" future, an SQLite4 will be released that is compatible with "modern" SQLite3, and that will be the new long-term support path.

http://www3.sqlite.org/lts.html

Interesting, thanks

OK, thanks