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;
IDID2
11

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;

DAYS_BETWEEN(31.12.9999,CURRENT_DATE)

-2916233

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);
do
begin
    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 )
        then
            commit;
        end if;
   
    end for;
end

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;

and

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;

COUNT(DISTINCT YEAR(DDAY) )

2499

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))
     
EXCEPT
(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

YEAR(DDAY)COUNT(DDAY)
1700365
1800365
1900365
2100365
2200365
2300365
2500365
2600365
2700365
2900365
3000365
3100365
3300365
3400365
3500365
3700365
3800365
3900365
4100365
4200365
4300365
4500365
4600365
4700365
4900365
5000365
5100365
5300365
5400365
5500365
5700365
5800365
5900365
6100365
6200365
6300365
6500365
6600365
6700365
6900365
7000365
7100365
7300365
7400365
7500365
7700365
7800365
7900365
8100365
8200365
8300365
8500365
8600365
8700365
8900365
9000365
9100365
9300365
9400365
9500365
9700365
9800365
9900365

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;

COUNT(DISTINCT DDAY)

355

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
where
       mod(to_integer(year(dday)), 4) = 0
       and
       not (mod(to_integer(year(dday)), 100) = 0)
      or
        mod(to_integer(year(dday)), 4) = 0
        and
        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
from
  (select year(dday) yr, count(dday) day_cnt
  from manydates
  where
        mod(to_integer(year(dday)), 4) = 0
        and
        not (mod(to_integer(year(dday)), 100) = 0)
       or
         mod(to_integer(year(dday)), 4) = 0
         and
         mod(to_integer(year(dday)), 400) = 0
        group by year(dday)
  ) mod_y
     
FULL OUTER JOIN
  (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
WHERE
   mod_y.yr is null or long_y.yr is null;
MOD_YRLONG_YR
?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.