MySQL Bugs: #93060: paste rows create wrong insert queries on apply

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:	None
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

Description:
"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

version updated

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.

zero table has two columns

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

Pls. select the "zero" table, and follow the instruction on how to repeat

1)
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'

2)
NULL, 'Akos', b'0'
NULL, 'Pete', b'0'

paste back these two rows, and apply

please notice that the insert queries are invalid

Thank you for the feedback. Windows version affected too.

https://bugs.mysql.com/bug.php?id=94298 marked as duplicate of this one.

https://bugs.mysql.com/bug.php?id=94644 marked as duplicate of this one.

https://bugs.mysql.com/bug.php?id=94734 marked as duplicate of this one.

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.

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');

Proof of wrong bit type in the review SQL dialog

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

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.

https://bugs.mysql.com/bug.php?id=97774 marked as duplicate of this one.

Bug #113087 marked as duplicate of this one.