Bug #116891 Inconsistent Results Between Materialized Table and View with DAYNAME

• Submitted: 6 Dec 7:56
• Modified: 6 Dec 11:30
• Reporter: Wenqian Deng
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.0
• OS: Any
• CPU Architecture: Any

[6 Dec 7:56] Wenqian Deng

Description:
In MySQL, a query involving the DAYNAME function and complex logical conditions produces inconsistent results when executed on a materialized table versus a view with the same definition. 

How to repeat:
1. Set up table t0:

CREATE TABLE t0 (c0 TIMESTAMP, c1 TIMESTAMP, c2 MEDIUMTEXT);
INSERT INTO t0 (c0, c1, c2) VALUES ('1982-11-10 14:03:07', '2030-12-20 15:28:14', 'Lisk14IvUNsTDJnIlne5KGYEZLj4mdYjDA7UjPqS9ilTsXjXFHigba4XNiQecQourfXZ9ZlELPESl8DhswZJvnbRa1MuouMyJlPDEMSNjPs');
INSERT INTO t0 (c0, c1, c2) VALUES ('1999-10-02 13:51:19', '2023-03-07 00:08:11', 'DpuYlxiqBAZ67wiVgQRX3yTEKKYnH0v9WdxHveNsaTibGeWC3yI768i2lOxmbb1NVyb6Cmb4lNa94eSBJ1E51UVuJUwMPuMxbPrQlrOyaaNrUoYwUHRztDA54C9nZyRRQ4xckAxLl5FuD1rxdfjunrvcahQRhBl5SAwZSmdsYIsLERO');
INSERT INTO t0 (c0, c1, c2) VALUES ('1980-09-13 04:04:39', '1998-12-10 05:32:57', 'Z0ueiqSwHKWbQE0kLNUJBvwEHFSoK8eFfyZTjSOEskP');
INSERT INTO t0 (c0, c1, c2) VALUES ('2004-09-02 04:51:17', '1990-05-14 20:03:06', 'AtaKwz8YE6tWGjpC5izmDQ19b76CR39v4ZOvZn9NWHW1IjGkNB8m7bHNXYid1');
INSERT INTO t0 (c0, c1, c2) VALUES ('2006-03-06 06:18:34', '2013-10-07 02:38:56', 'nK6HT3yeQqhefFvG5JH6lyrWCpKFgmv37ilDLYwgkBXSl033Yp51XCEg4DKZdyJYsCdE1YXvMaQXqyYPAjgZPIA5ATb6zIe3tO1eg49kHPiyhvL1TurybVsKBTgr9q3RcvADlC45OZWb3vDpzJ8NALFsqhd9F6w93og91');
INSERT INTO t0 (c0, c1, c2) VALUES ('2009-12-27 05:29:09', '2004-06-27 05:41:45', 'QVNvBy6IdJmsGPgpcKVPBXhUqvJ14Jkn26dnSR15cVRsKckh5F8HG8wDFsfsZ9qYbPX2Ge7tGja1gq4Os8');
INSERT INTO t0 (c0, c1, c2) VALUES ('2034-08-09 05:12:25', '1978-12-18 03:45:42', 'LU8XwyZeNvwG1mDxcy6T0qVyAdHxdRE7duL7loBH0Fx40qvsphzl39rsBPqA6zjHWZnty1jL7EtnNwclFOQUw4mN3tG7q56Wgy4DGKivHZxEPD6Kbs4Un75mg9XjwMZHMsJU5OxUUQw89M9F8efu3XUeoIyxMeAcAe7a9LvmazlSnqQ1cxwIS5rpbiI7P4KCsK0G0V4IY');

2. Create materialized table t1 and query it:
CREATE TABLE t1 AS (SELECT (DAYNAME(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 );
SELECT c1, (c0), c2 FROM t1 WHERE (CASE WHEN ((c2 > 'qp0Hed9uw3') OR ((c0))) THEN (('2035-05-13 22:43:09')) ELSE (('Ao5S71AJCSh0vIzFE0IiH247eZtlQDUJqvw8uFXNd2jplRO6ygE9jUkCwG4NodbPOZmGtaKdXwN5kC8JJ8SpczkYn74k1ueEh9s2dTHQMbAagrCK6mtJfvvrCEXD44msa3s')) END) ORDER BY (c0) DESC, c1 ASC;
+---------------------+----------+------------------------------------------------------------------------------------+
| c1                  | c0       | c2                                                                                 |
+---------------------+----------+------------------------------------------------------------------------------------+
| 2004-06-27 05:41:45 | Sunday   | QVNvBy6IdJmsGPgpcKVPBXhUqvJ14Jkn26dnSR15cVRsKckh5F8HG8wDFsfsZ9qYbPX2Ge7tGja1gq4Os8 |
| 1998-12-10 05:32:57 | Saturday | Z0ueiqSwHKWbQE0kLNUJBvwEHFSoK8eFfyZTjSOEskP                                        |
+---------------------+----------+------------------------------------------------------------------------------------+
2 rows in set, 12 warnings (0.00 sec)

3. Create view t1 instead and query it:
CREATE VIEW t1 AS (SELECT (DAYNAME(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 );
SELECT c1, (c0), c2 FROM t1 WHERE (CASE WHEN ((c2 > 'qp0Hed9uw3') OR ((c0))) THEN (('2035-05-13 22:43:09')) ELSE (('Ao5S71AJCSh0vIzFE0IiH247eZtlQDUJqvw8uFXNd2jplRO6ygE9jUkCwG4NodbPOZmGtaKdXwN5kC8JJ8SpczkYn74k1ueEh9s2dTHQMbAagrCK6mtJfvvrCEXD44msa3s')) END) ORDER BY (c0) DESC, c1 ASC;
+---------------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| c1                  | c0        | c2                                                                                                                                                                                                        |
+---------------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1978-12-18 03:45:42 | Wednesday | LU8XwyZeNvwG1mDxcy6T0qVyAdHxdRE7duL7loBH0Fx40qvsphzl39rsBPqA6zjHWZnty1jL7EtnNwclFOQUw4mN3tG7q56Wgy4DGKivHZxEPD6Kbs4Un75mg9XjwMZHMsJU5OxUUQw89M9F8efu3XUeoIyxMeAcAe7a9LvmazlSnqQ1cxwIS5rpbiI7P4KCsK0G0V4IY |
| 2030-12-20 15:28:14 | Wednesday | Lisk14IvUNsTDJnIlne5KGYEZLj4mdYjDA7UjPqS9ilTsXjXFHigba4XNiQecQourfXZ9ZlELPESl8DhswZJvnbRa1MuouMyJlPDEMSNjPs                                                                                               |
| 1990-05-14 20:03:06 | Thursday  | AtaKwz8YE6tWGjpC5izmDQ19b76CR39v4ZOvZn9NWHW1IjGkNB8m7bHNXYid1                                                                                                                                             |
| 2004-06-27 05:41:45 | Sunday    | QVNvBy6IdJmsGPgpcKVPBXhUqvJ14Jkn26dnSR15cVRsKckh5F8HG8wDFsfsZ9qYbPX2Ge7tGja1gq4Os8                                                                                                                        |
| 1998-12-10 05:32:57 | Saturday  | Z0ueiqSwHKWbQE0kLNUJBvwEHFSoK8eFfyZTjSOEskP                                                                                                                                                               |
| 2023-03-07 00:08:11 | Saturday  | DpuYlxiqBAZ67wiVgQRX3yTEKKYnH0v9WdxHveNsaTibGeWC3yI768i2lOxmbb1NVyb6Cmb4lNa94eSBJ1E51UVuJUwMPuMxbPrQlrOyaaNrUoYwUHRztDA54C9nZyRRQ4xckAxLl5FuD1rxdfjunrvcahQRhBl5SAwZSmdsYIsLERO                           |
+---------------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set, 7 warnings (0.00 sec)

Expected Behavior:

Both the materialized table and the view should return consistent results based on the query conditions.

[6 Dec 11:30] MySQL Verification Team

Hi Mr. Deng,

Thank you for your bug report.

We have fully repeated your bug report.

However, by analysing the code that does datatype conversions we concluded that this report is actually a duplicate of the bug #116890, also reported by you. Data types are different, but it is about the same part of the code that does data type conversions and materialisations .......

Duplicate.