Table user defined functions in 16 SP02

[Former Member]

I have created a function and would like to join to another table.  Following the approach shown in the reference manual I am getting a stack trace if I select more than one row from the other table.

Here is a simplified example using system tables so it should be easy to try out anywhere:

if object_id('GetColumns') is not NULL drop function dbo.GetColumns

go

CREATE function dbo.GetColumns(@ObjId int)

returns

    @cols table(ColumnName varchar(999))

as

begin

    insert @cols(ColumnName)

    select name from syscolumns where id = @ObjId

end

go

select * from  dbo.GetColumns(1)

select * from  dbo.GetColumns(NULL)

select o.id, o.name, c.ColumnName from  sysobjects o, dbo.GetColumns(o.id) c where o.id = 1

select o.id, o.name, c.ColumnName from  sysobjects o, dbo.GetColumns(o.id) c where o.id = 2

select o.id, o.name, c.ColumnName from  sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2

The first four selects work fine, but the last one fails spectacularly with a stack trace.

Is this a known problem or am I using it incorrectly?

Thanks

Mark

[Former Member]

Thanks for your help Mark.

I can confirm I am seeing the same problem on another dataserver at the same patch level and also when running through isql and sqsh.

We are upgrading from 15.7 to this level so we've missed out the earlier patch levels of 16. This is the first dataserver we've had which supports table user defined functions and I don't have access to an SP03 server.

Mark

[Mark_A_Parsons]

'fraid I've hit a dead wall ... tried a couple dozen different combinations of config changes (to match your settings) ... still unable to reproduce the problem.

[former_member188958]

I'm not having any luck reproducing this either.

In the spirit of "round up the usual suspects", could you try turning "literal autoparam" off and see if the problem keeps happening?

-bret

[Former Member]

Turning literal autoparam off made no difference.

Mark

[former_member89972]

Hi Bret

In my case literal autoparam was off

1> sp_helpconfig "enable literal autoparam"

2> go

The configuration parameter 'enable literal autoparam' enables/disables the auto-parameterization of literals in ad-hoc queries. The default value is 1 (enabled)

Minimum Value   Maximum Value   Default Value   Current Value   Memory Used   Unit   Type

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

               0               1               0               0           0   switch dynamic

(return status = 0)

1>

Query outcome seems to be OS specific.

As noted above for the last query I get a different error on AIX !

Msg 201, Level 16, State 2:

Server 'PHI5_SB_16', Procedure 'GetColumns':

Procedure GetColumns expects parameter @ObjId, which was not supplied.

I have copy-pasted the code Mark provided to run the queries.

My ASE @@version is :

Adaptive Server Enterprise/16.0 SP02 PL04/EBF 26120 SMP/P/RS6000/AIX 7.1/ase160sp02pl04x/2587/64-bit/FBO/Sat Jun 11 17:09:12 2016

Avinash

[Former Member]

Rewriting the query as a left outer join seems to fix the problem - I don't know why.

For example this works in all of the databases I've tried:

select      o.id, o.name, c.ColumnName

from        sysobjects o

left join   dbo.GetColumns(o.id) c on 1 = 1 /* deliberate cartesian join */

where       o.id = 1 or o.id = 2

Mark

[Former Member]

I have tried the test in some more databases and can add some more information, although it only seems to confuse matters even further!

I have found some user databases on the same server where the test did not cause a stack trace but returned rows as expected.  The databases have the same db_options set.

I then tried running the query from the bad database but using the sysobjects table from the good database:

use BadDb

go

select o.id, o.name, c.ColumnName from  GoodDb..sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2

and that works fine.

The other way around:

use GoodDb

go

select o.id, o.name, c.ColumnName from  BadDb..sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2

stack traces.

If I copy the sysobjects table into another table in the bad database and then use that in the query:

use BadDb

go

select * into MySysobjects from sysobjects

go

select o.id, o.name, c.ColumnName from  MySysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2

works fine.

SO it looks like there may be something wrong/different with the sysobjects tables on the bad databases. DBCC checktable returns no errors though.

Mark

[former_member188958]

If you compare showplan outputs from the same query in a database where it fails with a database where it works, is there any difference in the plan?

-bret

[Former Member]

I'm having problems getting the showplan output for the failing one, but it must be doing something different because if I put "set forceplan on" "set forceplan off" around it, it executes successfully.

Mark

[former_member188958]

The query returns results and doesn't stack for me on 16.0 SP02 PL04 Linux.

Could you post your exact @@version and a copy of the full  stacktrace error?

-bret

[Former Member]

Thanks Bret,

I have just noticed that it doesn't stack trace if I run the whole thing in tempdb - but I've tried it in three user databases and it stack traced in all of them.

The @@version is:

Adaptive Server Enterprise/16.0 SP02 PL04/EBF 26122 SMP/P/x86_64/Enterprise Linux/ase160sp02pl04x/2586/64-bit/FBO/Fri Jun 10 11:33:16 2016

Here's all of the stack trace I can get easily. Not sure if it's complete or not.

pc: 0x00000000015e41f9 conn_hdlr+0xe49()

pc: 0x0000000001c61f6f sequencer+0x8df()

pc: 0x00000000015d4c8f tdsrecv_language+0x1df()

end of stack trace, spid 3266, kpid 944181615, suid 6540

[Handler pc: 0x0x0000000001c5e6f0 s_handle installed by the following function:-]

[Handler pc: 0x0x0000000001c90060 hdl_stack installed by the following function:-]

[Handler pc: 0x0x0000000001d6eb60 ut_handle installed by the following function:-]

curdb = 37 tempdb = 83 pstat = 0x10000 p2stat = 0x40101000

lasterror = 0 preverror = 0 transtate = 1

p3stat = 0x800 p4stat = 0x0 p5stat = 0x8 p6stat = 0x11 p7stat = 0x10000

************************************

************************************

SQL causing error : select o.id, o.name, c.ColumnName from sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2

curcmd = 0 program = Aqua_Data_Studio

SQL Text: select o.id, o.name, c.ColumnName from sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2

Current process (0x3847116f) infected with signal 11 (SIGSEGV)

Address 0x0x0000000001645e24 (LeColumn::LeColumn(tree*)+0xa4), siginfo (code, address) = (1, 0x0x0000000000000004)

pc: 0x0000000001e1e2f5 GenLava+0x6c5()

pc: 0x00000000013328da kisignal+0x321()

pc: 0x00000000013011dc pcstkwalk+0x46e()

Thanks

Mark

[former_member188958]

Hi Mark,

I don't find any known issues with a stack including LeColumn::LeColumn(tree*), so this seems to be a new issue.

I'm on the exact same ESD, but still not seeing the behavior even in user databases.
So there may be a configuration difference in play. 
Could you post the output of

sp_configure display_nondefault_settings

[former_member89972]

Disregard my comments below if these were not relevant

On AIX I get following with your last query (SP02 PL04), no stack trace.

1> select o.id, o.name, c.ColumnName from  sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2

2> go

Msg 201, Level 16, State 2:

Server 'PHI5_SB_16', Procedure 'GetColumns':

Procedure GetColumns expects parameter @ObjId, which was not supplied.

HTH

Avinash

===============================================================================

For Aquadata Studio while registering a server 

have you chosen correct entry for ASE on General tab ?

It should read "Sybase ASE 11/12/15/16"

If the entry "Sybase Anywhere" was chosen, behavior may be unpredictable !

It may connect but some system tables/views in Sybase Anywhere may not match those in Sybase ASE.

HTH

Avinash

[Former Member]

Hi Bret,

This is the output of sp_configure display_nondefault_settings:

Parameter Name                   Default      Memory Used     Config Value      Run Value     Unit              Type   

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

SQL batch capture                0            0               1                 1             switch            dynamic

additional network memory        0            5120            5242880           5242880       bytes             dynamic

allocate max shared memory       0            0               1                 1             switch            dynamic

allow resource limits            0            #9304           1                 1             switch            static 

cis rpc handling                 0            0               1                 1             switch            dynamic

cpu grace time                   500          0               1500              1500          clock ticks       dynamic

current audit table              1            0               2                 2             id                dynamic

deadlock checking period         500          0               1200              1200          milliseconds      dynamic

deadlock pipe active             0            0               1                 1             switch            dynamic

deadlock pipe max messages       0            18221           1000              1000          number            dynamic

default network packet size      2048         #121630         10240             10240         bytes             static 

disable disk mirroring           1            0               0                 0             switch            static 

disk i/o structures              256          17818           60000             60000         number            dynamic

dump on conditions               0            0               1                 1             switch            dynamic

dynamic allocation on demand     1            0               0                 0             switch            dynamic

enable bulk inserts              0            0               1                 1             switch            dynamic

enable encrypted columns         0            96704           1                 1             switch            dynamic

enable functionality group       0            0               1                 1             switch            dynamic

enable housekeeper GC            1            0               4                 4             switch            dynamic

enable large pool for load       0            0               1                 1             switch            dynamic

enable literal autoparam         0            0               1                 1             switch            dynamic

enable logins during recovery    1            0               0                 0             switch            dynamic

enable monitoring                0            0               1                 1             switch            dynamic

enable rep agent threads         0            0               1                 1             switch            dynamic

enable select into in tran       0            0               1                 1             switch            dynamic

enable semantic partitioning     0            0               1                 1             switch            dynamic

enable spinlock monitoring       0            0               1                 1             switch            dynamic

enable utility lvl 0 scan wait   0            0               1                 1             switch            dynamic

enable xml                       0            0               1                 1             switch            dynamic

errorlog pipe active             0            0               1                 1             switch            dynamic

errorlog pipe max messages       0            9098            1000              1000          number            dynamic

event buffers per engine         100          #3754           2000              2000          number            static 

exception on rounding overflow   0            0               1                 1             switch            dynamic

global async prefetch limit      10           0               15                15            percent           dynamic

global cache partition number    1            0               64                64            number            static 

heap memory per user             4096         116967424       32768             32768         bytes             dynamic

histogram tuning factor          20           0               50                50            number            dynamic

housekeeper free write percent   1            0               90                90            percent           dynamic

i/o accounting flush interval    1000         0               10000             10000         clock ticks       dynamic

identity burning set factor      5000         0               10                10            number            static 

kernel resource memory           6396         263346          129607            129607        memory pages(2k)  dynamic

license information              0            0               50                50            number            dynamic

lock hashtable size              2048         1585            65536             65536         bytes             static 

max SQL text monitored           0            24174           8192              8192          bytes             static 

max async i/os per engine        1024         0               40000             40000         number            static 

max async i/os per server        2147483647   0               600000            600000        number            static 

max buffers per lava operator    2048         0               20480             20480         number            dynamic

max concurrently recovered db    0            0               14                14            number            dynamic

max memory                       172032       223232000       111616000         111616000     memory pages(2k)  dynamic

max network packet size          2048         0               65024             65024         bytes             static 

max online engines               1            66507           16                16            number            static 

max utility parallel degree      1            0               24                24            number            dynamic

number of alarms                 400          2044            20000             20000         number            dynamic

number of aux scan descriptors   256          #3802           2000              2000          number            dynamic

number of checkpoint tasks       1            0               4                 4             number            dynamic

number of devices                10           #258            200               200           number            dynamic

number of disk tasks             1            0               16                16            number            dynamic

number of engines at startup     1            0               16                16            number            static 

number of histogram steps        20           0               50                50            number            dynamic

number of large i/o buffers      6            525064          128               128           number            dynamic

number of locks                  10000        1409234         6000000           6000000       number            dynamic

number of network tasks          1            0               6                 6             number            dynamic

number of open databases         12           122606          200               200           number            dynamic

number of open indexes           500          164344          50000             50000         number            dynamic

number of open objects           500          179294          50000             50000         number            dynamic

number of open partitions        500          64073           40000             40000         number            dynamic

number of pre-allocated extents  2            0               31                31            number            dynamic

number of sort buffers           500          0               1000              1000          number            dynamic

number of user connections       25           4997152         3000              3000          number            dynamic

number of worker processes       0            159749          96                96            number            dynamic

object lockwait timing           0            0               1                 1             switch            dynamic

open index hash spinlock ratio   100          0               10                10            ratio             dynamic

open index spinlock ratio        100          0               10                10            ratio             dynamic

optimization goal                allrows_mix  0               allrows_oltp      allrows_oltp  name              dynamic

optimize dump for faster load    5            0               1                 1             switch            dynamic

optimizer level                  999999       0               999998            999998        number            dynamic

page lock promotion HWM          200          0               50000             50000         number            dynamic

page lock promotion LWM          200          0               10000             10000         number            dynamic

per object statistics active     0            0               1                 1             switch            dynamic

permission cache entries         64           #1187292        1200              1200          number            dynamic

print deadlock information       0            0               1                 1             number            dynamic

procedure cache size             14000        14305063        6144000           6144000       memory pages(2k)  dynamic

process wait events              0            0               1                 1             switch            dynamic

replication agent memory size    4096         40002           20000             20000         memory pages(2k)  dynamic

restricted parameter markers     0            0               1                 1             switch            dynamic

row lock promotion HWM           200          0               200000            200000        number            dynamic

row lock promotion LWM           200          0               40000             40000         number            dynamic

session tempdb log cache size    16384        0               65536             65536         bytes             static 

sql server clock tick length     100000       0               50000             50000         microseconds      static 

sql text pipe active             0            0               1                 1             switch            dynamic

sql text pipe max messages       0            9293            2000              2000          number            dynamic

stack guard size                 4096         18635           8192              8192          bytes             static 

stack size                       434176       1397662         614400            614400        bytes             static 

statement cache size             0            186263          80000             80000         memory pages(2k)  dynamic

statement pipe active            0            0               1                 1             switch            dynamic

statement pipe max messages      0            18122           10000             10000         number            dynamic

statement statistics active      0            0               1                 1             switch            dynamic

streamlined dynamic SQL          0            0               1                 1             switch            dynamic

user log cache spinlock ratio    20           0               10                10            ratio             dynamic

wait event timing                0            0               1                 1             switch            dynamic

100 record(s) selected

Mark

[Former Member]

Hi Avinesh,

Thanks for your help,

I can confirm my Aqua connection settings are correct.

That is an interesting error message you are getting and is one I can see sometimes with another function I wrote.  I wrote this simple one to try to demonstrate that error message, but it just stack traces instead for me!

Mark