MySQL Bugs: #17867: Specified Cast is not valid while using aggregate function

Bug #17867	Specified Cast is not valid while using aggregate function
Submitted:	2 Mar 2006 16:59	Modified:	6 May 2006 11:38
Reporter:	Gurjit Singh Batra	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	?	OS:	Windows (WIN XP)
Assigned to:		CPU Architecture:	Any

Description:
Specified Cast is not valid while using aggregate function:
===========================================
The Select statement used in C#.NET like as :

select v_dt, sum(sqty) as rect_qty, sum(bqty) as issue_qty from trtrade2
group by v_dt

The above statement works in mysql ver 5.0.1-alpha-nt but not in the latest version 5.0.17 and 5.0.18. 

The aggregate function variables shows as  Byte[] Array while getting the values from the above select statement. I have worked a lot to check. First Checking, MySql connector with various versions and then changing CLR in .NET etc. Finally, the above statement works in 5.0.1-alpha-nt but not in other versions.

Kindly update the bug and inform me immediate about this. This bug have already been reported in Forum Section Page No.8 but have not been removed yet in latest versions of mysql

How to repeat:
As Above

Suggested fix:
Kindly update in the latest version of mysql 5.0.17 and 5.0.18

Thank you for a bug report. Please, send the results of

SHOW CREATE TABLE trtrade2;

"Table","Create Table"
"trtrade2","CREATE TABLE `trtrade2` (
  `det_id` bigint(11) NOT NULL default '0',
  `v_dt` date NOT NULL default '0000-00-00',
  `sr_no` int(11) NOT NULL default '0',
  `doc_id` int(11) NOT NULL default '0',
  `doc_code` varchar(4) NOT NULL default '',
  `v_type` varchar(4) NOT NULL default '',
  `br_id` int(11) NOT NULL default '0',
  `item_code` varchar(10) NOT NULL default '',
  `dt_type` char(3) default NULL,
  `brok_type` char(2) default NULL,
  `set_type` char(2) default NULL,
  `stat` varchar(5) NOT NULL default '',
  `set_no` int(11) default '0',
  `delv_yn` char(1) NOT NULL default 'N',
  `sqty` int(11) NOT NULL default '0',
  `bqty` int(11) NOT NULL default '0',
  `cust_code` varchar(10) NOT NULL default '',
  `cust_desc` varchar(40) default '',
  `term_no` varchar(15) default '',
  `trade_id` varchar(13) default NULL,
  `trade_dt` date default NULL,
  `trade_time` varchar(8) default NULL,
  `trader_id` varchar(10) default NULL,
  `trading_unit` double default '0',
  `rate_per` double default '0',
  `multiplier` double default '0',
  `order_no` varchar(25) default NULL,
  `order_dt` date default NULL,
  `order_time` varchar(8) default NULL,
  `status_clr` char(1) NOT NULL default 'N',
  `sbilled_qty` int(11) NOT NULL default '0',
  `bbilled_qty` int(11) NOT NULL default '0',
  `billed_qty` int(11) NOT NULL default '0',
  `sio_qty` int(11) NOT NULL default '0',
  `bio_qty` int(11) NOT NULL default '0',
  `io_qty` int(11) NOT NULL default '0',
  `spl_qty` int(11) NOT NULL default '0',
  `bpl_qty` int(11) NOT NULL default '0',
  `pl_qty` int(11) NOT NULL default '0',
  `sertax_group_id` int(11) default '0',
  `sertax_det_id` int(11) default '0',
  `stax_per` double default '0',
  `turn_group_id` int(11) default '0',
  `turn_det_id` int(11) default '0',
  `turn_per` double default '0',
  `turn_amt` double default '0',
  `onturn_amt` double default '0',
  `ins_group_id` int(11) default '0',
  `ins_det_id` int(11) default '0',
  `ins_per` double default '0',
  `onins_amt` double default '0',
  `ins_amt_rs` double default '0',
  `chgs_group_id` int(11) default '0',
  `chgs_det_id` int(11) default '0',
  `chgs_per` double default '0',
  `onchgs_amt` double default '0',
  `chgs_amt_rs` double default '0',
  `brok_group_id` varchar(10) default NULL,
  `brok_det_id` int(11) default '0',
  `brok_perc` double default '0',
  `brok_rs` double default '0',
  `brok_per` double default '0',
  `from_detid` int(11) NOT NULL default '0',
  `link_no` int(11) default '0',
  `is_parent` char(1) NOT NULL default 'N',
  `dt_of_mat` date default '0000-00-00',
  `is_ca_put` char(2) default '',
  `strike_rate` double(12,2) default '0.00',
  `premium` double(12,2) NOT NULL default '0.00',
  `rate` double(12,4) NOT NULL default '0.0000',
  `mrkt_rate` double NOT NULL default '0',
  `rec_type` varchar(15) default '',
  `cut_type` varchar(15) default NULL,
  `remarks` varchar(25) default '',
  `userid` varchar(15) default NULL,
  `com_status` int(11) default '0',
  `com_book_type` varchar(10) default '',
  `com_mkt_type` varchar(10) default '',
  `com_user_id` int(11) default '0',
  `com_branch_no` varchar(10) default '',
  `com_pro_cli` int(11) default '0',
  `com_cmid` varchar(10) default '',
  `is_manual_brok` int(11) NOT NULL default '0',
  `tran_hdr_id` int(11) default '0',
  `perdeal_brokamt` double(12,2) default '0.00',
  PRIMARY KEY  (`det_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"

Sorry, but select you presented works OK on your table in mysql command line client:

mysql> select v_dt, sum(sqty) as rect_qty, sum(bqty) as issue_qty from trtrade2
    -> group by v_dt;
Empty set (0.01 sec)

mysql> create table tttt select v_dt, sum(sqty) as rect_qty, sum(bqty) as issue_qty from trtrade2 group by v_dt;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tttt;
+-----------+---------------+------+-----+------------+-------+
| Field     | Type          | Null | Key | Default    | Extra |
+-----------+---------------+------+-----+------------+-------+
| v_dt      | date          | NO   |     | 0000-00-00 |       |
| rect_qty  | decimal(32,0) | YES  |     |            |       |
| issue_qty | decimal(32,0) | YES  |     |            |       |
+-----------+---------------+------+-----+------------+-------+
3 rows in set (0.03 sec)

So, if you have any problems with results type etc. it is not a server problem, but Connector/NET (or Connector/ODBC) pronblem. Please, specify the exact version of Connector used.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".