Bug #93060 paste rows create wrong insert queries on apply
Submitted: 2 Nov 2018 12:21 Modified: 13 Nov 2018 18:15
Reporter: Peter Szabo Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:8.0.12/8.0.13, 8.0.34 OS:Ubuntu (18.04.1)
Assigned to: CPU Architecture:Any

[2 Nov 2018 12:21] Peter Szabo
"paste rows" to result set creates invalid insert queries on apply

How to repeat:
1) select a table which has VARCHAR and boolean columns.
2) copy row from the result set
3) modify and add another rows also in the text editor
4) paste back the multiple rows
5) apply

the insert query contains from the first boolean column b'', even if the type is varchar.

I need to remove the b from each of individual value manually, which is tedious.

generated insert queries
INSERT INTO `zeno` (`parent_uid`, `priv`) VALUES ('cc84c4ac-586a-8d65-27ba-08b1a073fd43', 'en', 'Nonreligious', b'0');
INSERT INTO `zeno` (`parent_uid`, `priv`) VALUES (b'cc84c4ac-586a-8d65-27ba-08b1a073fd43', b'0');

I wrote a regex replace workaround:
b('[^0-1]+(.*?),) -> $1
[2 Nov 2018 12:47] Peter Szabo
version updated
[2 Nov 2018 14:59] MySQL Verification Team
Thank you for the bug report. Please provide a test case with create table, data insert, etc. Just the description of how to repeat isn't effective way to repeat. Thanks.
[2 Nov 2018 17:16] Peter Szabo
zero table has two columns

Attachment: myscoutee_db_zero.sql (application/sql, text), 1.88 KiB.

[2 Nov 2018 17:17] Peter Szabo
Pls. select the "zero" table, and follow the instruction on how to repeat
[2 Nov 2018 17:21] Peter Szabo
right click on the row in the result set and copy to clipboard
Pls. notice if you pase back, than it fails b'0' should be there.

'1', 'errr', '0'

NULL, 'Akos', b'0'
NULL, 'Pete', b'0'

paste back these two rows, and apply

please notice that the insert queries are invalid
[13 Nov 2018 18:15] MySQL Verification Team
Thank you for the feedback. Windows version affected too.
[12 Feb 2019 20:15] MySQL Verification Team
https://bugs.mysql.com/bug.php?id=94298 marked as duplicate of this one.
[13 Mar 2019 22:06] MySQL Verification Team
https://bugs.mysql.com/bug.php?id=94644 marked as duplicate of this one.
[21 Mar 2019 13:17] MySQL Verification Team
https://bugs.mysql.com/bug.php?id=94734 marked as duplicate of this one.
[15 Nov 2019 22:58] Damiön la Bagh
On Copy MySQL Workbench adding bit(1) type to non bit(1) columns leading to invalid SQL

Attachment: 2019-11-15Workbench8.0.18_WrongBitTypeOnCopy.png (image/png, text), 276.37 KiB.

[15 Nov 2019 23:02] Damiön la Bagh
Here is the original paste directly from MySQL workbench. As shown in the screenshot

INSERT INTO `kassa`.`PRODUCTS` (`ID`, `REFERENCE`, `CODE`, `CODETYPE`, `NAME`, `PRICEBUY`, `PRICESELL`, `CATEGORY`, `TAXCAT`, `STOCKCOST`, `STOCKVOLUME`, `ISCOM`, `ISSCALE`, `ISKITCHEN`, `PRINTKB`, `SENDSTATUS`, `ISSERVICE`, `DISPLAY`, `ATTRIBUTES`, `ISVPRICE`, `ISVERPATRIB`, `TEXTTIP`, `WARRANTY`, `IMAGE`, `STOCKUNITS`, `ALIAS`, `ALWAYSAVAILABLE`, `DISCOUNTED`, `CANDISCOUNT`, `ISCATALOG`, `CATORDER`, `ISPACK`, `MANAGESTOCK`, `SITEGUID`) VALUES ('1d02754c-3f03-44e5-b9f5-2b498e642c3b', 'SNH109', 'SNH109', 'CODE128', 'Worstenbroodje', '0.8', '2.0642201834862384', '3934f96c-aaa6-4870-8d17-7050944dc958', '001', '0', '0', b'0', b'0', b'0', b'0', b'0', b'0', b'<html>Worstenbroodje', ?, b'0', b'0', b'', b'0', ?, b'0', b'', b'0', b'no', b'1', b'1', b'109', b'0', b'1', b'f4cea4c7-1cef-416f-a661-cf86b76a17b6');
[15 Nov 2019 23:05] Damiön la Bagh
Proof of wrong bit type in the review SQL dialog

Attachment: 2019-11-16Workbench8.0.18WrongBitTypeReviewSQL.png (image/png, text), 53.52 KiB.

[15 Nov 2019 23:09] Damiön la Bagh
Also affects version 8.0.18
The severity should be adjusted to a higher level as well as it takes a large amount of extra time to copy rows between databases. Especially since Blob items are also not copied.
[25 Nov 2019 20:14] MySQL Verification Team
https://bugs.mysql.com/bug.php?id=97774 marked as duplicate of this one.
[15 Nov 2023 12:11] MySQL Verification Team
Bug #113087 marked as duplicate of this one.