Skip to Content.
Sympa Menu

freetds - Re: [freetds] compute rows

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: John Kendall <john AT capps.com>
  • To: FreeTDS Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] compute rows
  • Date: Sat, 27 Feb 2016 10:16:37 +0000

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





Archive powered by MHonArc 2.6.24.

Top of Page