Description:
When I edit a cell in a table with a column of type BIT, the insertion query is generated incorrectly, assuming the types of modified fields all are BITs.
To give an example :
Suppose a table with below structure :
Id(INT) Name(VARCHAR) SomeValue(BIT) Other(VARCHAR)[simply not BIT]
Normally, an insertion with values (NULL, 'name' , 1 ) is generated like so :
INSERT INTO mytable (Id, Name, SomeValue) VALUES (NULL, 'name' , b'1');
Last column deliberately omitted and please notice the b'1' conversion.
This is perfectly normal.
But if there is any field in the insertion AFTER the first BIT field, like below :
(NULL, 'name' , 1 , 'testvalue')
It gets converted into this :
INSERT INTO mytable (Id, Name, SomeValue, OtherValue) VALUES (NULL, 'name' , b'1', b'testing');
Please notice b'testing'.
What's even more weird is, the generation failure occurs AFTER the FIRST BIT field.
Example of the said scenario is :
Insertion values :
(NULL, 'name1' , 'first_row')
(NULL, 'name2' , 'second_row')
(NULL, 'name3' ,1, 'third_row')
(NULL, 'name4' ,2, 'forth_row')
Generated queries :
INSERT INTO mytable (Id, Name, Other) VALUES (NULL, 'name1' , 'random'); <- No BIT field included in insertion
INSERT INTO mytable (Id, Name, Other) VALUES (NULL, 'name2' , 'harmless'); <- No BIT field included on second row as well
INSERT INTO mytable (Id, Name, SomeValue, Other) VALUES (NULL, 'name3' , b'1', b'blah'); <-Now it goes crazy, RIGHT AFTER BIT-type field
INSERT INTO mytable (Id, Name, SomeValue, Other) VALUES (b'NULL', b'name4' , b'0', b'blah'); <- and keeps going
INSERT INTO mytable (Id , Other) VALUES (b'NULL', b'blah'); <- No matter if the insertion included BIT-type field or not.
How to repeat:
Simply create a table with columns :
Id(INT) Name(VARCHAR) SomeValue(BIT) Other(VARCHAR)[something not BIT]
Insert below values by editing cells on workbench:
(NULL, 'name1' , 'first_row')
(NULL, 'name2' , 'second_row')
(NULL, 'name3' ,1, 'third_row')
(NULL, 'name4' ,2, 'forth_row')
Click Apply.
See generated malformed query in Review Dialog.
Suggested fix:
No idea.