Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcbe3732

Browse files
committed
more sanity checks for array_ops
1 parent09fe92a commitcbe3732

File tree

2 files changed

+225
-0
lines changed

2 files changed

+225
-0
lines changed

‎expected/array.out

Lines changed: 186 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,12 @@ CREATE TABLE test_array (
77
);
88
INSERT INTO test_array VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');
99
CREATE INDEX idx_array ON test_array USING rum (i);
10+
SELECT * FROM test_array WHERE i = '{NULL}';
11+
ERROR: array must not contain nulls
12+
SELECT * FROM test_array WHERE i = '{1,2,3,NULL}';
13+
ERROR: array must not contain nulls
14+
SELECT * FROM test_array WHERE i = '{{1,2},{3,4}}';
15+
ERROR: array must have 1 dimension
1016
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
1117
QUERY PLAN
1218
------------------------------------------
@@ -512,3 +518,183 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
512518
Index Cond: (i % '{}'::text[])
513519
(2 rows)
514520

521+
ALTER TABLE test_array ALTER COLUMN i TYPE varchar[];
522+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
523+
QUERY PLAN
524+
-----------------------------------------------
525+
Index Scan using idx_array on test_array
526+
Index Cond: (i = '{}'::character varying[])
527+
(2 rows)
528+
529+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}';
530+
QUERY PLAN
531+
------------------------------------------------
532+
Index Scan using idx_array on test_array
533+
Index Cond: (i && '{}'::character varying[])
534+
(2 rows)
535+
536+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}';
537+
QUERY PLAN
538+
------------------------------------------------
539+
Index Scan using idx_array on test_array
540+
Index Cond: (i @> '{}'::character varying[])
541+
(2 rows)
542+
543+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}';
544+
QUERY PLAN
545+
------------------------------------------------
546+
Index Scan using idx_array on test_array
547+
Index Cond: (i <@ '{}'::character varying[])
548+
(2 rows)
549+
550+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
551+
QUERY PLAN
552+
-----------------------------------------------
553+
Index Scan using idx_array on test_array
554+
Index Cond: (i % '{}'::character varying[])
555+
(2 rows)
556+
557+
ALTER TABLE test_array ALTER COLUMN i TYPE char[];
558+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
559+
QUERY PLAN
560+
------------------------------------------
561+
Index Scan using idx_array on test_array
562+
Index Cond: (i = '{}'::bpchar[])
563+
(2 rows)
564+
565+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}';
566+
QUERY PLAN
567+
------------------------------------------
568+
Index Scan using idx_array on test_array
569+
Index Cond: (i && '{}'::bpchar[])
570+
(2 rows)
571+
572+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}';
573+
QUERY PLAN
574+
------------------------------------------
575+
Index Scan using idx_array on test_array
576+
Index Cond: (i @> '{}'::bpchar[])
577+
(2 rows)
578+
579+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}';
580+
QUERY PLAN
581+
------------------------------------------
582+
Index Scan using idx_array on test_array
583+
Index Cond: (i <@ '{}'::bpchar[])
584+
(2 rows)
585+
586+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
587+
QUERY PLAN
588+
------------------------------------------
589+
Index Scan using idx_array on test_array
590+
Index Cond: (i % '{}'::bpchar[])
591+
(2 rows)
592+
593+
ALTER TABLE test_array ALTER COLUMN i TYPE numeric[] USING i::numeric[];
594+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
595+
QUERY PLAN
596+
------------------------------------------
597+
Index Scan using idx_array on test_array
598+
Index Cond: (i = '{}'::numeric[])
599+
(2 rows)
600+
601+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}';
602+
QUERY PLAN
603+
------------------------------------------
604+
Index Scan using idx_array on test_array
605+
Index Cond: (i && '{}'::numeric[])
606+
(2 rows)
607+
608+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}';
609+
QUERY PLAN
610+
------------------------------------------
611+
Index Scan using idx_array on test_array
612+
Index Cond: (i @> '{}'::numeric[])
613+
(2 rows)
614+
615+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}';
616+
QUERY PLAN
617+
------------------------------------------
618+
Index Scan using idx_array on test_array
619+
Index Cond: (i <@ '{}'::numeric[])
620+
(2 rows)
621+
622+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
623+
QUERY PLAN
624+
------------------------------------------
625+
Index Scan using idx_array on test_array
626+
Index Cond: (i % '{}'::numeric[])
627+
(2 rows)
628+
629+
ALTER TABLE test_array ALTER COLUMN i TYPE float4[] USING i::float4[];
630+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
631+
QUERY PLAN
632+
------------------------------------------
633+
Index Scan using idx_array on test_array
634+
Index Cond: (i = '{}'::real[])
635+
(2 rows)
636+
637+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}';
638+
QUERY PLAN
639+
------------------------------------------
640+
Index Scan using idx_array on test_array
641+
Index Cond: (i && '{}'::real[])
642+
(2 rows)
643+
644+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}';
645+
QUERY PLAN
646+
------------------------------------------
647+
Index Scan using idx_array on test_array
648+
Index Cond: (i @> '{}'::real[])
649+
(2 rows)
650+
651+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}';
652+
QUERY PLAN
653+
------------------------------------------
654+
Index Scan using idx_array on test_array
655+
Index Cond: (i <@ '{}'::real[])
656+
(2 rows)
657+
658+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
659+
QUERY PLAN
660+
------------------------------------------
661+
Index Scan using idx_array on test_array
662+
Index Cond: (i % '{}'::real[])
663+
(2 rows)
664+
665+
ALTER TABLE test_array ALTER COLUMN i TYPE float8[] USING i::float8[];
666+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}';
667+
QUERY PLAN
668+
----------------------------------------------
669+
Index Scan using idx_array on test_array
670+
Index Cond: (i = '{}'::double precision[])
671+
(2 rows)
672+
673+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}';
674+
QUERY PLAN
675+
-----------------------------------------------
676+
Index Scan using idx_array on test_array
677+
Index Cond: (i && '{}'::double precision[])
678+
(2 rows)
679+
680+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}';
681+
QUERY PLAN
682+
-----------------------------------------------
683+
Index Scan using idx_array on test_array
684+
Index Cond: (i @> '{}'::double precision[])
685+
(2 rows)
686+
687+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}';
688+
QUERY PLAN
689+
-----------------------------------------------
690+
Index Scan using idx_array on test_array
691+
Index Cond: (i <@ '{}'::double precision[])
692+
(2 rows)
693+
694+
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}';
695+
QUERY PLAN
696+
----------------------------------------------
697+
Index Scan using idx_array on test_array
698+
Index Cond: (i % '{}'::double precision[])
699+
(2 rows)
700+

‎sql/array.sql

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,10 @@ INSERT INTO test_array VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,
1313

1414
CREATEINDEXidx_arrayON test_array USING rum (i);
1515

16+
SELECT*FROM test_arrayWHERE i='{NULL}';
17+
SELECT*FROM test_arrayWHERE i='{1,2,3,NULL}';
18+
SELECT*FROM test_arrayWHERE i='{{1,2},{3,4}}';
19+
1620
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
1721
SELECT*FROM test_arrayWHERE i='{}';
1822
SELECT*FROM test_arrayWHERE i='{0}';
@@ -100,3 +104,38 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}';
100104
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
101105
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
102106

107+
ALTERTABLE test_array ALTER COLUMN i TYPEvarchar[];
108+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
109+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
110+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
111+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
112+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
113+
114+
ALTERTABLE test_array ALTER COLUMN i TYPEchar[];
115+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
116+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
117+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
118+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
119+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
120+
121+
ALTERTABLE test_array ALTER COLUMN i TYPEnumeric[] USING i::numeric[];
122+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
123+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
124+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
125+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
126+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
127+
128+
ALTERTABLE test_array ALTER COLUMN i TYPE float4[] USING i::float4[];
129+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
130+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
131+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
132+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
133+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
134+
135+
ALTERTABLE test_array ALTER COLUMN i TYPE float8[] USING i::float8[];
136+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i='{}';
137+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{}';
138+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i @>'{}';
139+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i<@'{}';
140+
EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i %'{}';
141+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp