I'm getting an error when running a set of SQL statements through an ODBC connection that change the data type for a column.
Error:
[HY000] Wrong value has been set [typeName=SQL_PUBLIC_X_0aae5780_0c94_4706_b144_f2ca2336a96e, fieldName=VALUE, fieldType=String, assignedValueType=int]
Is it intended behavior or expected error? Will this behavior change in future releases to support changing the data type of a column?
I'm using a fresh default Ignite (v2.7) instance connected from .Net Core (v2.2) via an OdbcConnection (System.Data.Odbc v4.5.0).
Stack Trace:
System.Data.Odbc.OdbcException (0x80131937): ERROR [HY000] Wrong value has been set [typeName=SQL_PUBLIC_X_0aae5780_0c94_4706_b144_f2ca2336a96e, fieldName=VALUE, fieldType=String, assignedValueType=int]
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at IgniteReproducer.Program.ChangeColumnType() in C:\...\IgniteMultistatementReproducer\IgniteMultistatementReproducer\Program.cs:line 51
Reproducer:
using System;
using System.Data.Odbc;
namespace IgniteReproducer {
internal class Program {
private static void Main(string[] args) {
ChangeColumnType();
}
private static void ChangeColumnType() {
try {
using (var conn = new OdbcConnection($"DRIVER={{Apache Ignite}};ADDRESS=localhost:10800;")) {
conn.Open();
var cmd = new OdbcCommand {
Connection = conn
};
cmd.CommandText = "DROP TABLE IF EXISTS X;";
var numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
cmd.CommandText = "CREATE TABLE X (Id INT, Value VARCHAR(36), PRIMARY KEY (Id));";
numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
cmd.CommandText = "INSERT INTO X (Id, Value) VALUES (1, 'a');";
numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
cmd.CommandText = "INSERT INTO X (Id, Value) VALUES (2, 'b');";
numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
cmd.CommandText = "ALTER TABLE X ADD COLUMN Temp INT;";
numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
cmd.CommandText = "UPDATE X SET Temp = 1;";
numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
cmd.CommandText = "ALTER TABLE X DROP COLUMN Value;";
numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
cmd.CommandText = "ALTER TABLE X ADD COLUMN Value INT;";
numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
cmd.CommandText = "UPDATE X SET Value = Temp;";
numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
cmd.CommandText = "ALTER TABLE X DROP COLUMN Temp;";
numRecordsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Num Records Affected: {numRecordsAffected}");
}
}
catch (Exception ex) {
Console.WriteLine(ex);
}
}
}
}
It is stated here, that when a column is dropped it is not actually removed from the cluster.
The command does not remove actual data from the cluster which means that if the column 'name' is dropped, the value of the 'name' will still be stored in the cluster. This limitation is to be addressed in the next releases.
This should be the reason for the error. The varchar data is still there.
And from this quote it looks like this will be resolved in the next releases.
User contributions licensed under CC BY-SA 3.0