FreeTDS Development Group

[Richard Rowell <richard AT bowmansystems.com>]

I create a table and insert rows into it.  The first time I do a select
the select returns nothing, not even the "0 rows affected" (even though
it should return two rows).  The exact same select repeated works the
second time and every time thereafter.  Snip for SQSH:

1> create table ttest ( name varchar(50))
2> insert into ttest values ( 'rick');
3> insert into ttest values ( 'dave');
4> go
1> select * from ttest where name = 'rick'
2> go
1> select * from ttest where name = 'rick'
2> go
 name                                              
 --------------------------------------------------
 rick                                              

(1 row affected)

I have played with this for the last two hours isolating it.  Here are
some observations I have made:
1.  If you drop the where clause, it works as expected (the first time.)
2.  If you drop the second go it works as expected.  
3.  If you only do one insert instead of two it works as expected.  
4.  Behaves the same with regular or temporary tables.
5.  The exact same SQL works as expected when using Query Analyzer from
Windows 2k.  

I'm attaching the pertinent portion of the log file also.

Environment:
sqsh 2.1-5 (but it does the same thing under PHP4.2.3 which is where we
originally noticed it)
freetds/libct : 0.61-2 (Debian unstable)
Debian Testing/Unstable on i386
SQL Server 2000 Service Pack 2
2003-04-08 14:12:10.102080 inside ct_cmd_alloc()
2003-04-08 14:12:10.102146 inside ct_command()
2003-04-08 14:12:10.102161 inside ct_cmd_props() action = CS_GET property = 51
2003-04-08 14:12:10.102225 inside ct_send()
Sending packet @ 2003-04-08 14:12:10.102246
0000  01 01 00 77 00 00 00 00  63 72 65 61 74 65 20 74   |...w.... create t|
0010  61 62 6c 65 20 74 74 65  73 74 20 28 20 6e 61 6d   |able tte st ( nam|
0020  65 20 76 61 72 63 68 61  72 28 35 30 29 29 0a 69   |e varcha r(50)).i|
0030  6e 73 65 72 74 20 69 6e  74 6f 20 74 74 65 73 74   |nsert in to ttest|
0040  20 76 61 6c 75 65 73 20  28 20 27 72 69 63 6b 27   | values  ( 'rick'|
0050  29 3b 0a 69 6e 73 65 72  74 20 69 6e 74 6f 20 74   |);.inser t into t|
0060  74 65 73 74 20 76 61 6c  75 65 73 20 28 20 27 64   |test val ues ( 'd|
0070  61 76 65 27 29 3b 0a                               |ave');.|


2003-04-08 14:12:10.102369 ct_send() succeeded
2003-04-08 14:12:10.102418 inside ct_results()
Received header @ 2003-04-08 14:12:10.108220
0000  04 01 00 23 00 34 01 00                            |...#.4..|


Received packet @ 2003-04-08 14:12:10.108248
0000  fd 01 00 c6 00 00 00 00  00 fd 11 00 c3 00 01 00   |........ ........|
0010  00 00 fd 10 00 c3 00 01  00 00 00                  |........ ...|


2003-04-08 14:12:10.108274 processing result tokens.  marker is  fd(DONE)
2003-04-08 14:12:10.108288 inside tds_process_end() more_results = 1, 
was_cancelled = 0 
2003-04-08 14:12:10.108302 inside ct_results() process_result_tokens returned 
1 (type 4047) 
2003-04-08 14:12:10.108316 inside ct_results()
2003-04-08 14:12:10.108326 processing result tokens.  marker is  fd(DONE)
2003-04-08 14:12:10.108337 inside tds_process_end() more_results = 1, 
was_cancelled = 0 
2003-04-08 14:12:10.108350 inside ct_results() process_result_tokens returned 
1 (type 4046) 
2003-04-08 14:12:10.108364 inside ct_results()
2003-04-08 14:12:10.108373 processing result tokens.  marker is  fd(DONE)
2003-04-08 14:12:10.108385 inside tds_process_end() more_results = 0, 
was_cancelled = 0 
2003-04-08 14:12:10.108398 inside ct_results() process_result_tokens returned 
1 (type 4046) 
2003-04-08 14:12:10.108411 inside ct_results()
2003-04-08 14:12:10.108420 inside tds_process_result_tokens() state is 
COMPLETED
2003-04-08 14:12:10.108432 inside ct_results() process_result_tokens returned 
2 (type 4046) 
2003-04-08 14:12:10.108450 inside ct_cmd_drop()
2003-04-08 14:12:10.116477 inside ct_cmd_alloc()
2003-04-08 14:12:10.116540 inside ct_command()
2003-04-08 14:12:10.116555 inside ct_cmd_props() action = CS_GET property = 51
2003-04-08 14:12:10.116574 inside ct_send()
Sending packet @ 2003-04-08 14:12:10.116592
0000  01 01 00 30 00 00 00 00  73 65 6c 65 63 74 20 2a   |...0.... select *|
0010  20 66 72 6f 6d 20 74 74  65 73 74 20 77 68 65 72   | from tt est wher|
0020  65 20 6e 61 6d 65 20 3d  20 27 72 69 63 6b 27 0a   |e name =  'rick'.|


2003-04-08 14:12:10.116682 ct_send() succeeded
2003-04-08 14:12:10.116725 inside ct_results()
Received header @ 2003-04-08 14:12:10.123413
0000  04 01 00 2d 00 34 01 00                            |...-.4..|


Received packet @ 2003-04-08 14:12:10.123452
0000  79 00 00 00 00 a0 05 00  04 6e 61 6d 65 a1 06 00   |y....... .name...|
0010  02 00 08 00 27 32 d1 04  72 69 63 6b fd 10 00 c1   |....'2.. rick....|
0020  00 01 00 00 00                                     |.....|


2003-04-08 14:12:10.123485 processing result tokens.  marker is  
79(RETURNSTATUS)
2003-04-08 14:12:10.123499 inside ct_results() process_result_tokens returned 
1 (type 4043) 
2003-04-08 14:12:10.123520 generating return status row. type = 56(int), 
varint_size 0
2003-04-08 14:12:10.123544 inside ct_fetch()
2003-04-08 14:12:10.123555 inside _ct_bind_data()
2003-04-08 14:12:10.123568 inside _ct_get_server_type(0)
2003-04-08 14:12:10.123583 inside ct_get_data()
2003-04-08 14:12:10.123598 inside ct_cancel()
Sending packet @ 2003-04-08 14:12:10.123611
0000  06 01 00 08 00 00 00 00                            |........|


2003-04-08 14:12:10.123651 inside tds_process_default_tokens() marker is 
a0(COLNAME)
2003-04-08 14:12:10.123691 inside tds_process_default_tokens() marker is 
a1(COLFMT)
2003-04-08 14:12:10.123705 processing result. type = 39(varchar), varint_size 
1
2003-04-08 14:12:10.123745 inside tds_process_default_tokens() marker is 
d1(ROW)
2003-04-08 14:12:10.123759 processing row.  column is 0 varint size = 1
2003-04-08 14:12:10.123772 processing row.  column size is 4 
2003-04-08 14:12:10.123783 clearing column 0 NULL bit
2003-04-08 14:12:10.123794 inside tds_process_end() more_results = 0, 
was_cancelled = 0 
Received header @ 2003-04-08 14:12:10.124110
0000  04 01 00 11 00 34 01 00                            |.....4..|


Received packet @ 2003-04-08 14:12:10.124132
0000  fd 20 00 fd 00 00 00 00  00                        |. ...... .|


2003-04-08 14:12:10.124148 inside tds_process_end() more_results = 0, 
was_cancelled = 1 
2003-04-08 14:12:10.124166 inside ct_cmd_drop()
2003-04-08 14:12:14.711799 inside ct_cmd_alloc()
2003-04-08 14:12:14.711872 inside ct_command()
2003-04-08 14:12:14.711887 inside ct_cmd_props() action = CS_GET property = 51
2003-04-08 14:12:14.711908 inside ct_send()
Sending packet @ 2003-04-08 14:12:14.711927
0000  01 01 00 30 00 00 00 00  73 65 6c 65 63 74 20 2a   |...0.... select *|
0010  20 66 72 6f 6d 20 74 74  65 73 74 20 77 68 65 72   | from tt est wher|
0020  65 20 6e 61 6d 65 20 3d  20 27 72 69 63 6b 27 0a   |e name =  'rick'.|


2003-04-08 14:12:14.712014 ct_send() succeeded
2003-04-08 14:12:14.712060 inside ct_results()
Received header @ 2003-04-08 14:12:14.712900
0000  04 01 00 28 00 34 01 00                            |...(.4..|


Received packet @ 2003-04-08 14:12:14.712924
0000  a0 05 00 04 6e 61 6d 65  a1 06 00 02 00 08 00 27   |....name .......'|
0010  32 d1 04 72 69 63 6b fd  10 00 c1 00 01 00 00 00   |2..rick. ........|


2003-04-08 14:12:14.712953 processing result tokens.  marker is  a0(COLNAME)
2003-04-08 14:12:14.712971 processing result tokens.  marker is  a1(COLFMT)
2003-04-08 14:12:14.712986 processing result. type = 39(varchar), varint_size 
1
2003-04-08 14:12:14.712999 inside ct_results() process_result_tokens returned 
1 (type 4049) 
2003-04-08 14:12:14.713013 processing result tokens.  marker is  d1(ROW)
2003-04-08 14:12:14.713033 inside ct_results() process_result_tokens returned 
1 (type 4040) 
2003-04-08 14:12:14.713049 inside ct_res_info()
2003-04-08 14:12:14.713060 ct_res_info(): Number of columns is 1
2003-04-08 14:12:14.713106 inside ct_describe()
2003-04-08 14:12:14.713119 inside _ct_get_client_type(type 39, size 50)
2003-04-08 14:12:14.713131 inside ct_describe() datafmt->datatype = 1 server 
type 39
2003-04-08 14:12:14.713147 inside ct_bind()
2003-04-08 14:12:14.713158 inside ct_bind() item = 1 datafmt->datatype = 1
2003-04-08 14:12:14.715402 inside ct_fetch()
2003-04-08 14:12:14.715477 processing row tokens.  marker is  d1(ROW)
2003-04-08 14:12:14.715494 processing row.  column is 0 varint size = 1
2003-04-08 14:12:14.715508 processing row.  column size is 4 
2003-04-08 14:12:14.715520 clearing column 0 NULL bit
2003-04-08 14:12:14.715532 inside ct_fetch() process_row_tokens returned 1
2003-04-08 14:12:14.715544 inside _ct_bind_data()
2003-04-08 14:12:14.715557 inside _ct_get_server_type(1)
2003-04-08 14:12:14.715569 inside _ct_get_client_type(type 39, size 50)
2003-04-08 14:12:14.715581 inside _ct_bind_data() setting source length for 0 
= 4 destlen = 51
2003-04-08 14:12:14.715597 inside cs_convert()
2003-04-08 14:12:14.715607 inside _ct_get_server_type(1)
2003-04-08 14:12:14.715618 inside _ct_get_server_type(1)
2003-04-08 14:12:14.715629 inside cs_convert() srctype = 47 (4) desttype = 47 
(51)
2003-04-08 14:12:14.715642 inside cs_convert() srctype = desttype
2003-04-08 14:12:14.715653 inside cs_convert() desttype = character
2003-04-08 14:12:14.716672 inside ct_fetch()
2003-04-08 14:12:14.716716 inside ct_results()
2003-04-08 14:12:14.716728 processing result tokens.  marker is  fd(DONE)
2003-04-08 14:12:14.716742 inside tds_process_end() more_results = 0, 
was_cancelled = 0 
2003-04-08 14:12:14.716757 inside ct_results() process_result_tokens returned 
1 (type 4046) 
2003-04-08 14:12:14.716772 inside ct_res_info()
2003-04-08 14:12:14.716782 ct_res_info(): Number of rows is 1
2003-04-08 14:12:14.716793 inside ct_results()
2003-04-08 14:12:14.716844 inside tds_process_result_tokens() state is 
COMPLETED
2003-04-08 14:12:14.716857 inside ct_results() process_result_tokens returned 
2 (type 4046) 
2003-04-08 14:12:14.718852 inside ct_cmd_drop()