Bug #94221 Incorrect query generation on Review SQL when modified fields of type BIT
Submitted: 6 Feb 2019 14:12 Modified: 7 Feb 2019 7:36
Reporter: mert can Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.13 OS:Windows (Microsoft Windows 10 Pro)
Assigned to: CPU Architecture:x86
Tags: WBBugReporter

[6 Feb 2019 14:12] mert can
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.
[6 Feb 2019 16:31] MySQL Verification Team
Thank you for the bug report. Looks related/duplicate of https://bugs.mysql.com/bug.php?id=93060; Please check.
[7 Feb 2019 7:02] mert can
Yes, though the system in the bug report https://bugs.mysql.com/bug.php?id=93060 is Ubuntu, it was known then that the Windows version is affected too. 

Anyways, I hope this will get resolved soon.

Best,
[7 Feb 2019 7:36] MySQL Verification Team
Thank you for the feedback.