[MDEV-33375] Parse issue when creating table with generated columns. Created: 2024-02-02  Updated: 2024-02-07  Resolved: 2024-02-05

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table, Virtual Columns
Affects Version/s: 10.6
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Mark Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: Compatibility, innodb, replication
Environment:

Ubuntu stable.

mysqld Ver 10.6.12-MariaDB-0ubuntu0.22.04.1 for debian-linux-gnu on x86_64 (Ubuntu 22.04)


 Description   

Originally, I thought this was an issue with mysqldump. But, after experimenting with workarounds, it seems like it is a parsing bug. Instead of being able to subtract, It seems like you have to multiply by -1, then add. Again, this syntax was generated with mysql dump.

This DOES NOT work:

`broadcast` int(10) unsigned GENERATED ALWAYS AS (`ip` | (0x0100000000 >> `cidr`) - 1) STORED,

This DOES work:

`broadcast` int(10) unsigned GENERATED ALWAYS AS (`ip` | (0x0100000000 >> `cidr`) + (-1 * 1)) STORED,

Full command (DOES NOT WORK):

CREATE TABLE `parsetest` (                                                                                                          
  `ip` int(10) unsigned NOT NULL,                                                                                                
  `cidr` tinyint(4) NOT NULL,                                                                                                    
  `network` int(10) unsigned GENERATED ALWAYS AS (`ip` & 0xffffffff ^ ((0x01 << 32 - `cidr`) - 1)) STORED,                       
  `broadcast` int(10) unsigned GENERATED ALWAYS AS (`ip` | (0x0100000000 >> `cidr`) - 1) STORED,
 
  PRIMARY KEY (`ip`)                                                                                                             
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

FULL Command (DOES WORK):

CREATE TABLE `parsetest` (                                         
  `ip` int(10) unsigned NOT NULL,                                                                                                
  `cidr` tinyint(4) NOT NULL,                                                                                                    
  `network` int(10) unsigned GENERATED ALWAYS AS (`ip` & 0xffffffff ^ ((0x01 << 32 + (-1 * `cidr`)) + (-1 * 1))) STORED,                       
  `broadcast` int(10) unsigned GENERATED ALWAYS AS (`ip` | (0x0100000000 >> `cidr`) + (-1 * 1)) STORED,                                
  PRIMARY KEY (`ip`)                                                                                                             
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is the full output:

MariaDB [moirai]> CREATE TABLE `parsetest` (                                                                                         
    ->   `ip` int(10) unsigned NOT NULL,                                                                                             
    ->   `cidr` tinyint(4) NOT NULL,                                                                                                 
    ->   `network` int(10) unsigned GENERATED ALWAYS AS (`ip` & 0xffffffff ^ ((0x01 << 32 - `cidr`) - 1)) STORED,                    
    ->   `broadcast` int(10) unsigned GENERATED ALWAYS AS (`ip` | (0x0100000000 >> `cidr`) - 1) STORED,
 
    ->   PRIMARY KEY (`ip`)                                                                                                          
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ERROR 1901 (HY000): Function or expression '`ip` & 0xffffffff ^ ((0x01 << 32 - `cidr`) - 1)' cannot be used in the GENERATED ALWAYS AS clause of `network`
 
MariaDB [moirai]> CREATE TABLE `parsetest` (
 
    ->   `ip` int(10) unsigned NOT NULL,                                                                                             
    ->   `cidr` tinyint(4) NOT NULL,                                                                                                 
    ->   `network` int(10) unsigned GENERATED ALWAYS AS (`ip` & 0xffffffff ^ ((0x01 << 32 + (-1 * `cidr`)) + (-1 * 1))) STORED,      
    ->   `broadcast` int(10) unsigned GENERATED ALWAYS AS (`ip` | (0x0100000000 >> `cidr`) + (-1 * 1)) STORED,                       
    ->   PRIMARY KEY (`ip`)                                                                                                          
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;



 Comments   
Comment by Sergei Golubchik [ 2024-02-05 ]

This is correct. Try this:

$ mariadb --column-type-info
 
...
 
MariaDB [test]> select (10 >> 1) - 1, (10 >> 1) + (-1*1);
 
Field   1:  `(10 >> 1) - 1`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       LONGLONG
 
Collation:  binary (63)
 
Length:     22
 
Max_length: 1
 
Decimals:   0
 
Flags:      NOT_NULL UNSIGNED BINARY NUM 
 
 
Field   2:  `(10 >> 1) + (-1*1)`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       LONGLONG
 
Collation:  binary (63)
 
Length:     22
 
Max_length: 1
 
Decimals:   0
 
Flags:      NOT_NULL UNSIGNED BINARY NUM 
 
 
+---------------+--------------------+
 
| (10 >> 1) - 1 | (10 >> 1) + (-1*1) |
 
+---------------+--------------------+
 
|             4 |                  4 |
 
+---------------+--------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> set sql_mode=no_unsigned_subtraction;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select (10 >> 1) - 1, (10 >> 1) + (-1*1);
 
Field   1:  `(10 >> 1) - 1`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       LONGLONG
 
Collation:  binary (63)
 
Length:     22
 
Max_length: 1
 
Decimals:   0
 
Flags:      NOT_NULL BINARY NUM 
 
 
Field   2:  `(10 >> 1) + (-1*1)`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       LONGLONG
 
Collation:  binary (63)
 
Length:     22
 
Max_length: 1
 
Decimals:   0
 
Flags:      NOT_NULL UNSIGNED BINARY NUM 
 
 
+---------------+--------------------+
 
| (10 >> 1) - 1 | (10 >> 1) + (-1*1) |
 
+---------------+--------------------+
 
|             4 |                  4 |
 
+---------------+--------------------+
 
1 row in set (0.001 sec)

Note that the second column is always unsigned, while the first column changes the signedness depending on the sql_mode. It means that the second column does not depend on the environment can can be safely used as a stored generated column. And the first column depends on the environment, that is, it cannot be stored.

The first column has to be rewritten to not depend on the environment. While your approach works, the documented solution is to use a cast:

`broadcast` int(10) unsigned GENERATED ALWAYS AS (CAST(`ip` | (0x0100000000 >> `cidr`) AS SIGNED)- 1) STORED,

and now there is no unsigned subtraction, both arguments are signed and the result does not depend on the environment anymore.

Comment by Mark [ 2024-02-06 ]

note, the original, non-working syntax was generated by mysqldump

Comment by Sergei Golubchik [ 2024-02-06 ]

Yes, I did notice it. I tried to repeat it and failed. I thought, perhaps you had the working syntax with + (-1 * 1) and mysqldump replaced it with non-working -1, but it didn't, it prints this workaround as + -1 * 1 without parentheses, but working nevertheless.

If you can find somewhere the original syntax you've used to create the table — that is, a syntax that can be used to create a table, but in the dump it no longer works — please, add a comment here and I'll reopen the issue.

Or, perhaps, you were hit by MDEV-33389. That is, may be you managed to create the table under no_unsigned_subtraction sql mode. If this is the case, it'll be fixed as MDEV-33389.

Comment by Mark [ 2024-02-07 ]

so, on the old box, with an older version of mariadb, the "show create table" output, has the syntax that subtracts, not the modification I had to make.

Generated at Thu Feb 08 10:38:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.