Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Not a Bug
-
10.11.11
-
None
-
MariaDB [database1]> select version();
+--------------------------+
| version() |
+--------------------------+
| 10.11.11-MariaDB-ubu2204 |
+--------------------------+
1 row in set (0.000 sec)
-
Not for Release Notes
Description
When a SELECT query retrieves data from a column with the ZEROFILL attribute, the formatting is correctly applied in the result set. However, when the same column is used within a query involving a set operator like INTERSECT, the ZEROFILL formatting attribute is lost in the final result set. This creates an inconsistency in behavior, where the presentation of data depends on whether a set operator is used, which can lead to unexpected results for users and applications.
Minimal reproduce step (Required)
DROP DATABASE IF EXISTS database61; |
CREATE DATABASE database61; |
USE database61; |
CREATE TABLE t0(c0 INT ZEROFILL NOT NULL ); |
|
|
CREATE TABLE t1 LIKE t0; |
REPLACE INTO t1 VALUES (false), (false), (true); |
INSERT INTO t0(c0) VALUES (false) ON DUPLICATE KEY UPDATE c0=t0.c0; |
REPLACE INTO t0 VALUES (false); |
REPLACE INTO t1(c0) VALUES (true); |
INSERT INTO t0 VALUES (false), (false), (false); |
REPLACE INTO t1(c0) VALUES (false); |
REPLACE INTO t1(c0) VALUES (false); |
REPLACE INTO t0(c0) VALUES (false); |
INSERT INTO t1 VALUES (false), (false) ON DUPLICATE KEY UPDATE c0=(~ ('YsISRq')); |
INSERT IGNORE INTO t0 VALUES (false), (NULL); |
|
|
INSERT IGNORE INTO t1(c0) VALUES (false); |
REPLACE INTO t1(c0) VALUES (true); |
|
|
UPDATE t0 SET c0=0.9256709693178293; |
INSERT IGNORE INTO t1(c0) VALUES (true); |
REPLACE INTO t1 VALUES (true), (true), (true); |
INSERT IGNORE INTO t1 VALUES (true); |
|
|
REPLACE INTO t0 VALUES (false); |
REPLACE INTO t1 VALUES (true); |
INSERT INTO t1(c0) VALUES (true) ON DUPLICATE KEY UPDATE c0=t1.c0; |
|
|
|
|
INSERT IGNORE INTO t0(c0) VALUES (false) ON DUPLICATE KEY UPDATE c0=t0.c0; |
|
|
INSERT IGNORE INTO t1(c0) VALUES (false), (true) ON DUPLICATE KEY UPDATE c0=((t1.c0)OR((CASE t1.c0 WHEN 'z.' THEN t1.c0 ELSE 'e' END ))); |
REPLACE INTO t1(c0) VALUES (false); |
UPDATE t1 SET c0=t1.c0 WHERE 0.19610550901368273; |
INSERT IGNORE INTO t0(c0) VALUES (true); |
REPLACE INTO t0 VALUES (true); |
UPDATE t0 SET c0=0.2800239623483346 WHERE (((CASE NULL WHEN false THEN -1146250208 ELSE t0.c0 END )) IS NULL); |
INSERT IGNORE INTO t0 VALUES (true); |
INSERT IGNORE INTO t1(c0) VALUES (false), (false); |
|
|
|
|
SELECT DISTINCT t1.c0 FROM t0 INNER JOIN t1 ON ((-1.757248483E9)<=(t1.c0)); |
|
|
(SELECT DISTINCT t1.c0 FROM t0 LEFT JOIN t1 ON ((-1.757248483E9)<=(t1.c0))) INTERSECT (SELECT DISTINCT t1.c0 FROM t0 RIGHT JOIN t1 ON ((-1.757248483E9)<=(t1.c0))); |
What did you expect to see? (Required)
MariaDB [database61]> SELECT DISTINCT t1.c0 FROM t0 INNER JOIN t1 ON ((-1.757248483E9)<=(t1.c0));
|
+------------+
|
| c0 |
|
+------------+
|
| 0000000000 |
|
| 0000000001 |
|
+------------+
|
2 rows in set (0.001 sec)
|
|
|
MariaDB [database61]> (SELECT DISTINCT t1.c0 FROM t0 LEFT JOIN t1 ON ((-1.757248483E9)<=(t1.c0))) INTERSECT (SELECT DISTINCT t1.c0 FROM t0 RIGHT JOIN t1 ON ((-1.757248483E9)<=(t1.c0)));
|
+------+
|
| c0 |
|
+------+
|
| 0000000000 |
|
| 0000000001 |
|
+------+
|
2 rows in set (0.001 sec)
|
|
|
*What did you see instead (Required)*
|
MariaDB [database61]> SELECT DISTINCT t1.c0 FROM t0 INNER JOIN t1 ON ((-1.757248483E9)<=(t1.c0));
|
+------------+
|
| c0 |
|
+------------+
|
| 0000000000 |
|
| 0000000001 |
|
+------------+
|
2 rows in set (0.001 sec)
|
|
|
MariaDB [database61]> (SELECT DISTINCT t1.c0 FROM t0 LEFT JOIN t1 ON ((-1.757248483E9)<=(t1.c0))) INTERSECT (SELECT DISTINCT t1.c0 FROM t0 RIGHT JOIN t1 ON ((-1.757248483E9)<=(t1.c0)));
|
+------+
|
| c0 |
|
+------+
|
| 0 |
|
| 1 |
|
+------+
|
2 rows in set (0.001 sec)
|