FreeTDS Development Group

[John Kendall <john AT capps.com>]

Greetings.

I've looked into this and have found one problem and I have a patch.  

It appears freetds ct_compute_info is not returning the same aggregate 
operator type as the sybase client lib.  According to 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32840.1550/html/ctref/CIHHCJCJ.htm
 

ct_compute_info should return one of the following symbolic values when 
passed the CS_COMP_OP type:

CS_OP_SUM, CS_OP_AVG, CS_OP_COUNT,  CS_OP_MIN or CS_OP_MAX.

According to cspublic.h, these symbolic values are:

#define CS_OP_SUM       5370
#define CS_OP_AVG       5371
#define CS_OP_COUNT     5372
#define CS_OP_MIN       5373
#define CS_OP_MAX       5374

However, freetds ct_compute_info is consistently returning the following:

for SUM:  77 
for AVG:   79 
for COUNT: 75
for MIN:   81
for MAX:   82

By consistently, i mean I've tested it on Sybase 11.02, ASE 16 and MS SQL 
2012 with Linux and SPARC clients.


Looking at dumps, it appears that all 3 servers are returning the lower 
numbers (77, 79, etc) and freetds ct_compute_info returns the value 
unmodified.  Clearly that is not what the sybase client is doing.  It must be 
doing some mapping of the wire numbers to the symbolic values.  

I've patched my copy of ct.c like so:

*** 2625,2636 ****
--- 2625,2644 ----
        case CS_COMP_OP:
                if (!resinfo) {
                        int_val = 0;
                } else {
                        curcol = resinfo->columns[colnum - 1];
                        int_val = curcol->column_operator;
+                       switch (int_val)
+                         {
+                         case 77: int_val= CS_OP_SUM; break;
+                         case 79: int_val= CS_OP_AVG; break;
+                         case 75: int_val= CS_OP_COUNT; break;
+                         case 81: int_val= CS_OP_MIN; break;
+                         case 82: int_val= CS_OP_MAX; break;
+                         }
                }
                memcpy(buffer, &int_val, sizeof(CS_INT));
                if (outlen)
                        *outlen = sizeof(CS_INT);
                break;
        default:


Could this, or somethings like it, be applied to the master?


John





Date: Mon, 08 Feb 2016 08:53:14 +0000
From: matthew.green AT datamartcomputing.com
To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
Subject: Re: [freetds] compute rows
Message-ID: <5cff0c0a6e0b62c2c9f53db48d572c66 AT datamartcomputing.com>

In case it helps:

Sybase 15.7 client with sqsh produces "predictable" results, with the correct 
headings. 

On the other hand isql itself produced results more like fisql:

1> select * from tempdb..tab compute sum(col1), avg(col1), min(col1), 
count(col1), avg(col2), max(col3), min(col3)
2> go
col1        col2        col3
----------- ----------- -----------
          1           1           1
          2           2           2
          3           3           3


Compute Result:
----------- ----------- ----------- ----------- ----------- ----------- 
-----------
          6           2           1           3           2           3       
     1

Sybase on Windows (isql GUI) gives:

col1        col2        col3        
----------- ----------- ----------- 
1           1           1           
2           2           2           
3           3           3           
(3 rows)

Sum(col1)   Avg(col1)   Min(col1)   Count(col1) Avg(col2)   Max(col3)   
Min(col3)   
----------- ----------- ----------- ----------- ----------- ----------- 
----------- 
6           2           1           3           2           3           1     
      

but isql from a DOS box gives the same output as Solaris.

All a bit of a mess, even when using the vendor version!

I tried to track the TDS stream with Ribo and I couldn't see any obvious 
difference in communication flow in regard to the results returned in each 
case. I'd therefore guess that the presentation must be a client side 
decision.

Cheers,

Matthew.





February 7 2016 1:50 PM, "Frediano Ziglio" <freddy77 AT gmail.com> wrote:
> 2016-02-06 9:33 GMT+00:00 John Kendall <john AT capps.com>:
> 
>> I am having trouble with the output of compute rows. I found the following 
>> exchange from 2007
>> between jkl and Michael Peppler that summarizes what I'm experiencing in 
>> 2016:
>> 
>> https://lists.ibiblio.org/sympa/arc/freetds/2007q1/020954.html
>> 
>> To summarize, sqsh (and other tools linked to freetds) do not produce 
>> usable output for compute
>> rows. Michael Peppler says that sqsh acts correctly when linked to sybase 
>> libs. I have an old
> copy
>> of isql that handles compute statements in a very predictable way, for 
>> example:
>> 
>> select col1=1, col2=1, col3=1 into tempdb..tab
>> insert tempdb..tab select 2,2,2
>> insert tempdb..tab select 3,3,3
>> go
>> select * from tempdb..tab compute sum(col1), avg(col1), min(col1), 
>> count(col1), avg(col2),
>> max(col3), min(col3)
>> go
>> col1 col2 col3
>> ----------- ----------- -----------
>> 1 1 1
>> 2 2 2
>> 3 3 3
>> sum
>> ===========
>> 6
>> avg avg
>> =========== ===========
>> 2 2
>> min min
>> =========== ===========
>> 1 1
>> count
>> ===========
>> 3
>> max
>> ===========
>> 3
>> 
>> (4 rows affected)
>> 
>> sqsh, linked to freetds, produces:
>> 
>> col1 col2 col3
>> ----------- ----------- -----------
>> 1 1 1
>> 2 2 2
>> 3 3 3
>> 
>> ===========
>> 6
>> 
>> ===========
>> 2
>> 
>> ===========
>> 1
>> 
>> =========== =========== ===========
>> 3 2 3
>> 
>> ===========
>> 1
>> 
>> (4 rows affected)
>> 
>> Note how sqsh doesn't label the aggregate columns, and it combined count, 
>> avg and max into one
> row,
>> while not combining the two min rows. Pretty useless.
>> 
>> I no longer have access to sybase libs, so I cannot test sqsh linked to 
>> them.
>> Just for comparison, fisql has a different set of problems:
>> 
>> col1 col2 col3
>> ----------- ----------- -----------
>> 1 1 1
>> 2 2 2
>> 3 3 3
>> sum avg min count avg max min
>> ----------- ----------- ----------- ----------- ----------- ----------- 
>> -----------
>> 6 2 1 3 2 3 1
>> 
>> (4 rows affected)
>> 
>> Micheal's theory was that an API call in CTlib wasn't quite right, such as 
>> ct_describe() or
>> ct_compute_info().
>> Any ideas anyone?
>> Thanks,
>> John
> 
> Weird request. MS is trying to remove (if not already) support for COMPUTE.
> Probably the better way is to looks at the dumps (TDSDUMPs), and see
> what functions
> return. Then looks at documentation and callers (fisql, sqsh) and try
> to understand what's
> wrong.
> 
> Frediano
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds