Re: ctlib compute columns (was: "Fix affected rows after insert with prepared query" side effect)

> On May 12, 2016, at 11:30 PM, Frediano Ziglio <[email protected]> wrote:
> 
> It's always a good idea to open a new thread for a new topic!

yes

> 2016-05-11 21:47 GMT+01:00 John Kendall <[email protected]>:
>> 
>> On May 11, 2016, at 1:23 PM, Frediano Ziglio <[email protected]> wrote:
>> 
>>> 2016-05-11 6:07 GMT+01:00 John Kendall <[email protected]>:
>>>> 
>>>>> On May 10, 2016, at 3:34 PM, Frediano Ziglio <[email protected]> wrote:
>>>>> 
>>>>> 2016-05-10 22:31 GMT+01:00 John Kendall <[email protected]>:
>>>>>> It appears the patch for this ("fix ct-library rows count") has broken compute in ctlib:
>>>>>> 
>>>>>> 
>>>>>> select col=1 into #tbl insert #tbl values (2) select * from #tbl compute sum(col)
>>>>>> (1 row affected)
>>>>>> (1 row affected)
>>>>>> col
>>>>>> -----------
>>>>>>           1
>>>>>>           2
>>>>>>           2
>>>>>> 
>>>>>> (3 rows affected)
>>>>>> 
>>>>>> dblib (and ctlib RC1) does the expected:
>>>>>> 
>>>>>> select col=1 into #tbl insert #tbl values (2) select * from #tbl compute sum(col)
>>>>>> (1 row affected)
>>>>>> (1 row affected)
>>>>>> col
>>>>>> -----------
>>>>>>           1
>>>>>>           2
>>>>>> sum
>>>>>> ===========
>>>>>>           3
>>>>>> 
>>>>>> (3 rows affected)
>>>>>> 
>>>>>> 
>>>>>> Sorry to keep finding things!
>>>>>> 
>>>>>> John
>>>>>> 
>>>>>> 
>>>>> 
>>>>> Can you put some more detailed examples? Which program did you use?
>>>>> 
>>>>> Frediano
>>>> 
>>>> I'm using sqsh.
>>>> 
>>>> The compute clause should return an extra result line.  In the example I have given above, the table is a single column table with two rows with values 1 and 2.  Adding the compute sum clause to the select should return a compute result of 3, instead it is returns an extra copy of the last row.  My example is not a special case, no compute (sum, max, min, avg, etc) results seem to be returning from any queries with a compute clause.  I never saw this problem before RC4. Backing out the 'fix ct-library rows count' patch on ct.c makes it return the expected result.
>>>> 
>>>> John
>>>> 
>>> 
>>> 
>>> Just to be sure, can you test 1.0rc5 ?
>>> 
>> 
>> I already have.   And it looks good.  Thanks.
>> While I have your attention, and you are in that part of the code, I wonder if you might look into this:
>> 
>> http://lists.ibiblio.org/pipermail/freetds/2016q1/029725.html
>> 
>> I want to clarify what I'm asking you to look into.  That was a multi-topic thread.
>> The problem I am still interested in fixing is the ct_compute_info return values.
>> (The rest of that thread turned out to just be client differences.)
>> 
> 
> Patch looks good. Take a look at "compute operator" in include/freetds/tds.h.
> 
> Looks like for dblib constants are defined as protocol so no
> transformation is required however ctlib (and our headers!) contain
> different constants. Your patch is good however:
> - I would add a small function to do the conversion;
> - I would add support for additional mssql operators (I would collapse
> CNT_BIG into count and *U ones in no -U constants).

Aha! found them in include/freetds/proto.h.  Thanks.
Agree with your suggestions.  Not sure what to do with these, however:
 SYBAOPSTDEV
 SYBAOPSTDEVP
 SYBAOPVAR
 SYBAOPVARP
 SYBAOPCHECKSUM_AGG

I don't see an obvious mapping for them.

This works for me:

*** ct.c-orig	2016-05-11 12:49:42 -0700
--- ct.c	2016-05-13 01:46:33 -0700
***************
*** 57,62 ****
--- 57,63 ----
  int _ct_bind_data(CS_CONTEXT *ctx, TDSRESULTINFO * resinfo, TDSRESULTINFO *bindinfo, CS_INT offset);
  static void _ct_initialise_cmd(CS_COMMAND *cmd);
  static CS_RETCODE _ct_cancel_cleanup(CS_COMMAND * cmd);
+ static CS_INT _ct_map_compute_op(CS_INT comp_op);
  
  /* Added for CT_DIAG */
  /* Code changes starts here - CT_DIAG - 01 */
***************
*** 2655,2661 ****
  			int_val = 0;
  		} else {
  			curcol = resinfo->columns[colnum - 1];
! 			int_val = curcol->column_operator;
  		}
  		memcpy(buffer, &int_val, sizeof(CS_INT));
  		if (outlen)
--- 2656,2662 ----
  			int_val = 0;
  		} else {
  			curcol = resinfo->columns[colnum - 1];
! 			int_val = _ct_map_compute_op(curcol->column_operator);
  		}
  		memcpy(buffer, &int_val, sizeof(CS_INT));
  		if (outlen)
***************
*** 4630,4632 ****
--- 4631,4655 ----
  	return CS_SUCCEED;
  }
  
+ static CS_INT
+ _ct_map_compute_op(CS_INT comp_op)
+ {
+ 	switch (comp_op)
+ 	{
+ 		case SYBAOPCNT: 
+ 		case SYBAOPCNTU: 
+ 		case SYBAOPCNT_BIG:
+ 		        return CS_OP_COUNT; 
+ 		case SYBAOPSUM: 
+ 		case SYBAOPSUMU: 
+ 		        return CS_OP_SUM; 
+ 		case SYBAOPAVG: 
+ 		case SYBAOPAVGU: 
+ 		        return CS_OP_AVG; 
+ 		case SYBAOPMIN: 
+ 		        return CS_OP_MIN; 
+ 		case SYBAOPMAX: 
+ 		        return CS_OP_MAX; 
+         }
+ 	return comp_op;
+ }


John


> 
> Frediano
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds