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
Am using this
Adaptive Server Enterprise/16.0 SP02 PL04/EBF 26122 SMP
and I get
sp_space2 " ", " "
go
c1 vc1
----------- -----------
3 1
on my Windows box with 16.0 SP02 PL02/EBF 25319 SMP/P/X64/Windows I get it right too.
Thanks for testing.
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
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.
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