Learning is not a spectator sport

Kris Kringle the Database – The Christmas MYSTERY

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”

  1. Matthias Rogel Avatar
    Matthias Rogel

    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 Avatar
      Connor McDonald

      No, there are not nasty override tricks here

      Reply
  2. Kaley Crum Avatar
    Kaley Crum

    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 Avatar
      Connor McDonald

      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
  3. MaximDemenko Avatar
    MaximDemenko

    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
  4. iudithd5bf8e4d8d Avatar
    iudithd5bf8e4d8d

    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 Avatar
      Connor McDonald

      Excellent suggestion but no issues with byte/char semantics in this case

      Reply
  5. MaximDemenko Avatar
    MaximDemenko

    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 Avatar
      Connor McDonald

      Excellent hypothesis but does your approach allow you to construct the case where (comparatively) a shorter input yields a longer output?

      Reply
      1. Maxim Avatar
        Maxim

        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
  6. Maxim Avatar
    Maxim

    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
  7. pwl256 Avatar
    pwl256

    is it utf8 data which uses variable byte lengths for characters

    Reply
    1. Connor McDonald Avatar
      Connor McDonald

      Excellent hypothesis but not an issue in this case

      Reply
  8. Raphaël Avatar
    Raphaël

    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 Avatar
      Connor McDonald

      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 Avatar
        RaphaËl

        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
  9. tduin Avatar
    tduin

    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 Avatar
      Connor McDonald

      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
  10. Piet de Visser Avatar
    Piet de Visser

    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 Avatar
      Connor McDonald

      it is a quoted constant, no reference to any other database objects other than the single table

      Reply
  11. Abraham Olsen Avatar
    Abraham Olsen

    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 Avatar
      Connor McDonald

      You are on the right track but I didnt use any special characters

      Reply
  12. Gary Myers Avatar
    Gary Myers

    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 Avatar
      Connor McDonald

      Only one column in this table

      Reply
  13. Sayan Malakshinov Avatar
    Sayan Malakshinov

    Xmltype?

    Reply
    1. Connor McDonald Avatar
      Connor McDonald

      You are on the right track, ie, datatypes that do manipulations on the data as its inserted before storing it

      Reply
  14. Stelios Vlasopoulos Avatar
    Stelios Vlasopoulos

    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 Avatar
      Connor McDonald

      Nice work! It does not match the way I did it, but I must give you marks for meeting the specification

      Reply
  15. Oj Avatar
    Oj

    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 Avatar
      Connor McDonald

      You are on the right track but you need a complicated data type

      Reply
  16. Philipp Salvisberg Avatar
    Philipp Salvisberg

    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 Avatar
      Connor McDonald

      Nice work! It does not match the way I did it, but it meets the specification so kudos!

      Reply
  17. zhwsh Avatar
    zhwsh

    $ 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
  18. Din Avatar
    Din

    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

Leave a reply to MaximDemenko Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trending