Bug #3589 Problem with Data Type 'LONG VARCHAR'

• Submitted: 28 Apr 2004 14:56
• Modified: 3 Sep 2004 16:51
• Reporter: Sanjay Patil
• Status: Can't repeat
• Category: MaxDB
• Severity: S1 (Critical)
• Version: 7.5.00.08
• OS: Windows (Windows 2000 , Linux)
• Assigned to: michel verwey
• CPU Architecture: Any

[28 Apr 2004 14:56] Sanjay Patil

Description:
Insert statement fails for the table with column of type 'LONG VARCHAR' provided that the insert statement contains atleast one function call.
 e.x Now() for system time.

How to repeat:
1. Create table 

CREATE TABLE PROJECT
(
    SYS_SID		NUMERIC(5)	NOT NULL,
    PRJ_SID		NUMERIC(6)	NOT NULL,
    PRJ_SNP_SID    NUMERIC(7)	NOT NULL,
    OBJ_ISSUE_DSC	LONG,
    OBJ_VER_DSC0	            LONG,
    ROW_TS		    TIMESTAMP       NOT NULL,
   CONSTRAINT PRJ_PK   PRIMARY KEY (SYS_SID, PRJ_SID, PRJ_SNP_SID)
)

2. Execute the insert statement 

     INSERT INTO PROJECT (
                           SYS_SID,
                           PRJ_SID,
                           PRJ_SNP_SID,
                           OBJ_ISSUE_DSC,
                           OBJ_VER_DSC0,
                          ROW_TS
                          )
                   VALUES (
                           0,
                           0,
                           0,
                          'This object has no issues',
                          'The obect version is 2.7.0.344',
                          now()
                          )
                          

Error
  Integrity constraint violation;-8006 POS(1) Data types must be compatible:OBJ_ISSUE_DSC

 <NOTE> The error occurs only if the insert statement has a any function( In the above case it is NOW() )</NOTE>

[3 Sep 2004 16:51] MySQL Verification Team

Hi,

I wasn't able to repeat this behavior:

[victoria@victoria maxdb]$ ./programs/bin/dbmcli -d TEST -u vita,vita sql_execute 'CREATE TABLE PROJECT (SYS_SID NUMERIC(5) NOT NULL, PRJ_SID NUMERIC(6) NOT NULL, PRJ_SNP_SID  NUMERIC(7) NOT NULL, OBJ_ISSUE_DSC LONG, OBJ_VER_DSC0 LONG, ROW_TS TIMESTAMP NOT NULL, CONSTRAINT PRJ_PK PRIMARY KEY (SYS_SID, PRJ_SID, PRJ_SNP_SID))'
OK
[victoria@victoria maxdb]$
[victoria@victoria maxdb]$ ./programs/bin/dbmcli -d TEST -u vita,vita sql_execute "INSERT INTO PROJECT \
> (SYS_SID, PRJ_SID, PRJ_SNP_SID, OBJ_ISSUE_DSC, OBJ_VER_DSC0, ROW_TS)\
> VALUES (0, 0, 0, 'This object has no issues', 'The obect version is 2.7.0.344', now())"
OK
[victoria@victoria maxdb]$ ./programs/bin/dbmcli -d TEST -u vita,vita sql_execute 'SELECT * FROM PROJECT'
OK
END
0;0;0;'This object has no issues';'The obect version is 2.7.0.344';'20040903184151812603'