freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
Re: [freetds] handling of compute rows in various tools
- From: michael.peppler AT bnpparibas.com
- To: freetds AT lists.ibiblio.org
- Subject: Re: [freetds] handling of compute rows in various tools
- Date: Mon, 22 Jan 2007 08:12:05 +0100
sqsh does show the aggregate column names when using Sybase's libs, so I
suspect that an API call in CTlib isn't quite right (probably
ct_describe(), or ct_compute_info())
uid usertype type
----------- -------- ----
1 4 37
1 80 37
sum
-----------
2
count
--------
2
uid usertype type
----------- -------- ----
1 2 39
1 18 39
1 25 39
sum
-----------
3
count
--------
3
uid usertype type
----------- -------- ----
1 1 47
1 24 47
sum
-----------
2
count
--------
2
avg
====
40
Michael
Internet
jklowden AT freetds.org@lists.ibiblio.org - 20.01.2007 03:46
Please respond to freetds AT lists.ibiblio.org
Sent by: freetds-bounces AT lists.ibiblio.org
To: freetds
cc:
Subject: [freetds] handling of compute rows in various tools
I tested sqsh, tsql, bsqldb, and fisql with this query:
select uid, usertype, type
from systypes
where type in (
select type
from systypes
group by type
having count(*) > 1
)
order by type, uid, usertype
compute sum(uid) by type
compute count(usertype) by type
compute avg(type)
None of them produced satisfactory results. sqsh probably comes closest;
I suspect ct-lib needs a little work in that department. Here are the
results:
sqsh:
[29] FreeTDS.1> select uid, usertype, type from systypes where type in
(select type from systypes group by type having count(*) > 1) order by
type, uid, usertype compute sum(uid) by type compute count(usertype) by
type compute avg(type)
[29] FreeTDS.2> go
uid usertype type
----------- -------- ----
1 4 37
1 80 37
-----------
2
--------
2
uid usertype type
----------- -------- ----
1 2 39
1 18 39
1 25 39
-----------
3
--------
3
uid usertype type
----------- -------- ----
1 1 47
1 24 47
-----------
2
--------
2
====
40
(14 rows affected)
Analysis: sqsh is missing the aggregating operator and the column it
operates on. It does line the compute columns under the right rows,
though, which helps.
tsql:
1> :r fancy_compute.sql
3> go
uid usertype type
1 4 37
1 80 37
2
2
1 2 39
1 18 39
1 25 39
3
3
1 1 47
1 24 47
2
2
40
Analysis: tsql is not a general purpose query tool. Good thing.
bsqldb:
]$ bsqldb -S $S -U $U -P $P -i fancy_compute.sql
uid usertype type
----------- -------- ----
1 4 37
1 80 37
sum(usertype)
-------------
2
count(type)
-----------
2
1 2 39
1 18 39
1 25 39
sum(usertype)
-------------
3
count(type)
-----------
3
1 1 47
1 24 47
sum(usertype)
-------------
2
count(type)
-----------
2
avg((null))
-----------
40
14 rows affected
Analysis: Most metadata of the lot. bsqldb is not meant for interactive
viewing, so the compute rows are intentionally left-justfified. Only the
last compute column name is missing; I suspect a db-lib bug.
fisql:
1>> :r fancy_compute.sql
Segmentation fault (core dumped)
$ printf "%s\ngo\n" "$(cat ../fancy_compute.sql)" | fisql -S $S -U $U -P
$P
Msg 5701, Level 10, State 2:
Server 'test', Changed database context to 'FreeTDS'.
uid usertype type
----------- -------- ----
1 4 37
1 80 37
1 80 37
1 80 37
1 2 39
1 18 39
1 25 39
1 25 39
1 25 39
1 1 47
1 24 47
1 24 47
1 24 47
1 24 47
(14 rows affected)
Analysis: When the compute rows arrive, the preceding data row is
reprinted.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
This message and any attachments (the "message") is
intended solely for the addressees and is confidential.
If you receive this message in error, please delete it and
immediately notify the sender. Any use not in accord with
its purpose, any dissemination or disclosure, either whole
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.
BNP PARIBAS (and its subsidiaries) shall (will) not
therefore be liable for the message if modified.
---------------------------------------------
Ce message et toutes les pieces jointes (ci-apres le
"message") sont etablis a l'intention exclusive de ses
destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir
immediatement l'expediteur. Toute utilisation de ce
message non conforme a sa destination, toute diffusion
ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce
message, dans l'hypothese ou il aurait ete modifie.
-
[freetds] handling of compute rows in various tools,
James K. Lowden, 01/19/2007
- Re: [freetds] handling of compute rows in various tools, michael . peppler, 01/22/2007
Archive powered by MHonArc 2.6.24.