MySQL Forums
Forum List  »  MySQL Workbench

Re: Bug in DEFAULT expr in column definition of Create table?
Posted by: Ken Gillett
Date: June 07, 2019 04:46AM

Peter Brawley Wrote:
-------------------------------------------------------
> > an expression used as a DEFAULT must be
> surrounded by parentheses (to distinguish it from
> literals)
>
> ?! A default value in most cases is a
> literal. Parens are needed only when the
> expression in other contexts requires parens, eg
> dates & strings but not numerics, see
> https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html
>
> Let's see the Create Table code.

As the page you linked to states:-

"enclose expression default values within parentheses to distinguish them from literal constant default values."

That seems quite clear to me, but WB doesn't do it, simply forgetting to include the parentheses. Here's the error also showing the create table code:-

--
Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UUID_TO_BIN(UUID(), true) COMMENT 'Binary & swapped UUID',
`img_uid` CHAR(36) ' at line 7
SQL Code:
-- -----------------------------------------------------
-- Table `MyDB`.`marques`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `MyDB `.`marques` (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`img_bin` BINARY(16) NULL DEFAULT UUID_TO_BIN(UUID(), true) COMMENT 'Binary & swapped UUID',
`img_uid` CHAR(36) GENERATED ALWAYS AS (BIN_TO_UUID(img_bin, true)) COMMENT 'UUID string',
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC),
UNIQUE INDEX `img_id_UNIQUE` (`img_bin` ASC))
ENGINE = InnoDB
COMMENT = 'Manufacturer’s used name'

SQL script execution finished: statements: 5 succeeded, 1 failed
--

the img_bin column def. does not include parentheses around the DEFAULT expression. Not only do the MySQL docs state these are required, but if you change that line to this:-

        `img_bin` BINARY(16) NULL DEFAULT (UUID_TO_BIN(UUID(), true)) COMMENT 'Binary & swapped UUID',

i.e. the simple addition of the parentheses as the docs state are required, it works perfectly with NO error. This is not an isolated case as I have 4 tables requiring this default and each one had missing parentheses as written by WB and all causing an error. Adding the parentheses to each of those 4 create table statements and the entire 'Forward Engineer' (or Synch) process ran without error.

So I am at a loss to know what could cause this apart from it being a bug in WB which creates SQL code that doesn't work due to a clear violation of their own rules on such code. Correct that omission in the code and it works perfectly. What else can it be?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Bug in DEFAULT expr in column definition of Create table?
64
June 07, 2019 04:46AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.