Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Default values (expressions) are shown wrong in "CREATE CODE" tab for MariaDB 10.2 #74

Open
vaintroub opened this issue Jan 8, 2018 · 2 comments

Comments

Projects
None yet
3 participants
@vaintroub
Copy link

commented Jan 8, 2018

Expected behavior

HeidiSQL's "create code" tab for the table's DDL should be semantically equivalent to "show create table" SQL command. Also in case where DEFAULT column values is an expressions (which is new MariaDB 10.2 feature, but also SQL standard one) , "Create Code" should show the expression correctly.

Current behavior

when DEFAULT for a COLUMN is an expression, HeidiSQL's "create code" does not understand it, and makes a character string instead of expression. In the "steps to reproduce" below, functions curdate(),curtime() are converted to character strings 'curdate' and 'curtime', and expression (rand()*4) ) is converted to character string 'rand'

Failure information (for bugs)

f5d9603
provides partial fix for special cases.

Steps to reproduce

  1. create a table in mariadb like this
Create table t (
dt date default curdate(),
tm time default curtime(),
d int default (rand()*4) );
  1. Rightclick on table in the tree, Edit, Choose "Create code" TAB to see how heidiSQL interprets it
CREATE TABLE `t` (
	`dt` DATE NULL DEFAULT 'curdate',
	`tm` TIME NULL DEFAULT 'curtime',
	`d` INT(11) NULL DEFAULT 'rand'
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

Context

Please provide any relevant information about your setup. This is important in case the issue is not reproducible except for under certain conditions.

  • HeidiSQL version - 9.5.0 Revision 5219
  • Database system + version: MariaDB 10.2
  • Operating system: Windows

@vaintroub vaintroub changed the title Default values (expressions) are shown wrong in MariaDB 10.2 Default values (expressions) are shown wrong in "CREATE CODE" tab for MariaDB 10.2 Jan 8, 2018

@rentalhost

This comment has been minimized.

Copy link
Collaborator

commented Feb 12, 2018

Same as #29.

Possible solutions when client is MariaDB 10.2:

  • Solution 1: on the Custom: input on Default column on Table editor should have an nested checkbox like Run as function;
  • Solution 2: create a new option below Custom: like Run as function:;
  • Solution 3: just create a checkbox without label on right-side of Custom: input with a hint that defines what it mean;
  • Solution 4 (danger workaround): if Custom: have any ( identify it directly as an function. But it could conflicts with a real definition from user "as text" that have ( on the content;

To be discarded: as in #29 is suggested that we have a context menu with available options, but it will not works once that you could use an expression like the example above (rand() * 4).

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Apr 21, 2019

Sorry for the long delay. But I just spent some of the lazy easter hours on this topic, where HeidiSQL's table editor is quite broken yet.

In the table editor, I just removed the radio button for the very specific CURRENT_TIMESTAMP and the checkbox for the ON UPDATE CURRENT_TIMESTAMP clause. Instead, we now have an "Expression" input field, along with an "On update" field:

grafik

In its parsing, HeidiSQL also supports expressions other than CURRENT_TIMESTAMP. I'm just yet unsure about which expressions require parentheses around them. Looks like normal functions don't need + want them, while an expression like 1 + 3 requires them. So HeidiSQL does not yet automatically add parentheses around the expression, the user has to manually enter them:

grafik

ansgarbecker added a commit that referenced this issue Apr 29, 2019

ansgarbecker added a commit that referenced this issue May 5, 2019

Detect ON UPDATE clause's content with a two-step approach, so it doe…
…s not eat a potentially existing COMMENT clause after it. Closes #637. Broken in 9fc7150 for #74.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.