Part of where condition ignored after upgrading to 72

[patrickbachmann]

Hi folks,

We have recently upgraded to 72 and in testing we have discovered that part of our SQL where condition is now ignored when using IS NULL in conjunction with OR operator.

For example;

If I have 10 records where ABC indicator = 'A' and 5 records where ABC indicator is null for a total of 15 records;

in 68 environment this below statement would return all 15 records:

select * from MSEG where BLDAT = '20140320' and (MAABC = 'A' or MAABC IS NULL)

in 72 this above statement is only returning the 5 null records.  Any other criteria within the parenthesis is completely ignored.

-Patrick

[patrickbachmann · Accepted solution]

Ok I'm shocked that nobody else has had this problem.  SAP has just confirmed this is a bug and it will be fixed in release 75.

-Patrick

[former_member182500 · Accepted solution]

I'm also shocked this made it to release.  How did you get the confirmation, via market place message or note? Think I'll stay on rev.70 until 75 released, thanks for the heads up.

[patrickbachmann · Accepted solution]

Via marketplace message. 

[former_member182500 · Accepted solution]

Showstopper?  For me certainly a reason to hold-off.

Incidentally is it just combination of OR and IS NULL, did SAP provide indication of issue with other predicates and expressions?

More visibility for those imminently intending to bin their AWS Developer rev.70 for recently offered rev.72?  I think so, @ Craig Cmehil

[patrickbachmann · Accepted solution]

Hi Jon-Paul,

Sorry somehow i missed the last couple of comments on this thread.  Actually it seems to be just an issue when used in conjunction with OR.  SAP really didn't provide any information except that a fix is now going to be in REV 74.01.  Originally they had said it would not be fixed until 75 but they just updated my message recently that said;

The release of this fix has been delayed.  SPS8 will be released

before the next revision in codeline SPS7, so this fix will be

included in Rev74.01 which is scheduled for release during the first

week of June.

-Patrick

[former_member182500 · Accepted solution]

Thanks for the update Patrick, appreciated.

[lbreddemann · Accepted solution]

Hi Patrick

really, you're shocked?

In my experience, most SQL statements are either very simple (simple flter/projection) or very complex (tons of joins, aggregations, nested filter conditions...).

Your statement is somewhat in between here.

Also, NULL is often not used in SAP databases (and luckily so), thus I cannot say that I am actually shocked about it.

Another aspect is, that in my view many end users wouldn't realize if some records are not returned any longer. Only when specific key values are so much off that it literally hits you on the head users start to wonder what's going on.

That's the same for all DBMS I've got to add here.

And that's the reason why (again, just my view) everybody should create stable test cases with fixed data to check the correctness of the data after revision upgrades, model changes etc.

For SAP HANA this specific bug is now in the regression test suite and will be checked for from now on.

- Lars

[former_member182500 · Accepted solution]

"And that's the reason why (again, just my view) everybody should create stable test cases with fixed data to check the correctness of the data"

Absolutely agree, to include SAP to catch these issues before they make it to release revision.

[lbreddemann · Accepted solution]

That's what we do - once we know about the bugs.

[former_member184768 · Accepted solution]

Lars Breddemann wrote:

Also, NULL is often not used in SAP databases (and luckily so), thus I cannot say that I am actually shocked about it.

- Lars

Hi Lars,

There could be NULL cases if the objects are used in UNION with some of the objects not mapped. This is quite a common scenario and very likely to happen.

Regards.

Ravi

[patrickbachmann · Accepted solution]

Lars, I say shocked because in my experience whenever I have a problem and search this forum I find that somebody else has experienced (and usually resolved) the same problem 2 or 3 months earlier.  It's like my work and learning is consistently a couple months behind the rest of my HANA pioneer pals and this time I found the issue first.  Definitely not shocked to find any bugs, that is not the shocking part. I can downgrade to 'surprised' if that makes you more happy.

-Patrick

[lbreddemann · Accepted solution]

Patrick - we *never* downgrade hahahah

Fair enough anyhow. Now you're also on the forefront of new bug finders... Otherwise I'd welcome you into the club, but I'm not too sure that you are actually happy about it...

Cheers, Lars

[lbreddemann · Accepted solution]

Hey Ravi

yep - you're right. NULLs easily can occur, most easily via outer joins.

I've no details on the bug, but I'd be surprised if the problem wasn't due some optimization on the attribute access level (where filter conditions on base tables are processed).

That's why I assume that the problem wouldn't occur with complex statement results.

- Lars

[AndyAnand]

Hi Patrick,

Just curious .. but do the following queries work for you ?

select * from MSEG where BLDAT = '20140320' and (MAABC = 'A' or MAABC != '')

or

select * from MSEG where (BLDAT = '20140320' and MAABC = 'A')  or (BLDAT = '20140320' and MAABC != '')

Regards,

Andy

[patrickbachmann]

Hi Andy,

I tried your suggestion but it does not return any of the NULL records.  Essentially the MAABC != '' will only return values in the field, not nulls.

-Patrick