SQL function bug in DS4.2 sp8.1

dataservices administration
1

Hi All. I’m in the process of upgrading from DS4.2 sp6 to DS4.2 sp8.1. This statement used to work in sp6 and a value of ‘-1’ would be returned:-
$G_DEFAULT_INT = sql( ‘EDW_TARGET’,‘SELECT DEFAULT_INT FROM AUDIT.TECH_JOB_CONFIG’);
An undocumented feature in sp8.1 means that the statement no longer works so my variable is interpreted as being NULL. :reallymad:

Has anyone else seen this feature?
I’ve raised a call with support but this is a show stopper for us as we rely on default values to avoid NULLs in our data warehouse.

Nemesis :australia: (BOB member since 2004-06-09)

2

I am a bit confused as to what it is returning? Are you saying that the SQL returns -1 if you execute it in your DB tool but null if executed from DS? That would seem to be a fairly major bug if so.

  • E

eepjr24 :us: (BOB member since 2005-09-16)

3

Does the SQL statement actually work? (maybe the table you’re querying is actually empty?).

If you just run a select statement in a SQL function as part of a test script, does that work? (e.g. have it printed to trace log just to see evidence of the SQL function actually working correctly.)

I can’t imagine that a basic function like this would be broken but then again, I’m still on SP08 (no patch) and everything works fine here.

Also keep in mind that the SQL function always returns a varchar(1020) value, no matter the data type the database is returning.

If you’re global variable is an integer, you may want to add an explicit cast to integer rather than relying on an implicit cast? Perhaps that’s the behaviour change between SP6 and SP8.1 that causes you grief?

ErikR :new_zealand: (BOB member since 2007-01-10)

4

Thanks for the responses guys.

Yes, that’s exactly what I’m saying. Also, in DS 4.2sp6, the same statement runs with no issues.

Yes, the SQL statement returns ‘-1’ when I run it in management studio.

No, I’ve tried using the SQL function in a variety of test scripts and none of them work.

I’ve tried that as well and it doesn’t help.

Interestingly, if I use the SQL function with traces turned on, I can see that the correct value (-1) is retuned by the function call. But the variable doesn’t receive this value.

My support provider has been able to replicate the issue and he’s passing it on to SAP.

I’ll keep you updated :?

Nemesis :australia: (BOB member since 2004-06-09)

5

Very interesting. Which database and version are you using?

You mention that you tried the script in various places, did you try it in a function call as well? Not saying you should have to, but sometimes inside functions perform quite differently.

  • E

eepjr24 :us: (BOB member since 2005-09-16)

6

We’re using SQL server 2014 and also SQL server 2014 in 2012 compatibility mode. Our support organisation replicated the issue using SQL Server 2008.

I have tried the script in a function call and get the same incorrect results.

The support call is with SAP at the moment. They are trying to replicate the issue on DS4.2 sp8 even though we have repeatedly told them that we are on DS4.2 sp8.1 :expressionless:

Nemesis :australia: (BOB member since 2004-06-09)

7

You can tell SAP to not bother with SP08 - I’ve just tried it and there is no problem. So this must be Patch 1 specific, if this is actually a bug or changed behaviour.

I have tried to reproduce the same situation by using DS 4.2 SP08 in a Microsoft environment, using MS SQL Server 2014 Express and IPS 4.1 SP08.

I created a new EDW_TARGET datastore pointing to the SQL 2014 database in which I created an AUDIT schema, owned by dbo, with the TECH_JOB_CONFIG table, with only 1 column: DEFAULT_INT as Integer. In this I insert 1 record with value -1.

Now I’m assuming that AUDIT is the schema or the owner? I don’t think it matters anyway.

I run the following script (as the only object in the test job):


$G_DEFAULT_INT = sql( 'EDW_TARGET','SELECT DEFAULT_INT FROM AUDIT.TECH_JOB_CONFIG'); 

Print(' $G_DEFAULT_INT: ' || cast($G_DEFAULT_INT, 'varchar(10)'));

Do note that on validation it will still give a conversion warning because you are hard casting a VARCHAR output from SQL() into an Integer Global Variable. (You really need to change that using a proper test and conversion function with error handling.)


[Script:Test_SQL_Function] 
Warning: Expression <sql('EDW_TARGET', 'SELECT DEFAULT_INT FROM AUDIT.TECH_JOB_CONFIG')> of type <VARCHAR> will be converted to type <INT>. (BODI-1110432)

Anyway, that aside, the return is this:


12736	2812	PRINTFN	5/05/2017 11:04:59 a.m.	$G_DEFAULT_INT: -1

Have you tried this in your environment using a new job and a new script all together? I recently had this problem at a customer (still on DS 4.2 SP04 … yes I know, it’s out of mainstream support) where a Data Flow got corrupted so that it literally refused to evaluate A + B = C… because it kept thinking A was NULL even though that was not possible. When I recreated the logic in a new Data Flow, it worked perfectly fine so something obviously got porked along the way.

ErikR :new_zealand: (BOB member since 2007-01-10)

8

Thanks for taking the time to investigate Eric.

Weirdly, SAP have managed to replicate the same problem in sp6. We’ve had no problems with sp6.

I totally agree. I’ve inherited this code from an organisation called iTelligence, who really should know better. One of my background tasks during downtime is to go through their code and sort out rubbish like this.

I’ve come across the corrupt dataflow issue before as well. I did try creating a brand new job just to test this issue and was slightly disappointed that it still occurred.

Nemesis :australia: (BOB member since 2004-06-09)

9

I am using a modest amount of sql() functions on this project and have seen no issues with DS 4.2 SP08 against Oracle 11g.

Looking forward to see what SAP has to say, or if they’ll just kick the can down the road.

eganjp :us: (BOB member since 2007-09-12)

10

I’m seeing some strange Dataflow corruption. In this case, the Dataflow is simply empty! Now, it could be the developer’s fault, but it’s happened more than once.

eganjp :us: (BOB member since 2007-09-12)

11

SAP have admitted that this issue is a bug.

They are adamant that it is fixed in DS4.2 sp8.2.

They are wrong about the second statement.

Nemesis :australia: (BOB member since 2004-06-09)

12

Thanks for the update. So can we conclude that this bug appeared in DS 4.2 SP08 P1?

I may want to stay away from that patch level then :slight_smile:

ErikR :new_zealand: (BOB member since 2007-01-10)

13

I’m waiting for a definitive answer as to when bug was introduced.

However, there’s another reason to avoid SP1 if you have SQL server local repositories. One of the upgrade scripts drops and recreates the AL_INDEX table to increase some column widths. We only noticed this by accident.

SAP have changed the script from SP3 onwards (or so I am told) to use alter statements. Which is all very well, but there’s no way to repopulate AL_INDEX without manually re-importing the tables :reallymad:

I know it’s not a big issue, but it’s still annoying.

Nemesis :australia: (BOB member since 2004-06-09)

14

SAP have provided a fix for this issue and it is generally available from DS 4.2 sp8.3 and sp9.
I’ve tested on DS4.2 sp8.3 and that works.
I’m about to upgrade my sandbox machine to DS4.2 sp9.1 so I’ll confirm if the issue has definitely been fixed in that version.

Nemesis :australia: (BOB member since 2004-06-09)

15

Good to know. Have you found anything in the sp9 release notes that would make that release a gotta-have release? I’m looking for an excuse to upgrade before we go to production.

eganjp :us: (BOB member since 2007-09-12)

16

We’ve been able to justify the upgrade on the grounds of this bug fix and also sp9 supporting the Azure Data Warehouse and SQL Server 2016 on Azure VM. My current client is very keen to move their data onto cloud based platforms.

Nemesis :australia: (BOB member since 2004-06-09)

17

Thanks for the update!

eganjp :us: (BOB member since 2007-09-12)

18

What’s the word? Is it fixed in SP 9.1?

eganjp :us: (BOB member since 2007-09-12)

19

Funnily enough, just today(long, long story), I’ve managed to get sp9.1 working on my sandpit machine. I can confirm that the bug is not present in DS4.2 sp9.1.

Nemesis :australia: (BOB member since 2004-06-09)

20

Good deal. Thanks for the update! :+1:

eganjp :us: (BOB member since 2007-09-12)