sql: internal error: in-between filters didn't yield a constraint #80820

cockroach-teamcity · 2022-04-30T05:38:28Z

roachtest.sqlsmith/setup=rand-tables/setting=no-mutations failed with artifacts on master @ a2e1910f51593bd2ef72e1d7c615e08f95791186:

The test failed on branch=master, cloud=gce:
test artifacts and logs in: /artifacts/sqlsmith/setup=rand-tables/setting=no-mutations/run_1
	sqlsmith.go:265,sqlsmith.go:305,test_runner.go:876: error: pq: internal error: in-between filters didn't yield a constraint
		stmt:
		WITH
			with_60848 (col_348252)
				AS (
					SELECT
						*
					FROM
						(VALUES ('hviat':::rand_typ_0), ('ymlez':::rand_typ_0), ('pzlue':::rand_typ_0), ('ivkys':::rand_typ_0))
							AS tab_152645 (col_348252)
				)
		SELECT
			NULL AS col_348253,
			tab_152646.col1_3 AS col_348254,
			tab_152646.col1_6 AS col_348255,
			tab_152646.col1_8 AS col_348256,
			23871:::INT8 AS col_348257,
			'\x5c91d7a0d2edd6fc0f':::BYTES AS col_348258,
			tab_152646.col1_5 AS col_348259,
			e'F\x01W[\x12':::STRING AS col_348260,
			(-6.335565852276255591E+29):::DECIMAL AS col_348261,
			tab_152646.col1_4 AS col_348262,
			NULL AS col_348263
		FROM
			defaultdb.public.table1@[0] AS tab_152646
		WHERE
			(7679919245303374124:::INT8 < tab_152646.col1_2)
		ORDER BY
			tab_152646.tableoid DESC;

Help

See: roachtest README

See: How To Investigate (internal)

/cc @cockroachdb/sql-queries _{This test on roachdash | Improve this report!

Jira issue: CRDB-15503}

The text was updated successfully, but these errors were encountered:

cockroach-teamcity · 2022-05-01T05:30:01Z

roachtest.sqlsmith/setup=rand-tables/setting=no-mutations failed with artifacts on master @ c9e0194b19a03d55c6be92572aad3fbafc256334:

The test failed on branch=master, cloud=gce:
test artifacts and logs in: /artifacts/sqlsmith/setup=rand-tables/setting=no-mutations/run_1
	sqlsmith.go:265,sqlsmith.go:305,test_runner.go:876: error: pq: internal error: in-between filters didn't yield a constraint
		stmt:
		SELECT
			tab_201.col1_5 AS col_410,
			tab_201.col1_3 AS col_411,
			tab_201.col1_1 AS col_412,
			3469688880:::OID AS col_413,
			'\x046086073eb3f42648':::BYTES AS col_414,
			tab_201.col1_3 AS col_415
		FROM
			defaultdb.public.table1@[0] AS tab_201
		WHERE
			tab_201.col1_9 SIMILAR TO tab_201.col1_10
		ORDER BY
			tab_201.col1_9 DESC, tab_201.col1_10 ASC;

Help

See: roachtest README

See: How To Investigate (internal)

_{This test on roachdash | Improve this report!}

rytaft · 2022-05-02T12:19:27Z

This reproduces on 21.2 and 22.1. Not a release blocker.

Steps to reproduce:

SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false;

SET CLUSTER SETTING sql.stats.histogram_collection.enabled = false;

CREATE TABLE table1 (col1_0 BYTES NOT NULL, col1_1 CHAR NOT NULL, col1_2 INT2 NOT NULL, col1_3 BIT(36), col1_4 JSONB, col1_5 INT2 NOT NULL AS (col1_2 + (-1142):::INT8) VIRTUAL, col1_6 STRING AS (lower(col1_1)) STORED, col1_7 INT2 NOT NULL AS (col1_2 + (-15933):::INT8) VIRTUAL, col1_8 STRING AS (lower(col1_1)) STORED, PRIMARY KEY (col1_0 DESC, col1_2 ASC, col1_1), UNIQUE (col1_8, col1_6 ASC) WHERE ((((((table1.col1_7 > 0:::INT8) AND (table1.col1_0 != '\x58':::BYTES)) OR (table1.col1_8 > e'\U00002603':::STRING)) OR (table1.col1_5 <= (-1):::INT8)) OR (table1.col1_1 >= e'\x00':::STRING)) AND (table1.col1_2 >= 127:::INT8)) AND (table1.col1_6 >= '':::STRING), UNIQUE (col1_2 DESC, lower(CAST(col1_3 AS STRING)), col1_8 DESC, col1_3, col1_5, col1_6 DESC, (col1_2 + (-8751):::INT8) DESC), INDEX (col1_0 DESC, col1_1 ASC, (CASE WHEN col1_0 IS NULL THEN '1EdvfB':::STRING ELSE e'P\x1f<tm)hyF':::STRING END), col1_2 ASC, col1_7, (col1_2 + 13771:::INT8) ASC, col1_6 DESC, lower(CAST(col1_3 AS STRING)) ASC) PARTITION BY LIST (col1_0, col1_1) (PARTITION table1_part_0 VALUES IN (('\x':::BYTES, e'\U00002603':::STRING), ('\x519f2f9baa759c':::BYTES, e'\x03':::STRING)), PARTITION table1_part_1 VALUES IN (('\xb36347fd5990ba6c':::BYTES, 'Q':::STRING), ('\x32ef63c4':::BYTES, 'l':::STRING)), PARTITION table1_part_2 VALUES IN (('\x7c921597':::BYTES, e'\x16':::STRING), ('\x28d8303ffd':::BYTES, e'\x0e':::STRING)), PARTITION table1_part_3 VALUES IN (('\x8ba318':::BYTES, 'J':::STRING), ('\x22':::BYTES, 'y':::STRING)), PARTITION table1_part_4 VALUES IN (('\x61':::BYTES, e'\x7f':::STRING), ('\xa3df':::BYTES, 'v':::STRING)), PARTITION table1_part_5 VALUES IN (('\x':::BYTES, e'\x1f':::STRING), ('\x61c054':::BYTES, 'd':::STRING)), PARTITION table1_part_6 VALUES IN (('\x4debaf315980':::BYTES, ',':::STRING), ('\x8b':::BYTES, 'u':::STRING)), PARTITION table1_part_7 VALUES IN (('\x8f631956bf6598':::BYTES, 'x':::STRING), ('\x7aba4ea69d38':::BYTES, 'g':::STRING))) WHERE (((((table1.col1_6 > '':::STRING) AND (table1.col1_5 > (-1):::INT8)) OR (table1.col1_7 != 127:::INT8)) OR (table1.col1_2 != 1:::INT8)) OR (table1.col1_0 >= '\x27':::BYTES)) OR (table1.col1_1 = '"':::STRING), INDEX (col1_6, col1_7, col1_0), UNIQUE (col1_0, lower(CAST(col1_4 AS STRING)) DESC, col1_7 ASC, col1_8, col1_3 DESC));

CREATE TABLE table2 (col2_0 TIMESTAMPTZ, col2_1 TIME, col2_2 NAME NULL, col2_3 TIMESTAMPTZ, col2_4 INET NOT NULL, col2_5 REGPROC NULL, col2_6 FLOAT4 NOT NULL, col2_7 BOOL NOT NULL, col2_8 REGNAMESPACE NOT NULL, col2_9 BOX2D, col2_10 REGPROC NOT NULL, PRIMARY KEY (col2_10 DESC), INDEX (col2_9, col2_7 ASC, col2_4, col2_8, col2_0, col2_5, col2_3, col2_1 DESC, col2_10), UNIQUE (col2_10 DESC, col2_3 DESC, col2_1 ASC, col2_4 DESC, col2_9 ASC, col2_7, col2_8 DESC, col2_0 DESC, col2_2) STORING (col2_5) WHERE ((((table2.col2_7 AND (table2.col2_0 >= '0001-01-01 00:00:00+00:00':::TIMESTAMPTZ)) OR (table2.col2_3 = '3000-01-01 00:00:00+00:00':::TIMESTAMPTZ)) OR (table2.col2_6 >= 3.4028234663852886e+38:::FLOAT8)) AND (table2.col2_1 >= '24:00:00':::TIME)) AND (table2.col2_2 >= '"':::STRING), UNIQUE (col2_1 ASC, col2_3 DESC, col2_9 ASC, col2_0, col2_10 DESC), UNIQUE (col2_0 DESC, col2_6, col2_5 ASC, col2_10 DESC, col2_7 ASC, col2_9 ASC), INDEX (col2_6 DESC, col2_7 DESC, col2_10 DESC, col2_2, col2_5 DESC, col2_9 ASC, col2_1 ASC) STORING (col2_0, col2_4, col2_8) WHERE ((((table2.col2_7 OR (table2.col2_2 < e'\'':::STRING)) AND (table2.col2_0 < '0001-01-01 00:00:00+00:00':::TIMESTAMPTZ)) OR (table2.col2_3 > '3000-01-01 00:00:00+00:00':::TIMESTAMPTZ)) AND (table2.col2_1 < '00:00:00':::TIME)) OR (table2.col2_6 <= 0.0:::FLOAT8), INDEX (col2_6 ASC) STORING (col2_0, col2_8) PARTITION BY LIST (col2_6) (PARTITION table2_part_0 VALUES IN (((-0.22332091629505157):::FLOAT8,), (1.3258287906646729:::FLOAT8,), ((-0.14706562459468842):::FLOAT8,), (0.42361849546432495:::FLOAT8,), (0.9313597679138184:::FLOAT8,)), PARTITION table2_part_1 VALUES IN ((0.9743322730064392:::FLOAT8,), (0.15822868049144745:::FLOAT8,), (1.2111992835998535:::FLOAT8,), (1.8988499641418457:::FLOAT8,), (1.6419795751571655:::FLOAT8,)), PARTITION table2_part_2 VALUES IN (((-0.9581801891326904):::FLOAT8,), (0.07313848286867142:::FLOAT8,), (0.3936866521835327:::FLOAT8,), ((-0.5762010216712952):::FLOAT8,), ((-1.4921040534973145):::FLOAT8,)), PARTITION table2_part_3 VALUES IN (((-0.051310744136571884):::FLOAT8,), (0.4275163412094116:::FLOAT8,), (0.5709536671638489:::FLOAT8,), ((-0.968177080154419):::FLOAT8,), ((-0.2234959453344345):::FLOAT8,))) WHERE (((((table2.col2_1 > '24:00:00':::TIME) AND (NOT table2.col2_7)) AND (table2.col2_3 < '0001-01-01 00:00:00+00:00':::TIMESTAMPTZ)) OR (table2.col2_6 != (-1.0):::FLOAT8)) OR (table2.col2_0 < '0001-01-01 00:00:00+00:00':::TIMESTAMPTZ)) AND (table2.col2_2 >= e'\'':::STRING), UNIQUE (col2_1 DESC, lower(CAST(col2_4 AS STRING)), col2_8 ASC), UNIQUE ((CASE WHEN col2_5 IS NULL THEN '6{A':::STRING ELSE e'D#el7\x18Z\\y':::STRING END) ASC, col2_7 ASC, col2_2, col2_1 DESC, col2_4 DESC) STORING (col2_0, col2_5, col2_6, col2_8, col2_9), INDEX (col2_10 ASC, col2_3, col2_1 ASC, col2_8 DESC) STORING (col2_0, col2_4, col2_5), FAMILY (col2_7, col2_3), FAMILY (col2_5, col2_4, col2_9), FAMILY (col2_8), FAMILY (col2_0), FAMILY (col2_10), FAMILY (col2_1), FAMILY (col2_2), FAMILY (col2_6));

ALTER TABLE table1 INJECT STATISTICS e'[{"avg_size": 0, "columns": ["col1_0"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 6074885787930024685, "histo_buckets": [{"distinct_range": 0, "num_eq": 6000000000, "num_range": 0, "upper_bound": "\\\\x"}, {"distinct_range": 0, "num_eq": 5000000000, "num_range": 5281895191266660529, "upper_bound": "\\\\x284dbe8e36ab905e"}, {"distinct_range": 0, "num_eq": 9055612309703752530, "num_range": 20, "upper_bound": "\\\\x6c24"}, {"distinct_range": 878782271850024600, "num_eq": 3954130133571870473, "num_range": 2845268650464067204, "upper_bound": "\\\\x82e298d3"}, {"distinct_range": 0, "num_eq": 40, "num_range": 90000000, "upper_bound": "\\\\xcf"}, {"distinct_range": 4225847753070243000, "num_eq": 7005611526409198257, "num_range": 4225847753070242860, "upper_bound": "\\\\xe29883"}], "histo_col_type": "BYTES", "name": "__auto__", "null_count": 0, "row_count": 6422227499722755882}, {"avg_size": 0, "columns": ["col1_1"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 5658149074648075857, "histo_col_type": "", "name": "__auto__", "null_count": 0, "row_count": 6422227499722755882}, {"avg_size": 0, "columns": ["col1_3"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 526461287075468208, "histo_col_type": "", "name": "__auto__", "null_count": 778872965158271421, "row_count": 6422227499722755882}, {"avg_size": 0, "columns": ["col1_4"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 167639354710879825, "histo_col_type": "", "name": "__auto__", "null_count": 339192229057091969, "row_count": 6422227499722755882}, {"avg_size": 0, "columns": ["col1_5"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 1971794543500445935, "histo_col_type": "", "name": "__auto__", "null_count": 0, "row_count": 6422227499722755882}, {"avg_size": 0, "columns": ["col1_6"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 1821876992083884151, "histo_buckets": [{"distinct_range": 0, "num_eq": 640291050312474131, "num_range": 0, "upper_bound": ""}, {"distinct_range": 3713011.9318973753, "num_eq": 400000000, "num_range": 10000000, "upper_bound": "\\u0007\\u0007O"}, {"distinct_range": 977548209.5493942, "num_eq": 431867404602468877, "num_range": 1000000000, "upper_bound": "-$6"}, {"distinct_range": 2491753839669798000, "num_eq": 70, "num_range": 3231504731575098133, "upper_bound": "1#A"}, {"distinct_range": 5485182409425973000, "num_eq": 5675712598480334295, "num_range": 5485182409425973597, "upper_bound": "1(G6"}, {"distinct_range": 6000000000, "num_eq": 94562554082264545, "num_range": 6000000000, "upper_bound": "Y\\u001a}zmY\\u000c"}, {"distinct_range": 0, "num_eq": 5062991620697521716, "num_range": 60000, "upper_bound": "^T7"}, {"distinct_range": 14.039541398256308, "num_eq": 8533669312078325274, "num_range": 90, "upper_bound": "f\\u0002|\\u0007"}], "histo_col_type": "STRING", "name": "__auto__", "null_count": 5690582199323826442, "row_count": 6422227499722755882}, {"avg_size": 0, "columns": ["col1_7"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 1637692812334974017, "histo_col_type": "", "name": "__auto__", "null_count": 0, "row_count": 6422227499722755882}, {"avg_size": 0, "columns": ["col1_2"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 490991151604857281, "histo_buckets": [{"distinct_range": 0, "num_eq": 4785183140655916187, "num_range": 0, "upper_bound": "-25466"}, {"distinct_range": 0, "num_eq": 6622766102093750155, "num_range": 100000, "upper_bound": "-20027"}, {"distinct_range": 90000000, "num_eq": 6733124653833020425, "num_range": 90000000, "upper_bound": "-1075"}, {"distinct_range": 432706412369414660, "num_eq": 300000, "num_range": 1853784651482932150, "upper_bound": "20350"}, {"distinct_range": 488.9935633087875, "num_eq": 90000000, "num_range": 500, "upper_bound": "27876"}], "histo_col_type": "INT2", "name": "__auto__", "null_count": 0, "row_count": 6422227499722755882}, {"avg_size": 0, "columns": ["col1_8"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 2104060673772675750, "histo_buckets": [{"distinct_range": 0, "num_eq": 7028385153942296316, "num_range": 0, "upper_bound": "UAWN\\u0015?\'&"}], "histo_col_type": "STRING", "name": "__auto__", "null_count": 2865210878004966949, "row_count": 6422227499722755882}]':::JSONB;

ALTER TABLE table2 INJECT STATISTICS '[{"avg_size": 0, "columns": ["col2_1"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 507, "histo_buckets": [{"distinct_range": 0, "num_eq": 5003451574652010517, "num_range": 0, "upper_bound": "00:00:00"}, {"distinct_range": 4000000000, "num_eq": 904827248757512403, "num_range": 4000000000, "upper_bound": "08:02:45.724544"}, {"distinct_range": 120751803063899380, "num_eq": 0, "num_range": 702189785260828919, "upper_bound": "14:11:01.82398"}], "histo_col_type": "TIME", "name": "__auto__", "null_count": 397, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_2"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 422, "histo_col_type": "", "name": "__auto__", "null_count": 835, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_3"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 100, "histo_col_type": "", "name": "__auto__", "null_count": 113, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_4"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 10, "histo_col_type": "", "name": "__auto__", "null_count": 0, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_5"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 497, "histo_col_type": "", "name": "__auto__", "null_count": 47, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_6"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 787, "histo_buckets": [{"distinct_range": 0, "num_eq": 80000000000, "num_range": 0, "upper_bound": "-1.6878645420074463"}, {"distinct_range": 0, "num_eq": 2236630730195273271, "num_range": 2211565209284221882, "upper_bound": "-1.3739452362060547"}, {"distinct_range": 0, "num_eq": 200000000, "num_range": 5403326609824866803, "upper_bound": "-0.9463318586349487"}, {"distinct_range": 0, "num_eq": 30000000000, "num_range": 300, "upper_bound": "-0.5479167103767395"}, {"distinct_range": 25.152026946170004, "num_eq": 7510494652347942924, "num_range": 100, "upper_bound": "0.31880638003349304"}, {"distinct_range": 2944476145265917400, "num_eq": 1000000, "num_range": 2944476145265917364, "upper_bound": "0.37076687812805176"}], "histo_col_type": "FLOAT4", "name": "__auto__", "null_count": 0, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_9"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 436, "histo_buckets": [{"distinct_range": 0, "num_eq": 80, "num_range": 0, "upper_bound": "BOX(-10 -10,10 10)"}, {"distinct_range": 312288301030452700, "num_eq": 8000000, "num_range": 312288301030452687, "upper_bound": "BOX(-1.906022913912544 -1.5512382351860532,-1.3837772156636254 -0.0083511788489124)"}, {"distinct_range": 3369682487707032600, "num_eq": 40000, "num_range": 3369682487707032630, "upper_bound": "BOX(-1.5336591482143653 -0.7045324436812491,0.3244419825788995 0.9881986273131259)"}, {"distinct_range": 6137507505299272000, "num_eq": 3391408561837110970, "num_range": 6137507505299271630, "upper_bound": "BOX(-1.4369269527113384 -0.1671449284304856,-0.2058539132810855 0.20310604754308392)"}, {"distinct_range": 12.02982801848461, "num_eq": 7993900434390426508, "num_range": 90, "upper_bound": "BOX(-1.0333236822385565 -1.146408215951928,0.3282471806653158 1.6565093802237485)"}, {"distinct_range": 0, "num_eq": 6000000000, "num_range": 80000000, "upper_bound": "BOX(-0.9094493741502774 -1.136189557506939,0.21377987304014723 0.2755747958391088)"}, {"distinct_range": 0, "num_eq": 900000, "num_range": 8000000, "upper_bound": "BOX(-0.7691777135603486 -0.6905086191211366,1.0600420860550908 0.7578859938924563)"}, {"distinct_range": 0, "num_eq": 4795021084043433913, "num_range": 4485133560304189525, "upper_bound": "BOX(-0.33315489046786295 1.1894969196415153,1.1213797116399287 1.3472370275863101)"}, {"distinct_range": 0, "num_eq": 80000, "num_range": 300, "upper_bound": "BOX(0.5750300910700044 0.5800791180735364,0.895800966424561 1.0418294447495091)"}], "histo_col_type": "BOX2D", "name": "__auto__", "null_count": 789, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_0"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 256, "histo_buckets": [{"distinct_range": 0, "num_eq": 10000000000, "num_range": 0, "upper_bound": "0001-01-01 00:00:00+00:00"}, {"distinct_range": 0, "num_eq": 900000000, "num_range": 10000000, "upper_bound": "1992-11-22 02:46:30.000501+00:00"}, {"distinct_range": 0, "num_eq": 60, "num_range": 0, "upper_bound": "2006-09-29 05:48:22.000219+00:00"}, {"distinct_range": 20000, "num_eq": 60000000, "num_range": 20000, "upper_bound": "2007-03-23 04:13:33.000558+00:00"}, {"distinct_range": 9000000, "num_eq": 100000000000, "num_range": 9000000, "upper_bound": "2007-11-07 23:27:14.000933+00:00"}], "histo_col_type": "TIMESTAMPTZ", "name": "__auto__", "null_count": 773, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_8"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 652, "histo_col_type": "", "name": "__auto__", "null_count": 0, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_10"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 248, "histo_buckets": [{"distinct_range": 0, "num_eq": 6852106795864968629, "num_range": 0, "upper_bound": "2156427638"}], "histo_col_type": "REGPROC", "name": "__auto__", "null_count": 0, "row_count": 1000}, {"avg_size": 0, "columns": ["col2_7"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 766, "histo_col_type": "", "name": "__auto__", "null_count": 0, "row_count": 1000}]':::JSONB;

CREATE TYPE rand_typ_0 AS ENUM ('ymlez', 'mna', 'pzlue', 'abhhnx', 'hviat', 'ivkys');

SET statement_timeout='1m0s';

WITH
			with_60848 (col_348252)
				AS (
					SELECT
						*
					FROM
						(VALUES ('hviat':::rand_typ_0), ('ymlez':::rand_typ_0), ('pzlue':::rand_typ_0), ('ivkys':::rand_typ_0))
							AS tab_152645 (col_348252)
				)
		SELECT
			NULL AS col_348253,
			tab_152646.col1_3 AS col_348254,
			tab_152646.col1_6 AS col_348255,
			tab_152646.col1_8 AS col_348256,
			23871:::INT8 AS col_348257,
			'\x5c91d7a0d2edd6fc0f':::BYTES AS col_348258,
			tab_152646.col1_5 AS col_348259,
			e'F\x01W[\x12':::STRING AS col_348260,
			(-6.335565852276255591E+29):::DECIMAL AS col_348261,
			tab_152646.col1_4 AS col_348262,
			NULL AS col_348263
		FROM
			defaultdb.public.table1@[0] AS tab_152646
		WHERE
			(7679919245303374124:::INT8 < tab_152646.col1_2)
		ORDER BY
			tab_152646.tableoid DESC;

rharding6373 · 2022-05-05T00:53:35Z

Still working on this, but here is a reduced repro:

CREATE TABLE table1 (col1_0 BYTES NOT NULL, col1_1 CHAR NOT NULL, col1_2 INT2 NOT NULL, col1_3 BIT(36), col1_4 JSONB,
INDEX (col1_0 DESC, col1_1 ASC, col1_2 ASC)
PARTITION BY LIST (col1_0, col1_1) (
PARTITION table1_part_0 VALUES IN (('\xab':::BYTES, 'A':::STRING)),
PARTITION table1_part_1 VALUES IN (('\xdf':::BYTES, 'Q':::STRING))));

SELECT
	col1_4
FROM
	table1
WHERE
	(4:::INT8 < col1_2)

The issue seems to be that we're trying to make a constrained scan over the partitioned index, but for some reason we aren't able to generate a constraint using the index partitions, index constraint, and query constraint. This seems like something we should be able to do.

SQLSmith keeps on hitting a known "in-between filters didn't yield a constraint" error, so let's silence it for now. Informs cockroachdb#80820 Release note: None

80548: sql: add a SET CLUSTER SETTING sql.defaults deprecation notice r=rafiss a=e-mbrown Resolves #80325 Added a deprecation notice for when `SET CLUSTER SETTINGS sql.defaults...` is used. The notice directs users to use the `ALTER ROLE` syntax instead Release note: None 81063: storage: remove slow path for MVCCResolveWriteIntentRange r=nvanbenschoten a=sumeerbhola We no longer have physically interleaved intents, that needed the slow path. Release note: None 81174: ccl/sqlproxyccl: remove the idle monitor component r=JeffSwenson a=jaylim-crl This commit removes the idle monitor component from the sqlproxy as there's no strong use case for it, as discussed. This is currently not being used anywhere on CockroachCloud, and with the connection migration feature, it's unlikely that we will need this. When a pod goes into the draining state, all connections are migrated away to other running pods. Even though the idle monitor may benefit us by terminating idle connections earlier for connections that cannot be migrated, the complexity of maintaining this outweighs the benefits that we get (i.e. being able to shut down a pod earlier; between 1 to 10 minutes rather than waiting until 10 minutes for forceful termination). At the same time, the --drain-timeout flag has been removed from the `mt start-proxy` command as well. Release note: None 81243: opt: fix outdated optgen comment r=mgartner a=mgartner This commit removes a reference to the `DetectCycle` rule tag which is no longer supported. Release note: None 81291: roachtest: silence known sqlsmith error for in-between filters r=rytaft a=rytaft SQLSmith keeps on hitting a known "in-between filters didn't yield a constraint" error, so let's silence it for now. Informs #80820 Release note: None Co-authored-by: e-mbrown <ebsonari@gmail.com> Co-authored-by: sumeerbhola <sumeer@cockroachlabs.com> Co-authored-by: Jay <jay@cockroachlabs.com> Co-authored-by: Marcus Gartner <marcus@cockroachlabs.com> Co-authored-by: Rebecca Taft <becca@cockroachlabs.com>

ajwerner · 2022-05-17T14:07:09Z

The most recent failure of #81094 is this:

QUERY ["SELECT array[(CASE WHEN col2061_2064 IS NULL THEN '':::STRING ELSE e'\\U00002603':::STRING END)::STRING::STRING] AS c FROM ( VALUES(0.10249499732686213:::FLOAT8,ARRAY[]:::FLOAT8[],ARRAY[3955667066:::OID,3583998912:::OID,3246013206:::OID,3929745433:::OID])) AS t(col2061_2063,col2061_2064,col2061_2065);" []] :[["â˜ƒ"]]: scanBool: "SELECT EXISTS ( SELECT * FROM schema1165.table2061 WHERE (col2061_2063)= ( SELECT (col2061_2063) FROM (VALUES( 0.10249499732686213:::FLOAT8) ) AS T(col2061_2063) ) )" []: ERROR: internal error: in-between filters didn't yield a constraint (SQLSTATE XX000)

rharding6373 · 2022-05-17T15:09:52Z

I was debugging this a bit on the plane, and I think I've figured out why we can't seem to produce a constrained scan in these cases.

The code assumes that if we can make a constraint for the query and partition filters, then we should be able to make a constraint for the query and in-between filters. In the reduction above, the filters makes an expression that looks something like this:

(col2 > 4) AND ((col0, col1) > (\xab, A) AND (col0, col1 < (\xdf, Q)) OR ((col0, col1) > (\xdf, Q)) OR ((col0, col1) < (\xab, A))

The first part of the AND (col2 > 4) is unconstrained, which I suspect is because it is the third column in INDEX (col1_0 DESC, col1_1 ASC, col1_2 ASC)

In the second part of the AND, since col0 and col1 have different orderings, we can only constrain on col0. As a result we end up merging spans [\xdf - \xab], [ - \xdf], and [\xab - ] for the OR expressions, which is also unconstrained.

Is it impossible to make a constrained scan using index columns that have different orderings? Maybe the solution here is to loosen the assumption that a constrained partition filter will also have a constrained in-between filter, not panic, and allow the xform rule GenerateConstrainedScans to not generate scans in this case.

rytaft · 2022-05-18T00:44:49Z

I think it is possible to make a constrained scan of an index like this, although it might be trickier with non-numeric and nullable types. Since I was struggling to work with the types in the repro above, I created another (even more) reduced repro:

CREATE TABLE tab_part (
    col0
        INT8 NOT NULL,
    col1
        INT8 NOT NULL,
    col2
        INT8 NOT NULL,
    INDEX (col0 DESC, col1, col2)
        PARTITION BY LIST (col0, col1)
            (
                PARTITION one VALUES IN ((1, 10)),
                PARTITION two VALUES IN ((2, 20))
            )
);

SELECT * FROM tab_part WHERE col2 > 4;

Normally, if the index had all columns ascending, we would construct in-between filters using tuples like this:

WHERE (col2 > 4) AND ((col0, col1) < (1, 10) OR ((col0, col1) > (1, 10) AND (col0, col1) < (2, 20)) OR (col0, col1) > (2, 20));

However, it's still possible to construct in-between filters for this schema as follows:

WHERE (col2 > 4) AND ((col0 > 2 OR (col0 = 2 AND col1 < 20)) OR ((col0 < 2 OR (col0 = 2 AND col1 > 20)) AND (col0 > 1 OR (col0 = 1 AND col1 < 10))) OR (col0 < 1 OR (col0 = 1 AND col1 > 10)));

For example:

> EXPLAIN SELECT * FROM tab_part WHERE col2 > 4 AND ((col0 > 2 OR (col0 = 2 AND col1 < 20)) OR ((col0 < 2 OR (col0 = 2 AND col1 > 20)) AND (col0 > 1 OR (col0 = 1 AND col1 < 10))) OR (col0 < 1 OR (col0 = 1 AND col1 > 10)));
                                        info
------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • filter
  │ estimated row count: 0
  │ filter: col2 > 4
  │
  └── • scan
        estimated row count: 1 (100% of the table; stats collected 13 minutes ago)
        table: tab_part@tab_part_col0_col1_col2_idx
        spans: [ - /2/19] [/2/21/5 - /1/9] [/1/11/5 - ]

That being said, I'm not sure it's really worth it to figure out how to generalize the above logic. If we ever see a real-world schema that requires this we can try, but I'd rather not add the complexity unless we need it. Seems like the easiest approach is to just remove the assertion and return if tryConstrainIndex returns ok=false for inBetweenFilters (as you suggested above). Maybe add a TODO/comment that references this issue for posterity.

Before this change, the optimizer assumed that if partition filters are constrained on an index then the in-between filters are also constrained, and would panic if it was unable to find a constraint. However, when an index is partitioned on multiple columns and the columns have different orders, the optimizer may be unable to generate a constraint for the in between filters of the partitions, even if the partition filters are constrained. This change removes the panic, since the assumption does not hold. As a consequence, the optimizer abondons the GenerateConstrainedScan rule, but doesn't crash. I did not include a release note since this appears to be a rare bug that we have not encountered in the wild. Fixes cockroachdb#80820 Release note: None

Before this change, the optimizer assumed that if partition filters are constrained on an index then the in-between filters are also constrained, and would panic if it was unable to find a constraint. However, when an index is partitioned on multiple columns and the columns have different orders, the optimizer may be unable to generate a constraint for the in between filters of the partitions, even if the partition filters are constrained. This change removes the panic, since the assumption does not hold. As a consequence, the optimizer abandons the GenerateConstrainedScan rule, but doesn't crash. I did not include a release note since this appears to be a rare bug that we have not encountered in the wild. Fixes cockroachdb#80820 Release note: None

80353: sql: enables distributed distsql queries for multi-tenant r=rharding6373 a=rharding6373 sql: enables distributed distsql queries for multi-tenant This change allows SQL queries to be distributed in multi-tenant environments. The distribution algorithm randomly assigns spans to SQL instances, but if only one instance is used the spans are assigned instead to the gateway instance. Distribution does not take locality into account, which will be implemented in a future PR. This change also supports running execbuilder tests with the 3node-tenant configuration, which is under CCL. These tests can be run in the following manner: ``` make test PKG=./pkg/ccl/logictestccl TESTS=TestTenantExecBuild ./dev test pkg/ccl/logictestccl -f=TestTenantExecBuild ``` Fixes: #80680 Release note: None 81452: release: publish latest docker tag for unstable releases r=rail a=rail Previously, when we published the pre-release docker images, we never touched the `latest` tag in the `cockroachdb/cockroach-unstable` docker repository, even though the script comments say we should. This patch adds a check to ensure we push every pre-release docker image with `latest` tag. Fixes #78663 Release note: None 81465: opt: remove panic when in between filters are not constrained r=rharding6373 a=rharding6373 Before this change, the optimizer assumed that if partition filters are constrained on an index then the in-between filters are also constrained, and would panic if it was unable to find a constraint. However, when an index is partitioned on multiple columns and the columns have different orders, the optimizer may be unable to generate a constraint for the in between filters of the partitions, even if the partition filters are constrained. This change removes the panic, since the assumption does not hold. As a consequence, the optimizer abandons the GenerateConstrainedScan rule, but doesn't crash. I did not include a release note since this appears to be a rare bug that we have not encountered in the wild. Fixes #80820 Release note: None Co-authored-by: rharding6373 <rharding6373@users.noreply.github.com> Co-authored-by: Rail Aliiev <rail@iqchoice.com>

Previously, if we ran in a mixed version state with the schema changer workload we could run into an optimizer bug (cockroachdb#80820). To address this, this patch in a mixed version workload disables the insert portion of the workload. Release justification: improves test coverage by enabling the mixed version test Release note: None

Previously, if we ran in a mixed version state with the schema changer workload we could run into an optimizer bug (#80820). To address this, this patch in a mixed version workload disables the insert portion of the workload. Release justification: improves test coverage by enabling the mixed version test Release note: None

Previously, if we ran in a mixed version state with the schema changer workload we could run into an optimizer bug (cockroachdb#80820). To address this, this patch in a mixed version workload disables the insert portion of the workload. Release justification: improves test coverage by enabling the mixed version test Release note: None

blathers-crl bot added the T-sql-queries SQL Queries Team label Apr 30, 2022

rytaft changed the title ~~roachtest: sqlsmith/setup=rand-tables/setting=no-mutations failed~~ sql: internal error: in-between filters didn't yield a constraint May 2, 2022

rytaft removed the release-blocker Indicates a release-blocker. Use with branch-release-2x.x label to denote which branch is blocked. label May 2, 2022

yuzefovich mentioned this issue May 2, 2022

sentry: select_funcs.go:320: in-between filters didn't yield a constraint -- *errutil.leafError: in-between filters didn't yield a constraint (1) select_funcs.go:320: *withstack.withStack (top exception) *ass... #80863

Closed

rharding6373 self-assigned this May 3, 2022

rytaft mentioned this issue May 4, 2022

roachtest: sqlsmith/setup=rand-tables/setting=no-mutations failed #80953

Closed

mgartner mentioned this issue May 9, 2022

roachtest: sqlsmith/setup=rand-tables/setting=no-ddl failed #81130

Closed

This was referenced May 16, 2022

roachtest: silence known sqlsmith error for in-between filters #81291

Merged

roachtest: sqlsmith/setup=rand-tables/setting=no-mutations failed #81263

Closed

ajwerner mentioned this issue May 17, 2022

roachtest: schemachange/random-load failed #81094

Closed

rharding6373 mentioned this issue May 18, 2022

opt: support constrained in between filters with multiple partitioning columns #81456

Open

rharding6373 mentioned this issue May 18, 2022

opt: remove panic when in between filters are not constrained #81465

Merged

craig bot closed this as completed in #81465 May 18, 2022

craig bot closed this as completed in cdbc442 May 18, 2022

jlinder added the sync-me label May 20, 2022

mgartner added this to SQL Queries Jul 24, 2023

mgartner moved this to Done in SQL Queries Jul 24, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sql: internal error: in-between filters didn't yield a constraint #80820

sql: internal error: in-between filters didn't yield a constraint #80820

cockroach-teamcity commented Apr 30, 2022 •

edited by cockroach-jira-scripts

Loading

cockroach-teamcity commented May 1, 2022

rytaft commented May 2, 2022

rharding6373 commented May 5, 2022 •

edited

Loading

ajwerner commented May 17, 2022

rharding6373 commented May 17, 2022

rytaft commented May 18, 2022

sql: internal error: in-between filters didn't yield a constraint #80820

sql: internal error: in-between filters didn't yield a constraint #80820

Comments

cockroach-teamcity commented Apr 30, 2022 • edited by cockroach-jira-scripts Loading

cockroach-teamcity commented May 1, 2022

rytaft commented May 2, 2022

rharding6373 commented May 5, 2022 • edited Loading

ajwerner commented May 17, 2022

rharding6373 commented May 17, 2022

rytaft commented May 18, 2022

cockroach-teamcity commented Apr 30, 2022 •

edited by cockroach-jira-scripts

Loading

rharding6373 commented May 5, 2022 •

edited

Loading