Bug with virtual columns

Hi,

If I create a virtual column, HeidiSQL works as expected.

But if I go back to table's definition, HeidiSQL doesn't load the expression. It shows the field as non-virtual.

I hope you can fix it, because it's a pain.

Thank you,
Lucian

Can you please add some ALTER TABLE code for the virtual column you created? Perhaps I have a glitch in some regular expression, which doesn't work correctly with specific data types, parentheses or whatever.

I copied from HeidiSQL:

ALTER TABLE `items`
CHANGE COLUMN `text0` `text0` TEXT AS (REPLACE(REPLACE(`addr`,'ș','s'),'ş','s')) PERSISTENT AFTER `addr`;

MariaDB 10.0.22 with HeidiSQL 9.3.0.5003 and I've got the same problem. Expression and Virtuality columns are empty, SHOW CREATE TABLE shows correct data, fields are calculated as expected.

One other thing: even if the PERSISTENT/VIRTUAL column is created correctly, GUI does not show the COMMENT either. So, if you manually ALTER TABLE ... CHANGE COLUMN with virtual fields and afterwards change it from the GUI (lets say you modify a field length), it loses the VIRTUAL part.

Manually I do:

ALTER TABLE `users`
CHANGE COLUMN `full_name` `full_name` VARCHAR(255) AS (concat(first_name,' ',last_name)) PERSISTENT COMMENT 'PERSISENT' ;
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 1 query: 0,047 sec. */

After refresh in the GUI, the comment is empty. If I change it to something, here is what HeidiSQL generates, as it obviously doesn't parse the SHOW CREATE correctly:

ALTER TABLE `users`
CHANGE COLUMN `full_name` `full_name` VARCHAR(255) NULL COMMENT 'PERS' COLLATE 'utf8_slovenian_ci' AFTER `last_name`;

Hope this helps in debugging session.

I can't get that bug reproduced here. Virtuality, expression and comment is saved and displayed correctly, also after going to another table and back again. See my screeny.

My CREATE CODE tab shows the following:

CREATE TABLE `test` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) AS (concat(id+1, ' hello')) VIRTUAL,
`addr` VARCHAR(50) NULL DEFAULT NULL,
`text0` TEXT AS (REPLACE(REPLACE(`addr`,'ș','s'),'ş','s')) PERSISTENT COMMENT 'This is a comment',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4

SHOW CREATE TABLE returns the following, quite similar code:

CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) AS (concat(id+1, ' hello')) VIRTUAL,
`addr` varchar(50) DEFAULT NULL,
`text0` text AS (REPLACE(REPLACE(`addr`,'ș','s'),'ş','s')) PERSISTENT COMMENT 'This is a comment',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

I guess in order to reproduce the issues I need the full result of SHOW CREATE TABLE from both of your tables (alesvaupotic + luc7v)

Hi, Ansgar.

I am not at my computer at the moment but could it be related to column collation? I see no column COLLATE in your example, I have them on all string fields.

Just saw your youtube video, and I finally decided to write down what it is showing in the result of SHOW CREATE TABLE:

CREATE TABLE vtest (
id INT NOT NULL AUTO_INCREMENT,
`text` VARCHAR(50) COLLATE utf8_slovenian_ci NOT NULL,
`text2` VARCHAR(50) COLLATE utf8_slovenian_ci AS (UPPER(`text`)) PERSISTENT,
PRIMARY KEY (id)
)
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS (UPPER(`text`)) PERSISTENT,
PRIMARY KEY (id)
)' at line 4 */

Scratching my head... Seems that my MariaDB doesn't support virtual columns any longer. MariaDB 10.1.8. Is there a server setting which I need to activate before I can do that?

I can't seem to add a collation to a column any longer...

My.cnf is configured as follows on my server: (just diffs from the standard file ...)

[client]
default-character-set=utf8
[mysqld]
collation-server = utf8_slovenian_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

and it works for me as shown in the video on 10.0.22. I think you should add text2 without the collation and it will come out the same as text as it inherits it's collation and is derived from it.

Think I fixed that in r5006. The collation was indeed the problem. Or, more exactly, HeidiSQL expected the collation after the "AS (..)" expression, and MariaDB does it exactly the other way around in SHOW CREATE TABLE.

Thanks, Ansgar.

I can confirm it works in 5006, although you still need to clear the collation in persistent columns before saving. But that's not Heidi, it's MySQL error in my opinion. Anyway, I am a happy camper now as it won't mess with my fields anymore.

Will keep you posted if anything unusual happens.

I've installed the latest version 5012, but I cannot see the definition of virtual column.

I have this table definition (in short):

CREATE TABLE `test` (
`ID` INT(11) NOT NULL DEFAULT '0',
`Field1` TIMESTAMP NULL,
`Field19` SMALLINT(6) GENERATED ALWAYS AS (CASE WHEN `Field1` IS NOT NULL THEN 1 ELSE 0 END) VIRTUAL,
PRIMARY KEY (`ID`)) ENGINE=InnoDB;


But when I select "CREATE CODE" I can see:

CREATE TABLE `test` (
`ID` INT(11) '0',
`Field1` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`Field19` SMALLINT(6) NOT NULL)

I was trying to refresh view, to switch to another table and back but with no success.

HeidiSQL does not yet see the "GENERATED ALWAYS" part in a column definition. Is that MariaDB-only syntax, MySQL-only, or both?

r5013 should parse such virtual columns correctly.

I'm affraid the problem is not solved. I have MySQL 5.7.9 (GA version) installed.
The one and only difference in syntax of virtual column is that MySQL attribute is "STORED" but MariaDB's is "PERSISTENT".

Yes, plus that "GENERATED ALWAYS" postfix, right?

I can see such columns as expected in HeidiSQL's table editor now. What makes you think it's not yet solved?

As I wrote, definition is
`Field19` SMALLINT(6) GENERATED ALWAYS AS (CASE WHEN `Field1` IS NOT NULL THEN 1 ELSE 0 END) VIRTUAL

but in editor ("CREATE code" tab) I can see
`Field19` SMALLINT(6) NOT NULL

Dumb question: Did you update your HeidiSQL build...?

I have latest 5013 HeidiSQL...

This is getting resolved: https://mariadb.atlassian.net/browse/MDEV-7655

Just installed the 10.0.23 MariaDB and its DDL is without collations for virtual columns. Yeaaa!

But, I still don't see those columns parsed correctly in HeidiSQL.