Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6
-
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;
|