Quick notes during a fiddle-session

For a while now I am working as a Solution Architect for the SAP Custom Development organisation.

This is the team you’d call when you want custom-built solutions that perfectly fit into your standard solution landscape.

My job there is not coding or implementing systems, but rather designing Solutions and turning those designs into commercial offers.

Obviously, it seems like a good idea to every now and then go and check that what I remember of doing “real work” is actually still true. and so I try to keep up with our tools and platforms as well as anyone.

While trying out different stuff for something that might become a blog post, later on, I came across some things I considered noteworthy, “interesting” or otherwise out of the expected.

All this is based on SAP HANA rev. 101 and SAP HANA Studio 2.1.6.

Change of default statement preparation in SAP HANA Studio

In Issue: MDX Query not executing in Hana Studio 2.0.8 Version and Re: Regarding “Generate Time Data” Option in HANA folks complained about the fact that MDX statements suddenly started to return error messages even though the worked in older SAP HANA Studio versions.

The error message was:

SAP DBTech JDBC: [2]: general error: MDX query doesn’t support prepared statement

I first proposed to circumvent this by creating a little wrapper procedure but this is inconvenient.

Shady Shen provided the correct hint: un-check the checkbox “Prepare statements before execution”.

This seems to have been set to active by default in the more recent versions of SAP HANA Studio. Once again a change that doesn’t occur in any documentation or whats-new note… 🙁

At least it’s not necessary to close/re-open the SQL editor to get this changed setting active.

No DEFAULT value specification in INSERT/UPDATE/UPSERT possible

Let’s say you were diligent with your table column specification and not only provided a name and data type but also a NOT NULL constraint and a DEFAULT value clause.

Something as extravagant as this:

create table test (id int not null default 0);

Now you want to make use of this and by following SQL standard you use the DEFAULT keyword to specify that you want to have the pre-defined default value:

insert into test (id) values (default);

SAP DBTech JDBC: [260]: invalid column name: DEFAULT: line 1 col 31 (at pos 30)

This is not to say SAP HANA doesn’t support default values.

If you want to use default values, you mustn’t include them in the DML statement at all.

So the following works without problems:

alter table test add (id2 int not null default 1);
insert into test (id) values (1);
select * from test;
1 1

Would be lovely to find such nuances in the documentation.

Limits of date operations

When working with date data types it’s real easy to forget about the limitations of this data type.

One example could be that non-existing dates like the February 30 or dates between 05.10.1582 and 14.10.1582 are rejected or mapped to the correct dates.

Another limitation I actually ran into while generating random garbage data for a date column is the smallest/largest date.

This time the documentation is clear about this: The range of the date value is between 0001-01-01 and 9999-12-31.

Usually I wouldn’t think much about this, but when using expressions like the following to create random dates, I hit an error:

select add_days (date'01.01.1980', rand()*10000000) from dummy;

[303]: invalid DATE, TIME or TIMESTAMP value: internal error in AddDays() at function add_days()

In my example, the 10000000 was, in fact, a sequence number in a rather large data set.

So, the quick fix would be to set an upper limit for the value that is fed into add_days().

select add_days (date'01.01.1980', least(rand()*10000000, 3560) from dummy;

The least() function comes in real handy here at it makes sure that the largest possible value returned will be 3560 (roughly 10 years worth of days).

Together with its sister the greatest() function, it’s easy to create upper and lower limits for generated values.

But, what if we don’t want to arbitrarily set the limit below what would be possible with this data type?

Easy too:

select days_between (date'31.12.9999', current_date) from dummy;



Gives you the number of days between today and the end of times – huh… gotta hurry!

Admittedly this example is super artificial and has no connection to real-life dates.

However, it shows that there are limitations to the date data type in SQL that it’s not too difficult to step into them.

There are of course other examples where a richer semantic for this data type would be useful.

Being able to have a MIN_DATE or MAX_DATE entry that would explicitly show up like this would be

Wandering off…

The date topic side-tracked me at this point and I looked into some other points, too:

I’ve shown the “generator query”-trick (cross join of objects system table to generate millions of rows) before.

This is of course not the only way to create a lot of rows.

Another option is to write SQL Script and this was the first time I used the new SPS 10 capability to run SQL script directly in the SQL console without the need for a wrapper procedure:

create table manydates (dday date);
    declare i bigint;
    for i in 0 .. 3652060 do
        insert into manydates (dday) values (add_days(date'01.01.0001', :i) );
        if (mod(i, 10000) = 0 )
        end if;
    end for;

This little gem runs for a while and fills a table with all possible date values.

Statement ‘do begin declare i bigint; for i in 0 .. 3652060 do insert into manydates (dday) values …’

successfully executed in 9:37.694 minutes  (server processing time: 9:37.681 minutes) – Rows Affected: 0

Now, about we claimed that no “no-dates” entries would be created.

And checking for duplicates via

select count(distinct dday) from manydates;


select dday, count(*) from manydates
group by dday having count(*) >1;

show that there aren’t any.

But how’s this?

We are looking at how many years worth of days here? 9999 exactly.

So, leaving out leap years we should end up with 365 * 9999 = 3.649.635 separate days.

That number is 2.426 days short of what we actually find in our table.

Alright, so then just taking 366 days per year will help us, maybe?

Nearly: 3.659.634 days result here, which is 7.573 days too many.

Maybe trying to account just for the leap years separately can help here.

Leap years occur roughly every four years, so for 9999/4 = 2.499 years, we should add one day.

That gets us to 3.652.134, which is just 73 days off from the actual number of days in this dates table.

We can even check this in SQL again:

select count(distinct year(dday))  from manydates
where mod(to_integer(year(dday)), 4) = 0;



But what leap years didn’t we cover with our simple arithmetic?

(select year(dday), count(dday)
    from manydates
    where  mod(to_integer(year(dday)), 4) = 0
    group by year(dday))
(select year(dday),  count(dday)
    from manydates
    group by year(dday) having count(dday) > 365
order by year(dday);

Fetched 63 row(s) in 21 ms 617 µs

1700 365
1800 365
1900 365
2100 365
2200 365
2300 365
2500 365
2600 365
2700 365
2900 365
3000 365
3100 365
3300 365
3400 365
3500 365
3700 365
3800 365
3900 365
4100 365
4200 365
4300 365
4500 365
4600 365
4700 365
4900 365
5000 365
5100 365
5300 365
5400 365
5500 365
5700 365
5800 365
5900 365
6100 365
6200 365
6300 365
6500 365
6600 365
6700 365
6900 365
7000 365
7100 365
7300 365
7400 365
7500 365
7700 365
7800 365
7900 365
8100 365
8200 365
8300 365
8500 365
8600 365
8700 365
8900 365
9000 365
9100 365
9300 365
9400 365
9500 365
9700 365
9800 365
9900 365

If you followed up to here, you’ll notice, that we were looking for 73 “missing days”.

Comparing the set of years where the number of the year is divisible by 4 without remainder and the set of years where the number of days is larger than 365 revealed 63 days.

So, where are the 10 last days here?

Remember that odd reference on the Gregorian Calendar reformation?

That took ten days away in 1582!

select count(distinct dday) from manydates
where year(dday) = 1582;



So far, so good.

We found all the “missing records”, but why aren’t these counted as leap years anyhow?

That’s because I used a too simple rule here.

Leap Year nearly every 4 years explains that a leap year is only counted as such if

  • The year is evenly divisible by 4;
  • If the year can be evenly divided by 100, it is NOT a leap year, unless;
  • The year is also evenly divisible by 400. Then it is a leap year.

Let’s run that against our dates table:

select year(dday) yr, count(dday) day_cnt
from manydates
       mod(to_integer(year(dday)), 4) = 0
       not (mod(to_integer(year(dday)), 100) = 0)
        mod(to_integer(year(dday)), 4) = 0
        mod(to_integer(year(dday)), 400) = 0
group by year(dday)

Fetched 2424 row(s) in 123 ms 147 µs (server processing time: 1 ms 665 µs)

Alright – the rule gives us 12 years less than what is in our dates table.

Which ones?

select mod_y.yr mod_yr, long_y.yr long_yr
  (select year(dday) yr, count(dday) day_cnt
  from manydates
        mod(to_integer(year(dday)), 4) = 0
        not (mod(to_integer(year(dday)), 100) = 0)
         mod(to_integer(year(dday)), 4) = 0
         mod(to_integer(year(dday)), 400) = 0
        group by year(dday)
  ) mod_y
  (select year(dday) yr,  count(dday) day_cnt
     from manydates
     group by year(dday) having count(dday) > 365
  ) long_y
  on mod_y.yr = long_y.yr
   mod_y.yr is null or long_y.yr is null;
? 100
? 500
? 900
? 1300
? 200
? 600
? 1000
? 1400
? 300
? 700
? 1100
? 1500

Can you spot the reason?

All of those records violate the rule of not being divisible by 100 except when they are divisible by 400.

Why had those years been generated with one day too many by our add_days() function?

The Gregorian Calendar Reformation clearly did change history but it didn’t change the past 🙂

Before this regulation got active, the leap years didn’t follow those rules (which called for the Reformation in the first place).

And as we see there are no false leap years after the year 1500.

That’s it for today.

Funny things come up when one spends some hours on a rainy Sunday afternoon fiddling with SAP HANA SQL.

There you go, now you know!

– Lars

Leave a Reply

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