Skip to Content.
Sympa Menu

freetds - [freetds] handling of compute rows in various tools

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: TDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: [freetds] handling of compute rows in various tools
  • Date: Fri, 19 Jan 2007 21:46:45 -0500

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





Archive powered by MHonArc 2.6.24.

Top of Page