[freetds] handling of compute rows in various tools

michael.peppler at bnpparibas.com michael.peppler at bnpparibas.com
Mon Jan 22 02:12:05 EST 2007


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.




More information about the FreeTDS mailing list