[MDEV-30284] Wrong result on 2nd execution of PS - Jira

XML

Word

Printable

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6
Component/s: Prepared Statements
Labels:
None

Description

See a decent test case in the comments, below is just a raw initial version.

CREATE /* QNO 0 TID 0 */ TABLE D (

              pk INTEGER AUTO_INCREMENT,

              col_int_nokey INTEGER,

              col_int_key INTEGER AS (col_int_nokey * 2) STORED,

              col_date_key DATE AS (DATE_SUB(col_date_nokey, INTERVAL 1 DAY)) STORED,

              col_date_nokey DATE,

              col_time_key TIME AS (TIME(col_time_nokey)) STORED,

              col_time_nokey TIME,

              col_datetime_key DATETIME AS (DATE_ADD(col_datetime_nokey, INTERVAL 1 HOUR)) STORED,

              col_datetime_nokey DATETIME,

              col_varchar_key VARCHAR(1) AS (CONCAT('virt-',col_varchar_nokey)) STORED,

              col_varchar_nokey VARCHAR(1),

              PRIMARY KEY (pk DESC),

              KEY (col_int_key),

              KEY (col_date_key DESC),

              KEY (col_time_key DESC),

              KEY (col_datetime_key DESC),

              KEY (col_varchar_key DESC, col_int_key ASC)

          )  ENGINE=Aria;

INSERT /* QNO 0 TID 0 */ /*! IGNORE */ INTO D (

          col_int_key, col_int_nokey,

          col_date_key, col_date_nokey,

          col_time_key, col_time_nokey,

          col_datetime_key, col_datetime_nokey,

          col_varchar_key, col_varchar_nokey

        ) VALUES (6, 4, NULL, NULL, '07:07:26.046704', '07:07:26.046704', '2026-05-10 16:44:18.025457', '2026-05-10 16:44:18.025457', 'm', 'm'),(7, NULL, NULL, NULL, '05:29:16.056724', '05:29:16.056724', '1913-04-09 08:09:37.063969', '1913-04-09 08:09:37.063969', 'm', 'm'),(9, 8, '2002-11-15', '2002-11-15', '11:34:20.000844', '11:34:20.000844', '1910-04-06 00:00:00', '1910-04-06 00:00:00', 'u', 'u'),(NULL, 3, '1973-12-22', '1973-12-22', '17:18:19.013790', '17:18:19.013790', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'g', 'g'),(0, 2, NULL, NULL, '10:53:56.020747', '10:53:56.020747', '1951-05-16 00:00:00', '1951-05-16 00:00:00', 'b', 'b'),(2, 9, '1900-01-01', '1900-01-01', '04:41:30.051312', '04:41:30.051312', '1968-02-06 19:38:33.002622', '1968-02-06 19:38:33.002622', 'e', 'e'),(4, NULL, '1971-10-26', '1971-10-26', '15:49:49.005493', '15:49:49.005493', '1947-11-28 00:00:00', '1947-11-28 00:00:00', 't', 't'),(2, 2, '1925-07-04', '1925-07-04', '20:57:21.040074', '20:57:21.040074', '1992-12-22 07:53:15.062706', '1992-12-22 07:53:15.062706', 'l', 'l'),(9, NULL, NULL, NULL, '02:22:00.031673', '02:22:00.031673', '1965-11-06 16:30:13.001354', '1965-11-06 16:30:13.001354', 'z', 'z'),(3, 5, '1926-05-25', '1926-05-25', '19:39:28.016091', '19:39:28.016091', '2035-01-29 00:00:00', '2035-01-29 00:00:00', 'r', 'r'),(9, 5, '2021-10-25', '2021-10-25', '12:23:03.008332', '12:23:03.008332', NULL, NULL, 't', 't'),(NULL, 4, NULL, NULL, '04:08:27.042496', '04:08:27.042496', '2005-07-02 19:46:00.014447', '2005-07-02 19:46:00.014447', 't', 't'),(8, 5, '1925-07-20', '1925-07-20', NULL, NULL, '2005-03-07 15:28:31.020044', '2005-03-07 15:28:31.020044', 'o', 'o'),(5, 0, '1907-10-15', '1907-10-15', '17:21:48.062882', '17:21:48.062882', '1946-01-12 17:22:32.002899', '1946-01-12 17:22:32.002899', 'c', 'c'),(7, 4, NULL, NULL, '23:31:19.047799', '23:31:19.047799', '1966-06-11 20:55:45.064378', '1966-06-11 20:55:45.064378', 'f', 'f'),(0, 7, '2021-09-23', '2021-09-23', NULL, NULL, '2005-04-23 09:44:16.064451', '2005-04-23 09:44:16.064451', 'v', 'v'),(5, 7, '1910-03-07', '1910-03-07', '01:59:15.043279', '01:59:15.043279', '1922-06-20 03:30:24.041464', '1922-06-20 03:30:24.041464', 'o', 'o'),(2, 9, '1929-02-22', '1929-02-22', '01:50:29.027984', '01:50:29.027984', '2026-10-19 18:00:22.042463', '2026-10-19 18:00:22.042463', 'n', 'n'),(6, 8, '1955-05-12', '1955-05-12', '16:59:12.006287', '16:59:12.006287', '1977-09-23 23:51:59.017394', '1977-09-23 23:51:59.017394', 'o', 'o'),(81, 45, '1917-11-22', '1917-11-22', '06:40:07.035308', '06:40:07.035308', '1975-07-24 02:14:33.004507', '1975-07-24 02:14:33.004507', 'd', 'd'),(3, 4, '1946-04-10', '1946-04-10', '00:00:00', '00:00:00', '1966-06-22 03:32:05.015081', '1966-06-22 03:32:05.015081', 'x', 'x'),(NULL, 1, '1925-02-07', '1925-02-07', '04:14:34.024308', '04:14:34.024308', '1988-06-21 00:00:00', '1988-06-21 00:00:00', 'f', 'f'),(0, 3, NULL, NULL, '01:56:47.046685', '01:56:47.046685', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'y', 'y'),(0, 2, NULL, NULL, '00:00:00', '00:00:00', '1954-02-26 00:00:00', '1954-02-26 00:00:00', 'b', 'b'),(169, 114, '1900-01-01', '1900-01-01', '05:18:06.039871', '05:18:06.039871', '1987-08-20 22:14:51.034946', '1987-08-20 22:14:51.034946', 'r', 'r'),(9, 4, '1953-05-26', '1953-05-26', '11:03:49.021296', '11:03:49.021296', '2024-12-18 11:20:24.002709', '2024-12-18 11:20:24.002709', 'w', 'w'),(7, NULL, '1977-09-09', '1977-09-09', '01:06:40.061600', '01:06:40.061600', NULL, NULL, 'k', 'k'),(0, 2, '1900-01-01', '1900-01-01', '01:35:01.022929', '01:35:01.022929', '2022-05-08 08:26:57.006487', '2022-05-08 08:26:57.006487', 'e', 'e'),(NULL, 9, '1946-07-27', '1946-07-27', '00:00:00', '00:00:00', '1988-03-04 06:32:58.018905', '1988-03-04 06:32:58.018905', 't', 't'),(8, 4, '1900-12-25', '1900-12-25', '14:01:14.003177', '14:01:14.003177', NULL, NULL, 'u', 'u'),(4, 2, '1907-01-12', '1907-01-12', '23:23:56.013748', '23:23:56.013748', '1906-07-07 00:00:00', '1906-07-07 00:00:00', 'p', 'p'),(7, 0, '2006-03-18', '2006-03-18', '08:04:22.008480', '08:04:22.008480', '1908-08-14 00:00:00', '1908-08-14 00:00:00', 'v', 'v'),(3, 3, '1907-08-05', '1907-08-05', '03:16:59.011059', '03:16:59.011059', '1936-09-30 11:19:17.030690', '1936-09-30 11:19:17.030690', 'n', 'n'),(1, 3, '1968-11-21', '1968-11-21', '16:15:36.064643', '16:15:36.064643', '1999-07-13 22:55:15.020476', '1999-07-13 22:55:15.020476', 'o', 'o'),(4, 1, '2001-10-21', '2001-10-21', '11:56:39.049493', '11:56:39.049493', '2024-03-29 14:11:18.007330', '2024-03-29 14:11:18.007330', 'x', 'x'),(2, 7, NULL, NULL, '08:15:06.047278', '08:15:06.047278', '1962-08-15 20:58:09.007907', '1962-08-15 20:58:09.007907', 'o', 'o'),(1, 6, '2036-01-06', '2036-01-06', '21:46:35.062194', '21:46:35.062194', '2034-02-09 23:01:11.021071', '2034-02-09 23:01:11.021071', 'k', 'k'),(2, 7, '1940-09-30', '1940-09-30', '08:40:19.038902', '08:40:19.038902', '1912-01-12 17:46:56.049742', '1912-01-12 17:46:56.049742', 'e', 'e'),(0, 0, '1994-06-09', '1994-06-09', '22:41:42.035996', '22:41:42.035996', NULL, NULL, 'v', 'v'),(7, 5, '1991-06-28', '1991-06-28', NULL, NULL, '2006-06-26 07:51:05.009572', '2006-06-26 07:51:05.009572', 'o', 'o'),(3, 4, '1976-09-17', '1976-09-17', '03:11:25.026524', '03:11:25.026524', NULL, NULL, 'i', 'i'),(9, 8, '1937-12-17', '1937-12-17', '21:01:15.013385', '21:01:15.013385', '1926-03-02 19:04:37.008131', '1926-03-02 19:04:37.008131', 'v', 'v'),(87, 28, '1948-01-08', '1948-01-08', '12:24:51.034033', '12:24:51.034033', '1933-03-11 22:28:05.049966', '1933-03-11 22:28:05.049966', 'x', 'x'),(9, 1, '1946-05-26', '1946-05-26', '21:40:07.049685', '21:40:07.049685', '1994-05-15 01:55:47.043139', '1994-05-15 01:55:47.043139', 'x', 'x'),(4, 1, '1961-08-15', '1961-08-15', '00:00:00', '00:00:00', '1924-10-15 09:04:57.005550', '1924-10-15 09:04:57.005550', 'm', 'm'),(106, NULL, '1948-12-09', '1948-12-09', '11:21:46.006284', '11:21:46.006284', '1931-03-04 22:28:59.031892', '1931-03-04 22:28:59.031892', 's', 's'),(NULL, NULL, '1901-05-30', '1901-05-30', '14:07:33.047116', '14:07:33.047116', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'd', 'd'),(4, 2, NULL, NULL, '19:22:42.012503', '19:22:42.012503', '1941-01-18 19:38:49.035528', '1941-01-18 19:38:49.035528', 'r', 'r'),(8, 7, '1900-01-01', '1900-01-01', '17:36:13.016415', '17:36:13.016415', '1962-07-31 20:13:40.065036', '1962-07-31 20:13:40.065036', 'p', 'p'),(3, 5, '1903-10-10', '1903-10-10', '04:30:20.038773', '04:30:20.038773', '1912-05-28 05:17:17.033017', '1912-05-28 05:17:17.033017', NULL, NULL),(9, 3, '1967-01-28', '1967-01-28', '03:57:17.055782', '03:57:17.055782', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'f', 'f'),(8, 8, '1900-01-01', '1900-01-01', '02:06:37.051311', '02:06:37.051311', '1969-12-15 13:46:01.022914', '1969-12-15 13:46:01.022914', 'h', 'h'),(3, 4, '1930-11-27', '1930-11-27', '00:00:00', '00:00:00', '1946-07-11 16:19:08.031799', '1946-07-11 16:19:08.031799', 'k', 'k'),(NULL, 9, '2035-05-08', '2035-05-08', '08:54:15.042679', '08:54:15.042679', '1987-10-21 00:00:00', '1987-10-21 00:00:00', 'k', 'k'),(1, 7, '1903-08-03', '1903-08-03', '05:15:16.058601', '05:15:16.058601', '2005-01-23 00:00:00', '2005-01-23 00:00:00', NULL, NULL),(7, 6, NULL, NULL, '09:22:24.039575', '09:22:24.039575', '1913-11-15 08:07:22.012166', '1913-11-15 08:07:22.012166', 'y', 'y'),(3, 2, '2016-03-07', '2016-03-07', '00:28:25.025653', '00:28:25.025653', '2011-06-14 08:29:06.046813', '2011-06-14 08:29:06.046813', 'n', 'n'),(4, NULL, '1935-02-22', '1935-02-22', '17:31:51.063963', '17:31:51.063963', '1955-03-26 18:39:51.035993', '1955-03-26 18:39:51.035993', 'f', 'f'),(6, 6, '2005-08-28', '2005-08-28', '05:07:56.009766', '05:07:56.009766', '1965-03-17 00:00:00', '1965-03-17 00:00:00', 'f', 'f'),(2, 2, '2019-01-24', '2019-01-24', '13:26:39.055568', '13:26:39.055568', '1901-12-20 06:34:30.002000', '1901-12-20 06:34:30.002000', 'c', 'c'),(3, 8, '2022-12-09', '2022-12-09', NULL, NULL, '1902-11-29 00:00:00', '1902-11-29 00:00:00', 'x', 'x'),(9, 8, '1960-09-12', '1960-09-12', '23:11:07.002537', '23:11:07.002537', '1982-01-30 19:09:42.053659', '1982-01-30 19:09:42.053659', 'd', 'd'),(4, 1, '1989-05-23', '1989-05-23', NULL, NULL, '1932-12-22 00:00:00', '1932-12-22 00:00:00', 'g', 'g'),(7, 9, '1965-12-15', '1965-12-15', '13:39:53.031483', '13:39:53.031483', '1917-11-26 00:00:00', '1917-11-26 00:00:00', NULL, NULL),(0, 0, '1982-12-01', '1982-12-01', '07:11:56.030714', '07:11:56.030714', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'm', 'm'),(4, 4, '1900-01-01', '1900-01-01', NULL, NULL, '1958-09-26 18:11:15.040578', '1958-09-26 18:11:15.040578', 'w', 'w'),(NULL, 0, '1982-03-04', '1982-03-04', '16:04:19.064538', '16:04:19.064538', '1977-04-09 05:31:04.062367', '1977-04-09 05:31:04.062367', 'z', 'z'),(2, 5, '2023-02-12', '2023-02-12', '20:30:34.030194', '20:30:34.030194', '1970-09-30 22:57:53.027819', '1970-09-30 22:57:53.027819', 'g', 'g'),(5, 9, '2006-02-21', '2006-02-21', '06:48:05.020520', '06:48:05.020520', '2004-07-23 20:34:01.000527', '2004-07-23 20:34:01.000527', 'k', 'k'),(6, 6, '1953-08-31', '1953-08-31', '03:17:06.051809', '03:17:06.051809', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'q', 'q'),(9, 5, '1950-12-01', '1950-12-01', '04:04:44.040352', '04:04:44.040352', '1936-01-25 19:53:02.027426', '1936-01-25 19:53:02.027426', 'y', 'y'),(33, 177, NULL, NULL, '12:16:29.063414', '12:16:29.063414', '1907-04-08 00:00:00', '1907-04-08 00:00:00', 't', 't'),(4, NULL, '1953-04-07', '1953-04-07', '00:53:47.058875', '00:53:47.058875', '1958-05-16 08:53:47.044789', '1958-05-16 08:53:47.044789', NULL, NULL),(6, 4, '1965-06-02', '1965-06-02', '18:45:12.064038', '18:45:12.064038', '1949-10-27 01:45:31.028608', '1949-10-27 01:45:31.028608', 'i', 'i'),(4, 1, '1996-06-05', '1996-06-05', '00:00:00', '00:00:00', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'i', 'i'),(2, 9, '1950-12-27', '1950-12-27', '01:56:48.038503', '01:56:48.038503', '1970-12-23 21:32:57.008825', '1970-12-23 21:32:57.008825', NULL, NULL),(7, 6, '1964-12-04', '1964-12-04', '08:18:17.048237', '08:18:17.048237', '1975-07-07 01:03:12.046083', '1975-07-07 01:03:12.046083', 'g', 'g'),(0, 6, NULL, NULL, NULL, NULL, '2028-05-27 13:16:12.043140', '2028-05-27 13:16:12.043140', 'a', 'a'),(2, 5, '1993-12-01', '1993-12-01', '10:14:07.025725', '10:14:07.025725', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'r', 'r'),(0, 5, '1917-12-12', '1917-12-12', '00:00:00', '00:00:00', NULL, NULL, 'b', 'b'),(215, 123, '1966-02-06', '1966-02-06', '16:56:48.009892', '16:56:48.009892', '2022-06-25 00:00:00', '2022-06-25 00:00:00', 's', 's'),(7, 2, '1998-10-11', '1998-10-11', NULL, NULL, NULL, NULL, 'h', 'h'),(7, 3, '1989-03-11', '1989-03-11', '21:15:11.032990', '21:15:11.032990', '1998-06-25 03:32:59.054261', '1998-06-25 03:32:59.054261', NULL, NULL),(8, 6, '1900-07-27', '1900-07-27', '21:54:26.031450', '21:54:26.031450', '1925-11-15 11:28:29.051482', '1925-11-15 11:28:29.051482', NULL, NULL),(NULL, 1, '1939-09-11', '1939-09-11', '23:06:05.032050', '23:06:05.032050', '1940-02-28 17:50:04.055818', '1940-02-28 17:50:04.055818', 'b', 'b'),(14, 4, '1923-01-21', '1923-01-21', NULL, NULL, '1909-01-07 02:38:04.032007', '1909-01-07 02:38:04.032007', 'd', 'd'),(4, 2, '1900-05-12', '1900-05-12', '06:37:50.025041', '06:37:50.025041', '1974-03-30 08:13:31.011553', '1974-03-30 08:13:31.011553', 'a', 'a'),(1, 2, '1900-01-01', '1900-01-01', '03:39:49.019971', '03:39:49.019971', '1911-08-06 16:12:49.025857', '1911-08-06 16:12:49.025857', 's', 's'),(5, 4, '1911-01-23', '1911-01-23', '23:55:26.034660', '23:55:26.034660', NULL, NULL, 'm', 'm'),(4, 7, '1900-01-01', '1900-01-01', '07:31:48.038737', '07:31:48.038737', '1974-04-27 23:06:31.064424', '1974-04-27 23:06:31.064424', 'a', 'a'),(6, 3, '1978-11-27', '1978-11-27', '05:10:23.007693', '05:10:23.007693', '1939-11-20 16:43:48.017809', '1939-11-20 16:43:48.017809', 'v', 'v'),(3, 1, '1909-07-06', '1909-07-06', '21:29:01.017911', '21:29:01.017911', '2038-12-19 07:57:40.002809', '2038-12-19 07:57:40.002809', NULL, NULL),(4, 8, '1900-01-01', '1900-01-01', '00:00:00', '00:00:00', '2022-01-10 07:03:45.022853', '2022-01-10 07:03:45.022853', 'j', 'j'),(8, 9, NULL, NULL, '22:34:42.047069', '22:34:42.047069', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'y', 'y'),(5, 9, '1921-01-03', '1921-01-03', '04:46:13.004338', '04:46:13.004338', NULL, NULL, 'u', 'u'),(9, 3, '1978-03-22', '1978-03-22', '20:00:36.060476', '20:00:36.060476', '1905-10-03 20:45:05.055990', '1905-10-03 20:45:05.055990', 'z', 'z'),(1, NULL, '2029-11-08', '2029-11-08', '06:52:11.023725', '06:52:11.023725', NULL, NULL, 'e', 'e'),(23, NULL, '1972-03-06', '1972-03-06', '02:45:47.055280', '02:45:47.055280', '1980-11-17 00:00:00', '1980-11-17 00:00:00', 'g', 'g'),(NULL, 8, '1954-05-22', '1954-05-22', '19:25:24.049962', '19:25:24.049962', NULL, NULL, 'c', 'c'),(9, 6, '1950-04-08', '1950-04-08', '02:28:39.041172', '02:28:39.041172', NULL, NULL, 't', 't');

CREATE /* QNO 0 TID 0 */ TABLE AA (

              pk INTEGER AUTO_INCREMENT,

              col_int_nokey INTEGER,

              col_int_key INTEGER AS (col_int_nokey * 2) STORED,

              col_date_key DATE AS (DATE_SUB(col_date_nokey, INTERVAL 1 DAY)) STORED,

              col_date_nokey DATE,

              col_time_key TIME AS (TIME(col_time_nokey)) STORED,

              col_time_nokey TIME,

              col_datetime_key DATETIME AS (DATE_ADD(col_datetime_nokey, INTERVAL 1 HOUR)) STORED,

              col_datetime_nokey DATETIME,

              col_varchar_key VARCHAR(1) AS (CONCAT('virt-',col_varchar_nokey)) STORED,

              col_varchar_nokey VARCHAR(1),

              PRIMARY KEY (pk ASC),

              KEY (col_int_key ASC),

              KEY (col_date_key ASC),

              KEY (col_time_key DESC),

              KEY (col_datetime_key ASC),

              KEY (col_varchar_key, col_int_key DESC)

          )  AUTO_INCREMENT=10 ENGINE=Aria;

prepare stmt from "SELECT -14 AS ifield1 FROM `D` AS alias1 WHERE ( 'r' NOT IN ( SELECT SQ1_alias1.`col_varchar_nokey` AS SQ1_cfield1 FROM `AA` AS SQ1_alias1 WHERE ( SQ1_alias1.`col_varchar_key` < SQ1_alias1.`col_varchar_nokey` AND SQ1_alias1.`col_varchar_nokey` <= SQ1_alias1.`col_varchar_key` ) HAVING SQ1_cfield1 = 6) ) OR alias1.`col_varchar_nokey` != alias1.`col_varchar_nokey`";

execute stmt;

execute stmt;

Attachments

Activity

People

Assignee:: Oleksandr Byelkin

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2022-12-20 14:01

Updated:: 2023-11-28 10:34

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.