| Bug #118551 | Inconsistant result after removing constraints on table | ||
|---|---|---|---|
| Submitted: | 29 Jun 17:59 | Modified: | 30 Jun 7:41 |
| Reporter: | Jiyuan Li | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 9.3.0 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | x86 | |
[29 Jun 17:59]
Jiyuan Li
[30 Jun 7:41]
MySQL Verification Team
Hello Jiyuan Li,
Thank you for the report and test case.
I quickly ran through your test case and observed that for NDB SE it didn't allow to create table itself and when I removed the part of the column definition then I was able to create and had no issues with the test case.
/bv/ushastry/Work/Binaries/mysql-cluster-9.3.0]$ bin/mysql -uroot -S/tmp/mysql_4.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 9.3.0-cluster MySQL Cluster Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.068 sec)
mysql> use test
Database changed
mysql> CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL STORAGE DISK COLUMN_FORMAT DYNAMIC UNIQUE)engine=ndb ;
ERROR 1005 (HY000): Can't create table 't0' (use SHOW WARNINGS for more info).
mysql>
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1478 | DYNAMIC column c0 with STORAGE DISK is not supported, column will become FIXED |
| Warning | 3014 | TABLESPACE option must be specified when using STORAGE DISK |
| Error | 1005 | Can't create table 't0' (use SHOW WARNINGS for more info). |
+---------+------+--------------------------------------------------------------------------------+
3 rows in set (0.000 sec)
mysql> CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL)engine=ndb ;
Query OK, 0 rows affected (0.139 sec)
mysql> REPLACE INTO t0(c0) VALUES(0);
Query OK, 1 row affected (0.002 sec)
mysql>
mysql> CREATE TABLE t00(c0 decimal(10,0));
Query OK, 0 rows affected (0.010 sec)
mysql> INSERT INTO t00 SELECT * FROM t0;
Query OK, 1 row affected (0.002 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (LEAST(1628627615, '0%畤'));
+------+
| c0 |
+------+
| 0 |
+------+
1 row in set (0.000 sec)
mysql>
mysql> SELECT ALL t00.c0 FROM t00 WHERE (t00.c0) IN (LEAST(1628627615, '0%畤'));
+------+
| c0 |
+------+
| 0 |
+------+
1 row in set (0.000 sec)
-- but with Innodb SE I can see the issue which you are complaining about.
mysql>
mysql> drop database test;
Query OK, 2 rows affected (0.116 sec)
mysql> create database test;
Query OK, 1 row affected (0.045 sec)
mysql> use test
Database changed
mysql> CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL STORAGE DISK COLUMN_FORMAT DYNAMIC UNIQUE) ;
Query OK, 0 rows affected (0.012 sec)
mysql> show create table t0\G
*************************** 1. row ***************************
Table: t0
Create Table: CREATE TABLE `t0` (
`c0` decimal(10,0) /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL,
UNIQUE KEY `c0` (`c0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.001 sec)
mysql> REPLACE INTO t0(c0) VALUES(0);
Query OK, 1 row affected (0.002 sec)
mysql>
mysql> CREATE TABLE t00(c0 decimal(10,0));
Query OK, 0 rows affected (0.010 sec)
mysql> INSERT INTO t00 SELECT * FROM t0;
Query OK, 1 row affected (0.002 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (LEAST(1628627615, '0%畤'));
Empty set, 1 warning (0.000 sec)
mysql>
mysql> SELECT ALL t00.c0 FROM t00 WHERE (t00.c0) IN (LEAST(1628627615, '0%畤'));
+------+
| c0 |
+------+
| 0 |
+------+
1 row in set (0.000 sec)
mysql>
regards,
Umesh
