| Bug #96040 | behavior of wndow function with range | ||
|---|---|---|---|
| Submitted: | 1 Jul 2019 2:28 | Modified: | 5 Jul 2019 17:16 |
| Reporter: | Feng Liyuan | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[1 Jul 2019 2:28]
Feng Liyuan
[3 Jul 2019 7:24]
Feng Liyuan
I tested on Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 and it got same result what I expect to see. SQL> SELECT FIRST_VALUE(col_varchar) OVER (ORDER BY col_time_key ASC NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), LAST_VALUE(col_varchar) OVER (ORDER BY col_time_key ASC NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), col_varchar, col_time_key FROM table20_int_autoinc; F L C COL_TIME_KEY - - - --------------------------------------------------------------------------- d z d d z o z 01-JAN-70 12.20.01 AM k z k 01-JAN-70 12.20.02 AM f z f 01-JAN-70 12.20.03 AM z 01-JAN-70 12.20.04 AM a z a 01-JAN-70 12.20.05 AM a z s 01-JAN-70 12.20.05 AM t z t 01-JAN-70 12.20.06 AM o z o 01-JAN-70 12.20.09 AM f z f 01-JAN-70 05.26.35 AM F L C COL_TIME_KEY - - - --------------------------------------------------------------------------- i z i 01-JAN-70 07.01.51 AM k z k 01-JAN-70 08.21.06 AM z 01-JAN-70 01.43.48 PM t z t 01-JAN-70 03.21.01 PM h z h 01-JAN-70 08.40.42 PM w z w 01-JAN-70 09.10.13 PM i z i 01-JAN-70 10.03.46 PM u z u 01-JAN-70 10.35.41 PM z z z 01-JAN-70 11.59.32 PM 20 rows selected. How to repeat： CREATE TABLE table20_int_autoinc ( col_int number(10) DEFAULT NULL, col_int_key number(10) DEFAULT NULL, col_time_key timestamp(0) DEFAULT NULL, col_date_key date DEFAULT NULL, pk number(10) NOT NULL, col_varchar varchar2(1) DEFAULT NULL, col_datetime timestamp(0) DEFAULT NULL, col_date date DEFAULT NULL, col_time timestamp(0) DEFAULT NULL, col_varchar_key varchar2(1) DEFAULT NULL, col_datetime_key timestamp(0) DEFAULT NULL, PRIMARY KEY (pk) ) ; -- Generate ID using sequence and trigger CREATE SEQUENCE table20_int_autoinc_seq START WITH 21 INCREMENT BY 1; CREATE OR REPLACE TRIGGER table20_int_autoinc_seq_tr BEFORE INSERT ON table20_int_autoinc FOR EACH ROW WHEN (NEW.pk IS NULL) BEGIN SELECT table20_int_autoinc_seq.NEXTVAL INTO :NEW.pk FROM DUAL; END; / CREATE INDEX col_int_key ON table20_int_autoinc (col_int_key); CREATE INDEX col_time_key ON table20_int_autoinc (col_time_key); CREATE INDEX col_date_key ON table20_int_autoinc (col_date_key); CREATE INDEX col_varchar_key ON table20_int_autoinc (col_varchar_key); CREATE INDEX col_datetime_key ON table20_int_autoinc (col_datetime_key); INSERT INTO table20_int_autoinc VALUES (-1,0,TIMESTAMP '1970-01-01 00:20:05',DATE '2000-05-06',1,'a',TIMESTAMP '1970-01-01 00:00:00',DATE '1970-01-01', TIMESTAMP '1970-01-01 00:20:01','k',TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (13,19321, TIMESTAMP '1970-01-01 20:40:42',DATE '1970-01-01',2,'h',TIMESTAMP '2005-02-15 00:00:00',DATE '2000-08-22',TIMESTAMP '1970-01-01 10:54:06','p',TIMESTAMP '2003-05-13 00:00:00'); INSERT INTO table20_int_autoinc VALUES (0,-111, TIMESTAMP '1970-01-01 07:01:51',DATE '1970-01-01',3,'i',TIMESTAMP '2004-11-15 00:00:00', DATE '2007-08-24', TIMESTAMP '1970-01-01 15:54:11','d', TIMESTAMP '2004-12-17 11:22:46'); INSERT INTO table20_int_autoinc VALUES (0,NULL, TIMESTAMP '1970-01-01 00:20:04',DATE '1970-01-01',4,NULL,TIMESTAMP '2007-06-07 00:00:00', DATE '2002-10-06',TIMESTAMP '1970-01-01 01:17:01','n',TIMESTAMP '2000-01-06 20:32:48'); INSERT INTO table20_int_autoinc VALUES (1,-1, TIMESTAMP '1970-01-01 00:20:01',DATE '1970-01-01',5,NULL,TIMESTAMP '1970-01-01 00:00:00', DATE '2005-07-03',TIMESTAMP '1970-01-01 17:54:01','r', TIMESTAMP '1970-01-01 00:00:00' ); INSERT INTO table20_int_autoinc VALUES (-2077,-1, TIMESTAMP '1970-01-01 08:21:06',DATE '2009-06-15',6,'k',TIMESTAMP '1970-01-01 00:00:00',DATE '2006-12-24',TIMESTAMP '1970-01-01 11:05:09','i', TIMESTAMP '2004-10-25 03:34:36'); INSERT INTO table20_int_autoinc VALUES (-111,0, TIMESTAMP '1970-01-01 15:21:01',DATE '2008-07-03',7,'t',NULL,DATE '2009-06-17',NULL,'o', TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (-1,-111,NULL,DATE '1970-01-01',8,'d',TIMESTAMP '2008-08-13 00:00:00',DATE '2003-05-20',TIMESTAMP '1970-01-01 00:20:08','s', TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (1,0,NULL,NULL,9,'o',TIMESTAMP '2001-02-16 21:26:55',DATE '1970-01-01',NULL,'f', TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (-1,0, TIMESTAMP '1970-01-01 22:03:46',NULL,10,'i',TIMESTAMP '2007-08-28 12:39:18',DATE '2005-10-10',TIMESTAMP '1970-01-01 19:15:38','v', TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (4896,-9, TIMESTAMP '1970-01-01 00:20:05',DATE '1970-01-01',11,'s',TIMESTAMP '1970-01-01 00:00:00',DATE '2002-11-07',TIMESTAMP '1970-01-01 23:06:47','h', TIMESTAMP '2008-01-17 00:00:00'); INSERT INTO table20_int_autoinc VALUES (-9,0, TIMESTAMP '1970-01-01 21:10:13',DATE '2006-10-24',12,'w',TIMESTAMP '1970-01-01 00:00:00',DATE '2001-07-05',TIMESTAMP '1970-01-01 00:20:02',NULL,NULL); INSERT INTO table20_int_autoinc VALUES (-9,0, TIMESTAMP '1970-01-01 00:20:09',DATE '2001-08-20',13,'o',TIMESTAMP '2001-08-18 00:00:00',DATE '1970-01-01',TIMESTAMP '1970-01-01 14:59:11','s', TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (27445,1, TIMESTAMP '1970-01-01 05:26:35',DATE '2001-04-12',14,'f',NULL,DATE '2005-02-09',TIMESTAMP '1970-01-01 08:16:34','u', TIMESTAMP '2003-05-23 15:42:00'); INSERT INTO table20_int_autoinc VALUES (13,-1, TIMESTAMP '1970-01-01 00:20:03',DATE '2007-03-14',15,'f',TIMESTAMP '2004-08-04 16:15:49',DATE '1970-01-01',TIMESTAMP '1970-01-01 10:52:19','o', TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (1,1, TIMESTAMP '1970-01-01 13:43:48',DATE '2005-12-09',16,NULL,TIMESTAMP '2008-11-14 01:59:25',NULL,TIMESTAMP '1970-01-01 11:17:56','i', TIMESTAMP '2009-08-03 00:00:00'); INSERT INTO table20_int_autoinc VALUES (1,13, TIMESTAMP '1970-01-01 00:20:06',DATE '2004-04-06',17,'t',TIMESTAMP '1970-01-01 00:00:00',DATE '2006-06-26',TIMESTAMP '1970-01-01 00:20:03',NULL, TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (0,-9, TIMESTAMP '1970-01-01 22:35:41',DATE '1970-01-01',18,'u',TIMESTAMP '2009-12-18 18:49:11',DATE '2006-01-18',NULL,'d', TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (0,1, TIMESTAMP '1970-01-01 23:59:32',DATE '2004-11-22',19,'z',TIMESTAMP '2009-12-15 17:47:16',DATE '2006-11-03',TIMESTAMP '1970-01-01 16:02:26','s', TIMESTAMP '1970-01-01 00:00:00'); INSERT INTO table20_int_autoinc VALUES (-10745,81, TIMESTAMP '1970-01-01 00:20:02',DATE '2007-11-14',20,'k',TIMESTAMP '1970-01-01 00:00:00',DATE '2008-03-03',NULL,NULL, TIMESTAMP '1970-01-01 00:00:00');
[3 Jul 2019 13:31]
MySQL Verification Team
Hi Mr. Liyuan, Thank you for your bug report. However, I can not reproduce it. You have sent us a test case for Oracle's DB, not for MySQL database. You are using column types and domains that we do not support and you are using sequences, which we do not support yet ... Types , domains and other features that we do support are described in our Reference Manual. Can't repeat.
[4 Jul 2019 2:36]
Feng Liyuan
Hi, Thank you for your reply. Please see my first post in this thread, it's a mysql problem and I described how to repeat. my second post is to demonstrate that how the ORACLE database works with similar data and similar SQL query. I'm sorry for misleading you.
[4 Jul 2019 13:04]
MySQL Verification Team
Hi, Still can not repeat. This is because your test case for MySQL is useless. The order of values in INSERT must correspond 100 % to the order of the column types and domains, as the one in the CREATE statement.
[5 Jul 2019 11:17]
Feng Liyuan
Sorry, I've update my `how to repeat` below, please take a look again. How to repeat: CREATE TABLE `table20_int_autoinc` ( `col_int` int(11) DEFAULT NULL, `col_int_key` int(11) DEFAULT NULL, `col_time_key` time DEFAULT NULL, `col_date_key` date DEFAULT NULL, `pk` int(11) NOT NULL AUTO_INCREMENT, `col_varchar` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `col_datetime` datetime DEFAULT NULL, `col_date` date DEFAULT NULL, `col_time` time DEFAULT NULL, `col_varchar_key` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `col_datetime_key` datetime DEFAULT NULL, PRIMARY KEY (`pk`), KEY `col_int_key` (`col_int_key`), KEY `col_time_key` (`col_time_key`), KEY `col_date_key` (`col_date_key`), KEY `col_varchar_key` (`col_varchar_key`), KEY `col_datetime_key` (`col_datetime_key`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO `table20_int_autoinc` VALUES (-1,0,'00:20:05','2000-05-06',1,'a','1970-01-01 00:00:00','1970-01-01','00:20:01','k','1970-01-01 00:00:00'),(13,19321,'20:40:42','1970-01-01',2,'h','2005-02-15 00:00:00','2000-08-22','10:54:06','p','2003-05-13 00:00:00'),(0,-111,'07:01:51','1970-01-01',3,'i','2004-11-15 00:00:00','2007-08-24','15:54:11','d','2004-12-17 11:22:46'),(0,NULL,'00:20:04','1970-01-01',4,NULL,'2007-06-07 00:00:00','2002-10-06','01:17:01','n','2000-01-06 20:32:48'),(1,-1,'00:20:01','1970-01-01',5,NULL,'1970-01-01 00:00:00','2005-07-03','17:54:01','r','1970-01-01 00:00:00'),(-2077,-1,'08:21:06','2009-06-15',6,'k','1970-01-01 00:00:00','2006-12-24','11:05:09','i','2004-10-25 03:34:36'),(-111,0,'15:21:01','2008-07-03',7,'t',NULL,'2009-06-17',NULL,'o','1970-01-01 00:00:00'),(-1,-111,NULL,'1970-01-01',8,'d','2008-08-13 00:00:00','2003-05-20','00:20:08','s','1970-01-01 00:00:00'),(1,0,NULL,NULL,9,'o','2001-02-16 21:26:55','1970-01-01',NULL,'f','1970-01-01 00:00:00'),(-1,0,'22:03:46',NULL,10,'i','2007-08-28 12:39:18','2005-10-10','19:15:38','v','1970-01-01 00:00:00'),(4896,-9,'00:20:05','1970-01-01',11,'s','1970-01-01 00:00:00','2002-11-07','23:06:47','h','2008-01-17 00:00:00'),(-9,0,'21:10:13','2006-10-24',12,'w','1970-01-01 00:00:00','2001-07-05','00:20:02',NULL,NULL),(-9,0,'00:20:09','2001-08-20',13,'o','2001-08-18 00:00:00','1970-01-01','14:59:11','s','1970-01-01 00:00:00'),(27445,1,'05:26:35','2001-04-12',14,'f',NULL,'2005-02-09','08:16:34','u','2003-05-23 15:42:00'),(13,-1,'00:20:03','2007-03-14',15,'f','2004-08-04 16:15:49','1970-01-01','10:52:19','o','1970-01-01 00:00:00'),(1,1,'13:43:48','2005-12-09',16,NULL,'2008-11-14 01:59:25',NULL,'11:17:56','i','2009-08-03 00:00:00'),(1,13,'00:20:06','2004-04-06',17,'t','1970-01-01 00:00:00','2006-06-26','00:20:03',NULL,'1970-01-01 00:00:00'),(0,-9,'22:35:41','1970-01-01',18,'u','2009-12-18 18:49:11','2006-01-18',NULL,'d','1970-01-01 00:00:00'),(0,1,'23:59:32','2004-11-22',19,'z','2009-12-15 17:47:16','2006-11-03','16:02:26','s','1970-01-01 00:00:00'),(-10745,81,'00:20:02','2007-11-14',20,'k','1970-01-01 00:00:00','2008-03-03',NULL,NULL,'1970-01-01 00:00:00');
[5 Jul 2019 13:00]
MySQL Verification Team
Hi, Still can't repeat .... This time, table got uploaded, but the query has a syntax error here: (ORDER BY `col_time_key` ASC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Even when you send us a corrected query, you will have to explain, in high detail, why do you think that your translation from Oracle's syntax is 100 % correct for MySQL syntax !!!!!
[5 Jul 2019 17:16]
Feng Liyuan
I tested on my macbook and mysql 8.0.16, did you test with the same version?
[8 Jul 2019 12:31]
MySQL Verification Team
Hi, I have tested your query on macOS Mojave with MySQL 8.0.16. These are the results that I have got: +-------------+------------+-------------+--------------+ | first_value | last_value | col_varchar | col_time_key | +-------------+------------+-------------+--------------+ | d | z | d | NULL | | d | z | o | NULL | | d | z | NULL | 00:20:01 | | d | z | k | 00:20:02 | | d | z | f | 00:20:03 | | d | z | NULL | 00:20:04 | | d | z | a | 00:20:05 | | d | z | s | 00:20:05 | | d | z | t | 00:20:06 | | d | z | o | 00:20:09 | | d | z | f | 05:26:35 | | d | z | i | 07:01:51 | | d | z | k | 08:21:06 | | d | z | NULL | 13:43:48 | | d | z | t | 15:21:01 | | d | z | h | 20:40:42 | | d | z | w | 21:10:13 | | d | z | i | 22:03:46 | | d | z | u | 22:35:41 | | d | z | z | 23:59:32 | +-------------+------------+-------------+--------------+ Now, please explain to us in detail why are these results wrong, please .....
