[freetds] compute rows
John Kendall
john at capps.com
Sat Feb 27 05:16:37 EST 2016
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:
>>
>> http://lists.ibiblio.org/pipermail/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
More information about the FreeTDS
mailing list