Skip to Content
Author's profile photo Andrew Melkonyan

ASE16: Losing blank spaces through procedure nesting

We’ve discovered a phenomenon when char() variable gets truncated to NULL as it is passed from proc to proc.


This has not been the case in old version and it causes some of our code to break.


I do not recall any mention of this type of change to default behaviour around spaces in ASE 16.


Is this something you encountered too? Is there a config parameter to change this behaviour back to what has been common in ASE 15?

Easy repro:


ASE15:

1> create proc sp_space1 @c1 char(3) = null, @vc1 varchar(3) = null as select char_length(@c1) as c1, char_length(@vc1) as vc1

2> go


1> sp_space1 ” “, ” “

2> go


c1 vc1

——

3 2
(1 row affected)

(return status = 0)

1> create proc sp_space2 @c1 char(3) = null, @vc1 varchar(3) = null as exec sp_space1 @c1 = @c1 , @vc1 = @vc1

2> go

1> sp_space1 ” “, ” “

2> go


c1 vc1

— —

3 2

(1 row affected)

(return status = 0)

1> sp_space2 ” “, ” “

2> go


c1 vc1

— —

3 2
(1 row affected)

(return status = 0)

1> select @@version

2> go

——————————————————————————————————————————

Adaptive Server Enterprise/15.0.3/EBF 18467 ESD#4 ONE-OFF/P/Sun_svr4/OS 5.8/ase1503/2768/64-bit/FBO/Thu Nov 18 20:03:17 2010

ASE16:

1> create proc sp_space1 @c1 char(3) = null, @vc1 varchar(3) = null as select char_length(@c1) as c1, char_length(@vc1) as vc1

2> go

1> sp_space1 ” “, ” “

2> go


c1 vc1

———- ———–

3 2

(1 row affected)

(return status = 0)

1> create proc sp_space2 @c1 char(3) = null, @vc1 varchar(3) = null as exec sp_space1 @c1 = @c1 , @vc1 = @vc1

2> go

1> sp_space1 ” “, ” “

2> go

c1 vc1

— —

3 2
(1 row affected)

(return status = 0)

1> sp_space2 ” “, ” “

2> go


c1 vc1

— —

NULL 2

(1 row affected)

(return status = 0)

1> select @@version

2> go

———————————————————————————————————————————————–
Adaptive Server Enterprise/16.0 SP01 PL02/EBF 24486 SMP/P/Sun_svr4/OS 5.10/ase160bw/3722/64-bit/FBO/Fri Jun 5 07:50:19 2015



Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Am using this

      Adaptive Server Enterprise/16.0 SP02 PL04/EBF 26122 SMP             

      and I get

      sp_space2 " ", "  "

      go

      c1          vc1

      ----------- -----------

                 3           1

      Author's profile photo Andrew Melkonyan
      Andrew Melkonyan
      Blog Post Author

      on my Windows box with 16.0 SP02 PL02/EBF 25319 SMP/P/X64/Windows I get it right too.

      Thanks for testing.

      Author's profile photo Bret Halford
      Bret Halford

      Hi Andrew,

      I'm also getting expected results on

      Adaptive Server Enterprise/16.0 SP01 PL02/EBF 24490 SMP/P/x86_64/Enterprise Linux

      One thing that does come to mind is an odd behavior ASE has with procedure parameters - if you pass parameters by name and specify a parameter name that doesn't exist, and the real parameter has a default value, ASE won't complain about the unknown parameter name and will just plug in the default value for the real parameter.

      1> create procedure p1 @param1 char(20) = "default" as print @param1

      2> go

      1> exec p1 @param1 = "correct"

      2> go

      correct

      (return status = 0)

      1> exec p1 @pram1 = "correct"  -- note misspelled parameter name

      2> go

      default

      (return status = 0)

      Now, your parameter names look consistent to me, but I'm wondering if there might be some odd mismatch going on.  If we change the default values for @c1, we can figure out if the NULL is coming from the parameter default or if something else is going on.

      create proc sp_space1 @c1 char(3) = 'pr1', @vc1 varchar(3) = null as select char_length(@c1) as c1, char_length(@vc1) as vc

      go

      sp_space1 " ", " "

      go

      create proc sp_space2 @c1 char(3) = 'pr2', @vc1 varchar(3) = null as exec sp_space1 @c1 = @c1 , @vc1 = @vc1

      go

      sp_space1 " ", " "

      go

      sp_space2 " ", " "

      go

      -bret

      Author's profile photo Andrew Melkonyan
      Andrew Melkonyan
      Blog Post Author

      Thank's for testing Bret.  I will experiment on Monday when I'm back on site and report.  People noted also that the results may differ even on the same version depending on client connectivity.  Some get expected result - some do not.  I will test your suggestion and report back.

      Author's profile photo Andrew Melkonyan
      Andrew Melkonyan
      Blog Post Author

      Bret,

      Tested it again:  defaults are not picked up:

      1> drop proc sp_space1

      2> go

      1> create proc sp_space1 @c1 char(3) = "DEF", @vc1 varchar(3) = "DEF"  as select char_length(@c1) as c1l, char_length(@vc1) as vc1l

      2> go

      1> sp_space2 "  ", "  "

      2> go

      ,c1l        ,vc1l       ,

      ,-----------,-----------,

      ,       NULL,          2,

      (1 row affected)

      (return status = 0)

      1> drop proc sp_space2

      2> go

      1> create proc sp_space2 @c1 char(3) = "DEF2", @vc1 varchar(3) = "DEF2" as exec sp_space1 @c1 = @c1, @vc1 = @vc1

      2> go

      1> sp_space2 "  ", "  "

      2> go

      ,c1l        ,vc1l       ,

      ,-----------,-----------,

      ,       NULL,          2,

      (1 row affected)

      (return status = 0)

      I've tested it further and discovered that this issue is related to auditing:

      1> sp_audit "all", "sa", "all", "off"

      2> go

      Audit option has been changed and has taken effect immediately.

      (return status = 0)

      1> tempdb..sp_space2 " ", " "

      2> go

      ,c1l        ,vc1l       ,

      ,-----------,-----------,

      ,          3,          1,

      (1 row affected)

      (return status = 0)

      1> sp_audit "all", "sa", "all", "on"

      2> go

      Audit option has been changed and has taken effect immediately.

      (return status = 0)

      1> tempdb..sp_space2 " ", " "

      2> go

      ,c1l        ,vc1l       ,

      ,-----------,-----------,

      ,       NULL,          1,

      (1 row affected)

      (return status = 0)

      Just as another case I posted on here (on proc cache being eaten up from stored proc when object_access or all has been set for a login).

      There is definitely some issue with new auditing enhancements.

      Cheers,

      Andrew