sql: oom in ilike operator due to huge operand

[cockroach-teamcity]

roachtest.unoptimized-query-oracle/disable-rules=half failed with artifacts on release-22.2 @ 2c1f4db5d31e18a718b974f992701dfee9a8cdd8:

test artifacts and logs in: /artifacts/unoptimized-query-oracle/disable-rules=half/run_1
	query_comparison_util.go:237,query_comparison_util.go:66,unoptimized_query_oracle.go:54,test_runner.go:930: failed to set random seed. 31565 statements run: dial tcp 35.231.144.250:26257: connect: connection refused
		(1) attached stack trace
		  -- stack trace:
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.(*queryComparisonHelper).makeError
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:345
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runUnoptimizedQueryOracleImpl
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:102
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.registerUnoptimizedQueryOracle.func1.1
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:57
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runOneRoundQueryComparison
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:236
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runQueryComparison
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:66
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.registerUnoptimizedQueryOracle.func1
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:54
		  | main.(*testRunner).runTest.func2
		  | 	main/pkg/cmd/roachtest/test_runner.go:930
		  | runtime.goexit
		  | 	GOROOT/src/runtime/asm_amd64.s:1594
		Wraps: (2) failed to set random seed. 31565 statements run
		Wraps: (3) dial tcp 35.231.144.250:26257
		Wraps: (4) connect
		Wraps: (5) connection refused
		Error types: (1) *withstack.withStack (2) *errutil.withPrefix (3) *net.OpError (4) *os.SyscallError (5) syscall.Errno

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_encrypted=false , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

Same failure on other branches

• roachtest: unoptimized-query-oracle/disable-rules=half failed #89617 roachtest: unoptimized-query-oracle/disable-rules=half failed [C-test-failure O-roachtest O-robot T-sql-queries branch-master]

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-20416

[msirek]

Reproduction:

CREATE TABLE table11 (
      col1_0 INT4 NULL,
      col1_1 OID NULL
);

insert into table11 values (616853617, NULL);

SELECT
        '':::VOID AS col_84422
FROM
        defaultdb.public.table11 AS tab_40339
WHERE
        'a ':::STRING ILIKE rpad('a':::STRING::STRING, tab_40339.col1_0::INT8)::STRING;

ERROR: rpad(): requested length too large, exceeds 128 MiB
SQLSTATE: 54000

[msirek]

@michae2 Let me know if you think the work you're planning to make query-oracle tests not halt when one of the statements errors would cover this issue? The erroring query in this case is only executed once, so I don't know if what you're planning would help this case.

[michae2]

Something strange is happening here. From dmesg.txt:

[ 5832.847028] oom_reaper: reaped process 29407 (cockroach), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB

So this was an OOM. I haven't been able to reproduce the OOM (or high memory usage) yet.

[michae2]

Ok, here's a repro of the OOM:

CREATE TABLE t (i) AS SELECT 82610433;
SELECT * FROM t WHERE 'a' ILIKE rpad('a', i);

This uses something like 13 GiB on my laptop. The problem is that 82610433 is below the rpad limit (128 MiB) but still way too high to compile into a regex for ILIKE. I think we need some more conservative limit on ILIKE and LIKE (or lower down, in the regex cache).

[michae2]

#89617 is the same but with lpad and !~ instead of rpad and ILIKE.

[mgartner]

In the case where there are no wildcard characters (% and _) on the RHS of the ILIKE, I think we can avoid compiling a regex altogether.

Even with the wildcards, there's some solution with reasonable space/time complexity because Postgres manages:

marcus=# CREATE TABLE t (i) AS SELECT 82610433;
SELECT 1

marcus=# SELECT * FROM t WHERE 'a' ILIKE rpad('%', i);
 i
---
(0 rows)

Time: 612.724 ms

[michae2]

(Note this also reproduces on 22.1 and 21.2.)

[msirek]

Fixed by #105863