Kris Kringle the Database – The Christmas MYSTERY – Learning is not a spectator sport

Kris Kringle the Database – The Christmas MYSTERY

December 24, 2024

1–2 minutes

Here’s a strange but true puzzle for you. See if you can work out the solution…

Riddle me this – I created a brand new schema, and in that schema I created a single table called T with a single column called DATA.

With some simple PL/SQL I inserted a 40byte string into that table



SQL> declare
  2    x varchar2(40 byte) := ...
  3  begin
  4    insert into t values (x);
  5  end;
  6  /

PL/SQL procedure successfully completed.

But when I checked the length of my string in the table I got this



SQL> select length(data) from t;

LENGTH(DATA)
------------
          52

Well that was unexpected. So I tried a shorter string, being only 20 bytes long



SQL> declare
  2    x varchar2(20 byte) := ...
  3  begin
  4    insert into t values (x);
  5  end;
  6  /

PL/SQL procedure successfully completed.

But that one came back as even longer!


SQL> select length(data) from t;

LENGTH(DATA)
------------
          58

What on earth is going on here?

Just to prove there’s no funny business, I can assure you that

There is only this table in the schema
There are no triggers on the table
There are no special multibyte characters in the string. No emojis etc etc. All good ol’ fashion single byte ASCII
It is not a view or a synonym, there are no INSTEAD OF triggers etc
There are no global DDL triggers which could do some funny business
There is no SQL patch or SQL translation tricks rewriting the query
No RLS / VPD / FGAC / redaction etc etc.
No ALTER SESSION commands executed, no NLS settings changed before or during

So how is this happening?

(I’ve temporarily turned on comment moderation so if think you have the solution, feel free to post a comment, and I’ll approve them after people have had a chance to explore for themselves)

Ho Ho Ho! Merry Christmas!

34 responses to “Kris Kringle the Database – The Christmas MYSTERY”

Matthias Rogel

December 24, 2024 at 8:48 pm

did you write your own “length”-function thus overwriting standard.length ?

(not sure if it’s possible though and cannot test it right now)

Reply
1. Connor McDonald
  
  December 24, 2024 at 9:36 pm
  
  No, there are not nasty override tricks here
  
  Reply
Kaley Crum

December 24, 2024 at 10:08 pm

I’ll bet that the DATA column is not a VARCHAR2…and what we’re seeing is the result of some kind of implicit type conversion.

e.g.

SQL> create table t
2 (
3 data timestamp with time zone
4 );

Table T created.

SQL> declare
2 x varchar2(20 byte) := ’24-DEC-24 12.00.00PM’;
3 begin
4 insert into t values (x);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select length(data)
2 from t;

LENGTH(DATA)
————
44

NLS settings and data type could be modified to get the desired length (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, etc).

Reply
1. Connor McDonald
  
  December 24, 2024 at 10:37 pm
  
  Excellent hypothesis but there are no dates, timestamps, or timestamps with time zones at play here.
  
  And ultimately, you’d expect all input to result in a common length output which is not the case here.
  
  I’ve augmented the conditions to indicate no NLS tricks
  
  Reply
MaximDemenko

December 24, 2024 at 10:49 pm

Well , its all about implicit data conversion – as i suggest – , similar effect can be achieved with number (and expontential notation), but not so big deviations, with json it is more easier – i didn’t get exact you values, but there is room for variations

create table t(data blob CHECK (data IS JSON FORMAT OSON))

declarex varchar2(40 byte) := ‘{key1:”val1″,key2:”val2″,key3:”val3″}’; –37 charactersbegininsert into t values (x);end;/

select length(data) from t –yields 63

declarex varchar2(40 byte) := ‘{key1:[10,20],key2:[30,40],key3:[3,1,5]}’; –40 charactersbegininsert into t values (x);end;/

select length(data) from t –yields 89

Regards

Maxim

Reply
iudithd5bf8e4d8d

December 24, 2024 at 10:53 pm

Hi Connor,
Just a partial guess …
Since “n” bytes store at most “n” characters, it is clear that your input values were blank-padded,
and, since we got back different values for LENGTH, this looks like column DATA is defined as a CHAR(n BYTE), as opposed to CHAR(n CHAR).

The question remains whether there exists some “weird” database character set in which ASCII characters could take up more than 1 byte …
and here I got stuck …

Hope that until Christmas (or maybe the New Year :)) steps in, we will get the solution, as a present 🙂

I wish you and your family a MERRY CHRISTMAS and a BRIGHT AND HAPPY NEW YEAR 2025, full of energy and accomplishments,
and wish myself to still have the opportunity to meet you soon 🙂

Cheers & Best Regards,
Iudith Mentzel

Reply
1. Connor McDonald
  
  December 24, 2024 at 11:06 pm
  
  Excellent suggestion but no issues with byte/char semantics in this case
  
  Reply
MaximDemenko

December 24, 2024 at 10:53 pm

Not sure, my comment was passed through wordpress – struggling with my account, to be sure – my suggestion is something like this
create table t(data blob CHECK (data IS JSON FORMAT OSON))

declare
x varchar2(40 byte) := ‘{key1:”val1″,key2:”val2″,key3:”val3″}’; –37 characters
begin
insert into t values (x);
end;
/

select length(data) from t –yields 63

declare
x varchar2(40 byte) := ‘{key1:[10,20],key2:[30,40],key3:[3,1,5]}’; –40 characters
begin
insert into t values (x);
end;
/

select length(data) from t –yields 89

so, there is a lot of room to play with json structure to get desired length…

Regards

Maxim

Reply
1. Connor McDonald
  
  December 24, 2024 at 11:10 pm
  
  Excellent hypothesis but does your approach allow you to construct the case where (comparatively) a shorter input yields a longer output?
  
  Reply
  1. Maxim
    
    December 24, 2024 at 11:14 pm
    
    Sure, i thought – it is visible from my comment – string with 37 characters input produces length of 63, input with 40 characters produces output with 89 length, the question is – what meaning in this case has function length(). Most likely – it is a length of internal representation (in bytes) – however, we can not easily justify it – dump can not be used with blob data type, so probably to prove it one have to dump a block…
    
    Regards
    
    Maxim
    
    Reply
Maxim

December 24, 2024 at 10:57 pm

I guess – like already suggested – implizit data conversion take place, a lot of possibilities to vary on length is using json

create table t(data blob CHECK (data IS JSON FORMAT OSON))

declare
x varchar2(40 byte) := ‘{key1:”val1″,key2:”val2″,key3:”val3″}’; –37 characters
begin
insert into t values (x);
end;
/

select length(data) from t –yields 63

declare
x varchar2(40 byte) := ‘{key1:[10,20],key2:[30,40],key3:[3,1,5]}’; –40 characters
begin
insert into t values (x);
end;
/

select length(data) from t –yields 89

Regards

Reply
pwl256

December 24, 2024 at 10:58 pm

is it utf8 data which uses variable byte lengths for characters

Reply
1. Connor McDonald
  
  December 24, 2024 at 11:05 pm
  
  Excellent hypothesis but not an issue in this case
  
  Reply
Raphaël

December 24, 2024 at 11:06 pm

Hi,

Here is my attempt – maybe x is null in the anonymous PLSQL block and there’s a smart default on DATA column:

SQL> create table T(data varchar2(100) default on null rpad(‘x’, to_number(to_char(sysdate,’SS’)),’x’));

Table created.

SQL> declare
2 x varchar2(40 byte):=”;
3 begin
4 insert into T values (x);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select length(data) from T;

LENGTH(DATA)

—————–

49

SQL>

Merry Christmas !

Raphaël

Reply
1. Connor McDonald
  
  December 24, 2024 at 11:11 pm
  
  Excellent suggestion but how do we then get the *variable* lengths as per my example, and moreover, a longer length for a shorter input
  
  Reply
  1. RaphaËl
    
    December 24, 2024 at 11:19 pm
    
    The inserted value length depends on the *moment* you do the insert (due to the way the default value is defined for DATA column). Each seconds, the value inserted gets 1 character longer.
    
    The variable definition (20 or 40 bytes) for x is pretty much irrelevant as its value is NULL (hence it benefits from the ON NULL default).
    
    Merry christmas!
    
    Raphaël
    
    Reply
tduin

December 24, 2024 at 11:08 pm

I guess the data colum is defined as a char(52):

SQL> create table t(data char(52));

Table created.

SQL>SQL> declare2 x varchar2(40 byte) := ‘0123456789012345678901234567890123456789’;3 begin4 insert into t values (x);5 end;6 /

PL/SQL procedure successfully completed.

SQL>SQL> select length(data) from t;

LENGTH(DATA)—————-52

SQL>SQL> delete from t;

1 row deleted.

SQL>SQL> declare2 x varchar2(20 byte) := ‘01234567890123456789’;3 begin4 insert into t values (x);5 end;6 /

PL/SQL procedure successfully completed.

SQL>SQL> select length(data) from t;

LENGTH(DATA)—————-52

Reply
1. Connor McDonald
  
  December 24, 2024 at 11:12 pm
  
  Excellent suggestion but you’ll always end up with 52. Notice in my example, we get variable lengths, and moreover, the length of shorter input resulted in a longer output
  
  Reply
Piet de Visser

December 25, 2024 at 12:18 am

Partial answer: I suspect the assign to x varchar2(n) := … is not a quoted constant, but some function that only gets evaluated on the actual insert, not on the initial assignment ?

Reply
1. Connor McDonald
  
  December 25, 2024 at 7:30 am
  
  it is a quoted constant, no reference to any other database objects other than the single table
  
  Reply
Abraham Olsen

December 25, 2024 at 7:42 am

I don’t know how you created the table. Or rather: how column “DATA” is declared – a varchar2(40 char)?? nchar? clob?

I do know that even if you declare the pl/sql as: x t.data%type then you can fill more bytes in the variable then in the table.
but you pl/sql uses 20 bytes and you wrote there aren’t any multibyte values in the string.

I do believe that length is affected by the input character set. I would like to see the output of other length functions:

The LENGTH functions return the length of char.

LENGTH calculates length using characters as defined by the input character set.

LENGTHB uses bytes instead of characters.

LENGTHC uses Unicode complete characters.

LENGTH2 uses UCS2 code points.

LENGTH4 uses UCS4 code points.

Reply
1. Connor McDonald
  
  December 25, 2024 at 8:58 pm
  
  You are on the right track but I didnt use any special characters
  
  Reply
Gary Myers

December 25, 2024 at 8:26 am

Your insert doesn’t specify data as the column name so I suspect it is an invisible column and the values is set via a default. That could be some wild SYS_CONTEXT thing or exotic function but don’t think you’ve gone that path

Reply
1. Connor McDonald
  
  December 25, 2024 at 8:59 pm
  
  Only one column in this table
  
  Reply
Sayan Malakshinov

December 25, 2024 at 11:20 am

Xmltype?

Reply
1. Connor McDonald
  
  December 25, 2024 at 8:59 pm
  
  You are on the right track, ie, datatypes that do manipulations on the data as its inserted before storing it
  
  Reply
Stelios Vlasopoulos

December 25, 2024 at 11:53 am

I came up with the following :

drop table t;
create table t(data JSON default on null ‘{“fruit”:”pomegranate”,”quantity”:”100″}’);

declare
x varchar2(40 byte) := ‘{“fruit”:”apple”,”quantity”:”10″}’;
begin
insert into t values (x);
end;
/

select length(data) from t; — returns 51

declare
x varchar2(20 byte) := null;
begin
insert into t values (x);
end;
/

select length(data) from t; — returns 58

Reply
1. Connor McDonald
  
  December 25, 2024 at 9:00 pm
  
  Nice work! It does not match the way I did it, but I must give you marks for meeting the specification
  
  Reply
Oj

December 25, 2024 at 4:40 pm

Maybe DATA a number column , and your varchar2 input is in scientific format where you could pass in a small number in a varchar2(40) and a big number in a varchar2(20), e.g 1.79E2 and 1.79E39….but i cant get beyond the value of 40 in length(data)…..

Reply
1. Connor McDonald
  
  December 25, 2024 at 9:01 pm
  
  You are on the right track but you need a complicated data type
  
  Reply
Philipp Salvisberg

December 25, 2024 at 6:41 pm
JSON. I have to fiddle a bit to get the right lengths.
- create table t (data json(object));
- 52 with ‘{ “greet”: “merry x-mas” , “to”: “all” }’;
- 58 with ‘{xmas:[1,2,3,4,5,6]}’;
Reply
1. Connor McDonald
  
  December 25, 2024 at 9:01 pm
  
  Nice work! It does not match the way I did it, but it meets the specification so kudos!
  
  Reply
zhwsh

December 25, 2024 at 9:00 pm

$ cat d1.txt
declare
x varchar2(&2 byte) := ‘&&1’;
begin
insert into t values (x);
end;
/

> create table t (data json);
Table created.

> @ d1.txt ‘{“fields”: {“field1”:{ “name”: “f1”},}}’ 40
PL/SQL procedure successfully completed.

> select length(data) from t;
LENGTH(DATA)
————
59

Reply
Din

December 25, 2024 at 9:17 pm

Is the DATA column storing the input numerical value spelled out?

an eight-digit number like 10,000,000 will be (stored as ‘ten million’) will return length of 11

but a smaller number say 990 will return (stored as ‘nine hundred ninety’) will return length of 19

Reply