You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I had altered type of filed in our database from BIGINT to VARCHAR(21). After alter table I was unable to do selects from this table if select clause contained string value in equals expression over this field. After backup and restore the selects worked. But this was not an option for our database upgrade script. So we were forced to create new VARCHAR field, copy values from BIGINT to VARCHAR field, drop BIGINT filed and rename the second field to original name.
I have created script that simulates the problem.
test script:
SET SQL DIALECT 3;
CREATE DATABASE 'localhost:C:\test.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
COMMIT;
CONNECT 'localhost:C:\test.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
CREATE TABLE TEST1(
ID INTEGER,
TEST_FIELD BIGINT,
PRIMARY KEY(ID)
);
COMMIT;
INSERT INTO TEST1(ID, TEST_FIELD)
VALUES(1, 234);
COMMIT;
ALTER TABLE TEST1
ALTER TEST_FIELD TYPE VARCHAR(21);
COMMIT;
SELECT ID, TEST_FIELD
FROM TEST1
WHERE TEST_FIELD = 'A';
expected result:
empty result set
actual result:
ID TEST_FIELD
============ =====================
Statement failed, SQLCODE = -413
conversion error from string "A"
After line 26 in file test.sql
The text was updated successfully, but these errors were encountered:
Submitted by: Simeon Bodurov (simeon.bodurov)
Is related to CORE1432
Is related to QA441
I had altered type of filed in our database from BIGINT to VARCHAR(21). After alter table I was unable to do selects from this table if select clause contained string value in equals expression over this field. After backup and restore the selects worked. But this was not an option for our database upgrade script. So we were forced to create new VARCHAR field, copy values from BIGINT to VARCHAR field, drop BIGINT filed and rename the second field to original name.
I have created script that simulates the problem.
test script:
SET SQL DIALECT 3;
CREATE DATABASE 'localhost:C:\test.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
COMMIT;
CONNECT 'localhost:C:\test.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
CREATE TABLE TEST1(
ID INTEGER,
TEST_FIELD BIGINT,
PRIMARY KEY(ID)
);
COMMIT;
INSERT INTO TEST1(ID, TEST_FIELD)
VALUES(1, 234);
COMMIT;
ALTER TABLE TEST1
ALTER TEST_FIELD TYPE VARCHAR(21);
COMMIT;
SELECT ID, TEST_FIELD
FROM TEST1
WHERE TEST_FIELD = 'A';
expected result:
empty result set
actual result:
ID TEST_FIELD
============ =====================
Statement failed, SQLCODE = -413
conversion error from string "A"
After line 26 in file test.sql
The text was updated successfully, but these errors were encountered: