[YSQL][randgen] SELECT DISTINCT query returns duplicate rows (DISTINCT push down issue)
Opened this issue · 0 comments
mtakahar commented
Jira Link: DB-11957
Description
Problem
- Reproducible on recent master (7082b8f).
- The query below returns duplicate rows despite DISTINCT being specified:
YB test# SELECT DISTINCT col_int_key FROM t1 WHERE col_varchar_nokey IS NULL;
col_int_key
-------------
5
<null>
<null>
6
7
9
9
8
8
8
3
(11 rows)
YB test# explain (analyze, costs off) SELECT DISTINCT col_int_key FROM t1 WHERE col_varchar_nokey IS NULL;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Distinct Index Only Scan using t1_col_int_key_col_int_nokey_col_varchar_nokey_col_datetime_idx on t1 (actual time=1.434..1.440 rows=11 loops=1)
Distinct Keys: col_int_key, col_int_nokey, col_varchar_nokey
Storage Filter: (col_varchar_nokey IS NULL)
Heap Fetches: 0
Planning Time: 0.086 ms
Execution Time: 1.483 ms
Peak Memory Usage: 24 kB
(7 rows)
Test Case
SET client_min_messages = warning;
DROP TABLE IF EXISTS t1;
RESET client_min_messages;
CREATE TABLE t1 ( pk integer NOT NULL, col_int_nokey integer, col_int_key integer, col_datetime_key timestamp without time zone, col_datetime_nokey timestamp without time zone, col_varchar_key character varying(1), col_varchar_nokey character varying(1), CONSTRAINT t1_pkey PRIMARY KEY((pk) HASH));
COPY t1 (pk, col_int_nokey, col_int_key, col_datetime_key, col_datetime_nokey, col_varchar_key, col_varchar_nokey) FROM stdin;
81 1 2 \N \N s s
61 0 3 1998-06-12 00:00:00 1998-06-12 00:00:00 t t
45 8 5 2001-04-19 21:52:38.017765 2001-04-19 21:52:38.017765 u u
5 7 8 2015-01-24 15:15:41.015816 2015-01-24 15:15:41.015816 e e
1 8 4 \N \N c c
98 4 9 \N \N n n
49 1 2 1986-02-09 17:44:31.001526 1986-02-09 17:44:31.001526 m m
11 3 48 2004-11-22 00:42:24.007216 2004-11-22 00:42:24.007216 x x
78 3 1 1987-03-08 00:00:00 1987-03-08 00:00:00 z z
64 9 4 1999-06-07 08:08:32.056989 1999-06-07 08:08:32.056989 v v
41 4 4 2025-12-03 06:39:37.00995 2025-12-03 06:39:37.00995 t t
29 4 0 1991-08-12 00:00:00 1991-08-12 00:00:00 i i
89 1 3 1990-01-21 23:43:07.004846 1990-01-21 23:43:07.004846 d d
52 8 \N 1976-06-27 07:34:53.063059 1976-06-27 07:34:53.063059 b b
55 \N 172 1974-07-08 10:33:13.028775 1974-07-08 10:33:13.028775 v v
13 1 3 2011-12-26 00:33:56.056159 2011-12-26 00:33:56.056159 x x
21 4 6 2003-08-10 00:00:00 2003-08-10 00:00:00 h h
43 8 \N 2034-09-08 17:10:40.057585 2034-09-08 17:10:40.057585 \N \N
38 4 5 1900-01-01 00:00:00 1900-01-01 00:00:00 x x
86 1 \N 1991-09-09 12:56:20.057724 1991-09-09 12:56:20.057724 i i
91 2 5 1993-09-06 20:49:33.054044 1993-09-06 20:49:33.054044 i i
90 0 5 \N \N c c
16 4 6 2000-06-10 00:00:00 2000-06-10 00:00:00 s s
59 1 5 \N \N b b
12 210 228 2025-05-06 15:57:54.0461 2025-05-06 15:57:54.0461 x x
73 6 5 1995-10-17 23:46:36.02655 1995-10-17 23:46:36.02655 n n
40 9 1 1978-11-17 06:51:08.060652 1978-11-17 06:51:08.060652 d d
97 8 9 2025-03-18 22:35:04.041716 2025-03-18 22:35:04.041716 z z
15 251 39 2006-06-12 11:13:17.034994 2006-06-12 11:13:17.034994 e e
44 \N 6 1975-04-15 20:15:45.050577 1975-04-15 20:15:45.050577 v v
6 4 2 2010-04-25 15:55:43.024669 2010-04-25 15:55:43.024669 l l
51 5 9 \N \N n n
7 7 9 2002-05-21 00:00:00 2002-05-21 00:00:00 \N \N
96 3 1 2012-06-07 00:00:00 2012-06-07 00:00:00 n n
42 8 8 2000-03-11 16:35:26.052472 2000-03-11 16:35:26.052472 \N \N
50 3 8 2013-12-19 09:51:49.003532 2013-12-19 09:51:49.003532 x x
32 5 5 1977-10-27 05:05:38.051899 1977-10-27 05:05:38.051899 f f
48 \N 7 \N \N v v
47 8 1 1983-06-10 17:21:09.045864 1983-06-10 17:21:09.045864 o o
9 8 \N 1985-10-11 00:00:00 1985-10-11 00:00:00 c c
77 0 3 1983-11-11 20:10:29.001244 1983-11-11 20:10:29.001244 y y
94 2 1 2031-06-13 00:26:39.015322 2031-06-13 00:26:39.015322 q q
65 1 6 1900-01-01 00:00:00 1900-01-01 00:00:00 n n
83 4 8 1998-10-24 03:10:15.057252 1998-10-24 03:10:15.057252 \N \N
71 5 9 1980-06-08 21:21:31.059876 1980-06-08 21:21:31.059876 k k
60 0 4 1987-04-27 12:42:58.026518 1987-04-27 12:42:58.026518 s s
92 0 1 1991-09-20 10:11:02.005667 1991-09-20 10:11:02.005667 t t
17 4 8 1988-04-07 00:00:00 1988-04-07 00:00:00 k k
10 6 \N 1900-01-01 00:00:00 1900-01-01 00:00:00 u u
72 4 7 2029-05-25 23:36:30.001155 2029-05-25 23:36:30.001155 \N \N
19 4 \N 2019-04-05 11:02:01.035782 2019-04-05 11:02:01.035782 x x
36 211 172 2026-12-07 10:17:40.013275 2026-12-07 10:17:40.013275 h h
58 9 6 2020-05-19 17:08:35.008795 2020-05-19 17:08:35.008795 \N \N
70 9 2 \N \N k k
34 2 0 1982-11-03 00:00:00 1982-11-03 00:00:00 p p
53 7 \N 1900-01-01 00:00:00 1900-01-01 00:00:00 \N \N
54 2 3 1900-01-01 00:00:00 1900-01-01 00:00:00 r r
23 1 1 2020-06-24 00:00:00 2020-06-24 00:00:00 x x
69 1 8 1900-01-01 00:00:00 1900-01-01 00:00:00 h h
87 2 3 1983-06-09 00:00:00 1983-06-09 00:00:00 \N \N
4 \N 4 1998-07-23 22:15:05.039929 1998-07-23 22:15:05.039929 g g
18 9 3 1988-08-01 01:33:30.032947 1988-08-01 01:33:30.032947 m m
66 9 0 1986-05-11 00:00:00 1986-05-11 00:00:00 j j
30 8 0 2029-05-24 18:11:13.003533 2029-05-24 18:11:13.003533 e e
68 2 5 \N \N \N \N
57 \N 6 \N \N u u
88 8 7 1900-01-01 00:00:00 1900-01-01 00:00:00 h h
100 \N 3 1987-03-27 06:53:22.006663 1987-03-27 06:53:22.006663 p p
37 2 \N \N \N m m
99 4 8 2007-07-15 12:46:49.046948 2007-07-15 12:46:49.046948 r r
75 2 0 2010-04-14 13:27:34.062542 2010-04-14 13:27:34.062542 s s
84 9 \N 1983-08-16 00:00:00 1983-08-16 00:00:00 d d
63 \N 7 1982-05-18 02:54:34.025888 1982-05-18 02:54:34.025888 m m
67 7 8 2004-12-17 21:47:57.031968 2004-12-17 21:47:57.031968 \N \N
27 4 \N 2020-02-22 21:45:20.001412 2020-02-22 21:45:20.001412 u u
79 0 0 1900-01-01 00:00:00 1900-01-01 00:00:00 b b
2 3 5 1974-11-06 04:59:50.060815 1974-11-06 04:59:50.060815 c c
8 7 6 2023-11-19 18:59:17.04111 2023-11-19 18:59:17.04111 v v
46 4 5 1986-02-03 00:00:00 1986-02-03 00:00:00 p p
80 6 6 2017-07-21 19:26:57.063604 2017-07-21 19:26:57.063604 f f
14 2 5 1993-03-12 00:00:00 1993-03-12 00:00:00 l l
35 4 2 2030-11-26 19:28:11.005115 2030-11-26 19:28:11.005115 n n
56 3 \N 1992-07-18 04:16:49.0346 1992-07-18 04:16:49.0346 a a
93 1 2 2008-10-11 00:00:00 2008-10-11 00:00:00 g g
22 \N 3 2003-07-12 19:18:28.007325 2003-07-12 19:18:28.007325 u u
24 6 4 1990-06-10 09:54:42.024934 1990-06-10 09:54:42.024934 l l
26 \N 1 1993-03-09 11:26:35.022784 1993-03-09 11:26:35.022784 i i
82 9 \N \N \N d d
39 125 119 2013-11-27 00:00:00 2013-11-27 00:00:00 d d
62 8 2 1900-01-01 00:00:00 1900-01-01 00:00:00 b b
28 248 97 \N \N i i
74 5 7 2005-02-28 00:00:00 2005-02-28 00:00:00 e e
33 9 9 1994-08-04 14:29:37.030503 1994-08-04 14:29:37.030503 \N \N
31 4 9 2011-12-21 15:20:30.054478 2011-12-21 15:20:30.054478 h h
76 4 4 2008-10-20 00:00:00 2008-10-20 00:00:00 w w
85 4 \N \N \N n n
95 \N 7 1977-06-18 08:40:49.042917 1977-06-18 08:40:49.042917 l l
3 3 8 1988-08-13 00:00:00 1988-08-13 00:00:00 q q
25 2 3 1900-01-01 00:00:00 1900-01-01 00:00:00 p p
20 \N 2 2019-10-25 12:46:30.035123 2019-10-25 12:46:30.035123 s s
\.
CREATE INDEX t1_col_datetime_key_idx ON t1 (col_datetime_key HASH);
CREATE INDEX t1_col_int_key_col_datetime_key_col_varchar_key_idx ON t1 (col_int_key HASH, col_datetime_key ASC, col_varchar_key ASC);
CREATE INDEX t1_col_int_key_col_int_nokey_col_varchar_nokey_col_datetime_idx ON t1 (col_int_key HASH) INCLUDE (col_int_nokey, col_varchar_nokey, col_datetime_nokey);
CREATE INDEX t1_col_int_key_col_varchar_key_col_int_nokey_col_datetime_n_idx ON t1 (col_int_key HASH, col_varchar_key ASC) INCLUDE (col_int_nokey, col_datetime_nokey);
CREATE INDEX t1_col_varchar_key_col_int_nokey_col_varchar_nokey_idx ON t1 (col_varchar_key HASH) INCLUDE (col_int_nokey, col_varchar_nokey);
SELECT
DISTINCT col_int_key
FROM
t1
WHERE
col_varchar_nokey IS NULL
;
Issue Type
kind/bug
Warning: Please confirm that this issue does not contain any sensitive information
- I confirm this issue does not contain any sensitive information.