Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL)
-
None
Description
I create this stored function which changes the session time zone:
SET sql_mode=DEFAULT; |
DELIMITER $$
|
CREATE OR REPLACE FUNCTION f1(tz TEXT) RETURNS TEXT |
BEGIN
|
SET time_zone=tz; |
RETURN @@time_zone; |
END; |
$$
|
DELIMITER ;
|
SELECT f1('+00:00'); |
Now I execute this anonymous code block
(notice, it changes the session time_zone in the middle of the CREATE..SELECT query by using f1 in the select list):
SET sql_mode=DEFAULT; |
DELIMITER $$
|
BEGIN NOT ATOMIC |
DECLARE va TIMESTAMP; |
SET time_zone='+00:00'; |
SET va= FROM_UNIXTIME(1288477526); |
SELECT @@time_zone, UNIX_TIMESTAMP(va), va, CONVERT_TZ(va,'+00:00','Europe/Moscow'); |
EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT ? AS a11,f1(''Europe/Moscow'') AS tz12, ? AS a12' USING va, va; |
SELECT @@time_zone, UNIX_TIMESTAMP(a11), a11, UNIX_TIMESTAMP(a12), a12 FROM t1; |
END; |
$$
|
DELIMITER ;
|
It returns these two result sets:
+-------------+--------------------+---------------------+-----------------------------------------+
|
| @@time_zone | UNIX_TIMESTAMP(va) | va | CONVERT_TZ(va,'+00:00','Europe/Moscow') |
|
+-------------+--------------------+---------------------+-----------------------------------------+
|
| +00:00 | 1288477526 | 2010-10-30 22:25:26 | 2010-10-31 02:25:26 |
|
+-------------+--------------------+---------------------+-----------------------------------------+
|
1 row in set (0.00 sec)
|
+---------------+---------------------+---------------------+---------------------+---------------------+
|
| @@time_zone | UNIX_TIMESTAMP(a11) | a11 | UNIX_TIMESTAMP(a12) | a12 |
|
+---------------+---------------------+---------------------+---------------------+---------------------+
|
| Europe/Moscow | 1288477526 | 2010-10-31 02:25:26 | 1288463126 | 2010-10-30 22:25:26 |
|
+---------------+---------------------+---------------------+---------------------+---------------------+
|
1 row in set (0.04 sec)
|
Notice, in the second result set the values in UNIX_TIMESTAMP(a11) and a11 are correct:
- The timestamp is equal to the original timestamp value in va
- The datetime representation is equal to CONVERT_TZ() in the first result set
The values in UNIX_TIMESTAMP(a12) and a12 are wrong:
- The timestamp value 1288463126 is not equal to the original value 1288477526. This is wrong. It should preserve the original value. This is the TIMESTAMP data type!
- The value in a12 should be 2010-10-31 02:25:26, similar to a11. Instead, it preserved the DATETIME representation of va at bind time.
Attachments
Issue Links
- relates to
-
MDEV-10709
Expressions as parameters to Dynamic SQL
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Open
-