Bug #46450 .NET Connector 6.0.4 incorrectly casts CHAR to DOUBLE
Submitted: 29 Jul 2009 14:19 Modified: 30 Aug 2009 3:43
Reporter: Leon de Almeida Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.0.4 OS:Microsoft Windows (Inserting 'A' into CHAR(1) column returns error "Truncated incorrect DOUBLE value: 'A'")
Assigned to: CPU Architecture:Any
Tags: .net, char, connector, double, incorrect CAST

[29 Jul 2009 14:19] Leon de Almeida
Description:
I have one table called 'validation' that is defined as follows:

+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| storage_id | int(10) unsigned | NO   | PRI | NULL    |       |
| status     | char(1)          | NO   |     | N       |       |
| reason     | varchar(255)     | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+

A second table called 'storage', defined as follows:

+-------------+----------------------+------+-----+---------+----------------+
| Field       | Type                 | Null | Key | Default | Extra          |
+-------------+----------------------+------+-----+---------+----------------+
| storage_id  | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| user_id     | smallint(5) unsigned | NO   | MUL | NULL    |                |
| app_id      | smallint(5) unsigned | NO   | MUL | NULL    |                |
| source_id   | smallint(5) unsigned | NO   | MUL | NULL    |                |
| event_date  | date                 | NO   | MUL | NULL    |                |
| start_tm    | time                 | NO   |     | NULL    |                |
| end_tm      | time                 | NO   |     | NULL    |                |
| identifier  | varchar(20)          | NO   |     | NULL    |                |
| description | varchar(255)         | NO   |     | NULL    |                |
+-------------+----------------------+------+-----+---------+----------------+

Both table engines is InnoDB.

Table 'validation' has FK from storage_id to storage.storage_id.

Table 'storage' has two triggers, defined as follows:

CREATE TRIGGER storage_to_validation_insert AFTER INSERT ON storage
    FOR EACH ROW 
        BEGIN
            INSERT INTO validation VALUES (NEW.storage_id, CAST('N' AS CHAR), '');
        END//

CREATE TRIGGER storage_to_validation_update AFTER UPDATE ON storage
    FOR EACH ROW
        BEGIN
            UPDATE validation V SET status = 'N' WHERE V.storage_id = NEW.storage_id;
        END//

When the 'storage' table is updated (via the .NET interface - I'm using Visual Studio 2008, VB, .NET Platform 3.5), the trigger is CORRECTLY run and the validation table receives the UPDATE to 'N' on the status field. So, since this is run on the server side, there is no issue on the server. Updating the 'validation' table through the command-line also works fine.

However, when the 'validation' table (status field) is updated (e.g., to 'A') via the GUI (thus through the connector), I receive the following error message: "Truncated incorrect DOUBLE value: 'A'"

How to repeat:
1. Create both tables:

CREATE  TABLE IF NOT EXISTS `storage` (
  `storage_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `user_id` SMALLINT UNSIGNED NOT NULL ,
  `app_id` SMALLINT UNSIGNED NOT NULL ,
  `source_id` SMALLINT UNSIGNED NOT NULL ,
  `event_date` DATE NOT NULL ,
  `start_tm` TIME NOT NULL ,
  `end_tm` TIME NOT NULL ,
  `identifier` VARCHAR(20) NOT NULL ,
  `description` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`storage_id`) ,
  INDEX `fk_storage_users_id` (`user_id` ASC) ,
  INDEX `fk_storage_source_id` (`source_id` ASC) ,
  INDEX `fk_storage_apps_id` (`app_id` ASC) ,
  INDEX `ik_storage_eventdate` (`event_date` ASC) ,
  INDEX `ik_storage_appid` (`app_id` ASC) ,
  INDEX `ik_storage_userid` (`user_id` ASC) ,
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `validation` (
  `storage_id` INT UNSIGNED NOT NULL ,
  `status` CHAR(1) NOT NULL,
  `reason` VARCHAR(255) NULL ,
  PRIMARY KEY (`storage_id`) ,
  INDEX `fk_rejected_storage_id` (`storage_id` ASC) ,
  CONSTRAINT `fk_rejected_storage_id`
    FOREIGN KEY (`storage_id` )
    REFERENCES storage` (`storage_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

2. Create the triggers:

DELIMITER //

CREATE TRIGGER storage_to_validation_insert AFTER INSERT ON storage
    FOR EACH ROW 
        BEGIN
            INSERT INTO validation VALUES (NEW.storage_id, 'N', '');
        END//

CREATE TRIGGER storage_to_validation_update AFTER UPDATE ON storage
    FOR EACH ROW
        BEGIN
            UPDATE validation V SET STATUS = CAST('N' AS CHAR) WHERE V.storage_id = NEW.storage_id;

        END//

DELIMITER ;

3. Add some records to 'storage':
INSERT INTO storage VALUES (1,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 1');
INSERT INTO storage VALUES (2,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 2');
INSERT INTO storage VALUES (3,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 3');
INSERT INTO storage VALUES (4,1,1,1,'2009-07-07','07:00','09:00','Test','Bogus Test 4');

4. Check 'validation'. There should be four records there, all with the status field set to 'N'.

5. Using the .NET Connector 6.0.4, run the following statement:
"UPDATE validation V SET V.status = 'A' AND V.reason = '' WHERE V.storage_id=1; "

6. You should receive the error message "Truncated incorrect DOUBLE value: 'A'"

Note: if you force the CAST, the value is inserted as '0':
7. Using the .NET Connector 6.0.4, run the following statement:
"UPDATE validation V SET V.status = CAST('A' AS CHAR) AND V.reason = '' WHERE V.storage_id=1; "

8. Check the 'validation' table records. The row with 'storage_id' = 1 should have its 'status' field set to '0'.

Suggested fix:
Is the Connector converting CHAR(1) fields to another Datatype? DOUBLE or BIT, perhaps?
[30 Jul 2009 3:43] Valeriy Kravchuk
Thank you for the problem report. This your problem is not related to connector. Please, check from the command line that

UPDATE validation V SET V.status = 'A' AND V.reason = '' WHERE V.storage_id=1; 

will give you at least a warning (run SHOW WARNINGS\G after this UPDATE) if not the same error message (it may depend on SQL mode).

The update above is logically incorrect. If you need to update 2 columns, use comma:

UPDATE validation V SET V.status = 'A', V.reason = '' WHERE V.storage_id=1;

Check http://dev.mysql.com/doc/refman/5.1/en/update.html.
[30 Aug 2009 23:00] Bugs System
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".