[YSQL][randgen] Index Scan with inequality row comparison access key returns wrong results

[mtakahar]

Jira Link: DB-10747

Description

Problem

• Reproducible on a recent master (f60d999).
• Index (Only) Scan with Index Cond: (ROW(col_bigint_key, col_varchar_10_key) >= ROW(8, ' '::text)) returns 110 rows (Both old and new CBO produces the same plan for this query):

YB test# SET yb_enable_optimizer_statistics = ON;
SET
YB test# SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
--
(110 rows)

YB test# EXPLAIN SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Only Scan using t1_col_bigint_key_col_varchar_10_key_idx on t1 table1  (cost=4.00..18.22 rows=89 width=0)
   Index Cond: (ROW(col_bigint_key, col_varchar_10_key) >= ROW(8, ' '::text))
(2 rows)

• Seq Scan and Index Scan with Index Cond: (col_bigint_key >= 8) + Filter: (ROW(col_bigint_key, (col_varchar_10_key)::text) >= ROW(8, ' '::text)) both return 88 rows.

YB test# SET yb_enable_optimizer_statistics = OFF;
SET
YB test# SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
--
(88 rows)

YB test# EXPLAIN SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Index Scan using t1_col_bigint_key_idx on t1 table1  (cost=0.00..4.34 rows=3 width=0)
   Index Cond: (col_bigint_key >= 8)
   Filter: (ROW(col_bigint_key, (col_varchar_10_key)::text) >= ROW(8, ' '::text))
(3 rows)

YB test# /*+ SeqScan(table1) */SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
--
(88 rows)

YB test# EXPLAIN /*+ SeqScan(table1) */SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Seq Scan on t1 table1  (cost=180.00..1120.71 rows=89 width=0)
   Filter: (ROW(col_bigint_key, (col_varchar_10_key)::text) >= ROW(8, ' '::text))
(2 rows)

• Vanilla PG15 returns 88 rows from the query with Seq Scan or Index Scans using either index:

PG15 test# /*+ IndexScan(table1 t1_col_bigint_key_col_varchar_10_key_idx) */ SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
--
(88 rows)

PG15 test# EXPLAIN /*+ IndexScan(table1 t1_col_bigint_key_col_varchar_10_key_idx) */ SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Index Scan using t1_col_bigint_key_col_varchar_10_key_idx on t1 table1  (cost=0.27..17.75 rows=89 width=0)
   Index Cond: (ROW(col_bigint_key, (col_varchar_10_key)::text) >= ROW(8, ' '::text))
(2 rows)

PG15 test# /*+ IndexScan(table1 t1_col_bigint_key_idx) */ SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
--
(88 rows)

PG15 test# EXPLAIN /*+ IndexScan(table1 t1_col_bigint_key_idx) */ SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using t1_col_bigint_key_idx on t1 table1  (cost=0.14..14.01 rows=89 width=0)
   Index Cond: (col_bigint_key >= 8)
   Filter: (ROW(col_bigint_key, (col_varchar_10_key)::text) >= ROW(8, ' '::text))
(3 rows)

PG15 test# /*+ SeqScan(table1) */SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
--
(88 rows)

PG15 test# EXPLAIN /*+ SeqScan(table1) */SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Seq Scan on t1 table1  (cost=0.00..5.82 rows=89 width=0)
   Filter: (ROW(col_bigint_key, (col_varchar_10_key)::text) >= ROW(8, ' '::text))
(2 rows)

PG15 test#

Test Case

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (    col_varchar_10_key character varying(10),    col_bigint_key bigint,    pk integer NOT NULL,    col_int integer,    CONSTRAINT t1_pkey PRIMARY KEY(pk));


COPY t1 (col_varchar_10_key, col_bigint_key, pk, col_int) FROM stdin;
tribal	8	1	-340066304
scholar	\N	2	36
71	9	3	1162870784
k	-70	4	71
26	1	5	-3
mxmbngboyl	\N	6	-56
-7	57	7	\N
civil	-33	8	0
r	\N	9	9
b	54	10	8
z	7	11	75
x	1	12	-33
0	-5950099532686557184	13	-16
gjcfzchymd	95	14	-25
m	-70	15	32
-92	3	16	1
horizon	9	17	-114
invisible	0	18	9
chymdrediw	-11	19	1
z	1	20	7
r	8	21	\N
v	2	22	0
68	\N	23	4
shohgsxkir	9	24	7
gsxkirxhwb	2	25	3
74	-72	26	1871642624
reliable	5	27	2
hw	1	28	5
measuremen	67	29	7
j	6	30	-32
119	5	31	-1608384512
biyfvzqcik	1	32	3
j	8	33	\N
fvzqcikvjq	-7527203827196362752	34	-374669312
zq	-47	35	4
ik	8	36	\N
-55	-5312840185413632000	37	0
proud	-33	38	-92
25	7	39	4
onion	7	40	1
consist	-104	41	6
oyizgsmvpq	-60	42	9
iz	9	43	-965214208
z	-2238007539826425856	44	-107
mvpqvnlkmb	3	45	-1042546688
qvnlkmbjpq	-4722024209297965056	46	\N
musician	-44	47	3
lkmbjpqhtb	4	48	-60
44	-75	49	\N
-112	9	50	4
pq	2	51	-1542979584
q	\N	52	2
tbphaphpzd	118	53	\N
-81	36	54	66
terrorist	2896658985329360896	55	-56
therapist	8	56	8
twice	3	57	2
dhpwqkxkrz	8	58	21
109	7186337630399758336	59	115
g	0	60	24
x	-25	61	7
soup	1	62	\N
zxnaiuewwl	6	63	45
n	39	64	-126
uewwldtyai	0	65	-551813120
s	31	66	80
d	3	67	-49
111	-66	68	54
e	-34	69	32
tyairwteju	93	70	3
ir	5	71	35
wte	4386506037058863104	72	-41
tejukgspzv	3954160472831295488	73	204668928
ukgspzvpbp	-103	74	0
45	6	75	4
k	41	76	-103
pz	64	77	-9
pb	\N	78	46
i	5256545190071500800	79	4
-41	9	80	\N
i	5	81	8
fruit	7	82	0
fscjacihuw	1	83	87
cjacihuwot	-109	84	4
forever	4	85	9
newly	-20	86	91
97	\N	87	5
ihuwotzmyt	-42	88	8
-49	3	89	-81
o	4	90	-16
uncertain	4	91	74
-98	5	92	6
t	4	93	15
vocal	-1536853372840181760	94	\N
myttermrku	1	95	-16
ttermrkuob	8351644033981874176	96	\N
b	9	97	2
117	4	98	26
-56	120	99	\N
-8	2	100	-65
uobtkmgpsd	5780933071683452928	101	8
f	5	102	7
tkmgpsdbvd	\N	103	8
mg	8	104	-1492451328
db	\N	105	-60
b	5	106	3
twentieth	7	107	-49
sqluxqntts	-88	108	\N
dessert	3	109	48
92	-53	110	9
e	4	111	4
r	-60	112	409141248
ttsldddywz	0	113	\N
sldddywzdu	\N	114	1465516032
welfare	7	115	85
ddywzduudv	5	116	-1931149312
e	-14	117	8
r	-60	118	8
hope	89	119	-92
duudvuejso	-41	120	-176160768
rational	7	121	48
uejsojsuqf	5	122	69
so	3	123	3
k	3	124	1
king	6	125	-795082752
n	1532068298236100608	126	-33
motive	5	127	6
v	-69	128	4
0	8	129	4
p	0	130	2
n	4	131	2
wckwgqigkh	9	132	\N
kwgqigkhih	2035908506548174848	133	80
l	30	134	\N
qigk	3	135	-98
68	3	136	69
gkhi	-5569263889197039616	137	15
c	114	138	-43
carrier	\N	139	61
-43	\N	140	47
42	0	141	1907818496
-46	25	142	-57
ejgmbhtwqx	-35	143	-1826226176
jgmb	3	144	-124
bhtwqxlagf	3	145	-71
d	\N	146	6
l	102	147	-17
actual	83	148	-46
f	4	149	93
resort	-8	150	77
speech	94	151	4
-73	-15	152	3
7	7	153	-42
pant	\N	154	8
23	-109	155	35
-38	\N	156	-67
h	-53	157	9
weekly	1	158	-941031424
blvehqfodg	8	159	2
o	\N	160	13
q	1	161	\N
dg	2141743097791381504	162	-80
32	3	163	-1298989056
122	9	164	-1616183296
37	6846315858533285888	165	43
4	5	166	3
103	-25	167	\N
kpzdfeernt	-4707106035532300288	168	8
z	65	169	58
fe	106	170	\N
f	101	171	-7
rntkgvywka	-5225301467656617984	172	\N
j	-8	173	-55
58	86	174	5
ground	\N	175	-632094720
mere	\N	176	6
copy	9	177	27
whenever	\N	178	\N
29	-5241627016305836032	179	\N
algdzufqib	2	180	-119
lgdzufqibm	789818784650100736	181	45
v	-7	182	\N
q	90	183	4
s	7	184	108
b	6433673542675464192	185	7
q	\N	186	-121
ibmnxnmtcg	9	187	72
expose	-110	188	47
nxnmtcgfew	5065142205908254720	189	1
xnmtcgfewt	2	190	59
-24	-104	191	9
a	9	192	1038221312
quantity	\N	193	-7
m	3	194	4
58	-73	195	1
scramble	9	196	7
fxzuopxheu	97	197	4
grade	7	198	92
opxheuupum	4090675836535963648	199	\N
m	74	200	-122
b	2	201	81
107	2775624745343778816	202	-1
fiscal	3	203	9
e	2	204	-1379205120
pu	8	205	-30
otowczasnb	-7811493553674125312	206	113
t	6	207	-99
towczasnbr	67	208	-2032271360
czasnbrpmc	3	209	1
-86	\N	210	\N
suspicious	90	211	9
g	-10	212	367132672
-15	25	213	1
nbrp	9	214	5
pmcpclusgl	-2994330802247958528	215	53
fog	1	216	7
achieve	12	217	99
usglcdgdfm	0	218	19
72	63	219	7
glcdgdfmig	7	220	3
handsome	8681814181663473664	221	8
dgdfmiguon	7714947636662370304	222	-66
-20	2	223	-73
43	-44	224	2
r	-34	225	-82
-49	2632353982198054912	226	0
u	92	227	1
p	9	228	54
nuu	0	229	1
e	-6364993648358064128	230	1543372800
f	8	231	-39
k	6	232	-59
99	3	233	-514850816
p	89	234	-321388544
corruption	0	235	-108
tiesejrbwt	-75	236	-1120075776
e	9	237	-263782400
121	9	238	-868089856
v	98	239	37
blond	-35	240	82
rbwtpwtiyx	9	241	1
tpwt	0	242	112
wti	35	243	9
iy	5	244	-1294925824
v	6	245	\N
odvpijgyfa	4	246	2
agent	3191926235898839040	247	1
openly	42	248	\N
gyfatagfbr	4	249	-54
r	357191745445822464	250	7
e	5	251	-27
tagfbrrjli	7	252	9
alcohol	67	253	0
-113	110	254	-913637376
rjli	1	255	12
\.

CREATE INDEX t1_col_bigint_key_col_varchar_10_key_idx ON t1 (col_bigint_key ASC, col_varchar_10_key ASC);

CREATE INDEX t1_col_bigint_key_idx ON t1 (col_bigint_key ASC);


ANALYZE t1;

/*+ IndexScan(table1 t1_col_bigint_key_col_varchar_10_key_idx) */ SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;

/*+ IndexScan(table1 t1_col_bigint_key_idx) */ SELECT  FROM t1  AS table1  WHERE ( col_bigint_key  , col_varchar_10_key  )  >= (  8  , ' ' )   ;

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.