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

Commitd841cc4

Browse files
committed
A few regression tests for VALUES, from Gavin Sherry.
1 parent4b8378a commitd841cc4

File tree

6 files changed

+136
-0
lines changed

6 files changed

+136
-0
lines changed

‎src/test/regress/expected/insert.out

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -37,4 +37,21 @@ select * from inserttest;
3737
| 7 | testing
3838
(4 rows)
3939

40+
--
41+
-- VALUES test
42+
--
43+
insert into inserttest values(10, 20, '40'), (-1, 2, DEFAULT),
44+
((select 2), (select i from (values(3)) as foo (i)), 'values are fun!');
45+
select * from inserttest;
46+
col1 | col2 | col3
47+
------+------+-----------------
48+
| 3 | testing
49+
| 5 | testing
50+
| 5 | test
51+
| 7 | testing
52+
10 | 20 | 40
53+
-1 | 2 | testing
54+
2 | 3 | values are fun!
55+
(7 rows)
56+
4057
drop table inserttest;

‎src/test/regress/expected/select.out

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -452,3 +452,64 @@ select foo from (select 'xyzzy',1,null) as foo;
452452
(xyzzy,1,)
453453
(1 row)
454454

455+
--
456+
-- Test VALUES lists
457+
--
458+
select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j)
459+
WHERE onek.unique1 = v.i and onek.stringu1 = v.j;
460+
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i | j
461+
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+--------
462+
147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx | 147 | RFAAAA
463+
931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx | 931 | VJAAAA
464+
(2 rows)
465+
466+
-- a more complex case
467+
-- looks like we're coding lisp :-)
468+
select * from onek,
469+
(values ((select i from
470+
(values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i)
471+
order by i asc limit 1))) bar (i)
472+
where onek.unique1 = bar.i;
473+
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i
474+
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---
475+
2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2
476+
(1 row)
477+
478+
-- try VALUES in a subquery
479+
select * from onek
480+
where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
481+
order by unique1;
482+
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
483+
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
484+
1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
485+
20 | 306 | 0 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 0 | 1 | UAAAAA | ULAAAA | OOOOxx
486+
99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx
487+
(3 rows)
488+
489+
-- VALUES is also legal as a standalone query or a set-operation member
490+
VALUES (1,2), (3,4+4), (7,77.7);
491+
column1 | column2
492+
---------+---------
493+
1 | 2
494+
3 | 8
495+
7 | 77.7
496+
(3 rows)
497+
498+
VALUES (1,2), (3,4+4), (7,77.7)
499+
UNION ALL
500+
SELECT 2+2, 57
501+
UNION ALL
502+
SELECT * FROM int8_tbl;
503+
column1 | column2
504+
------------------+-------------------
505+
1 | 2
506+
3 | 8
507+
7 | 77.7
508+
4 | 57
509+
123 | 456
510+
123 | 4567890123456789
511+
4567890123456789 | 123
512+
4567890123456789 | 4567890123456789
513+
4567890123456789 | -4567890123456789
514+
(9 rows)
515+

‎src/test/regress/expected/update.out

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,18 @@ SELECT * FROM update_test;
3939
10 | 20
4040
(2 rows)
4141

42+
--
43+
-- Test VALUES in FROM
44+
--
45+
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
46+
WHERE update_test.b = v.j;
47+
SELECT * FROM update_test;
48+
a | b
49+
-----+----
50+
100 | 20
51+
100 | 20
52+
(2 rows)
53+
4254
-- if an alias for the target table is specified, don't allow references
4355
-- to the original table name
4456
BEGIN;

‎src/test/regress/sql/insert.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,4 +19,13 @@ insert into inserttest (col1) values (1, 2);
1919
insert into inserttest (col1)values (DEFAULT, DEFAULT);
2020

2121
select*from inserttest;
22+
23+
--
24+
-- VALUES test
25+
--
26+
insert into inserttestvalues(10,20,'40'), (-1,2, DEFAULT),
27+
((select2), (select ifrom (values(3))as foo (i)),'values are fun!');
28+
29+
select*from inserttest;
30+
2231
droptable inserttest;

‎src/test/regress/sql/select.sql

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -110,3 +110,31 @@ SELECT p.name, p.age FROM person* p ORDER BY age using >, name;
110110
select foofrom (select1)as foo;
111111
select foofrom (selectnull)as foo;
112112
select foofrom (select'xyzzy',1,null)as foo;
113+
114+
--
115+
-- Test VALUES lists
116+
--
117+
select*from onek, (values(147,'RFAAAA'), (931,'VJAAAA'))as v (i, j)
118+
WHEREonek.unique1=v.iandonek.stringu1=v.j;
119+
120+
-- a more complex case
121+
-- looks like we're coding lisp :-)
122+
select*from onek,
123+
(values ((select ifrom
124+
(values(10000), (2), (389), (1000), (2000), ((select10029)))as foo(i)
125+
order by iasclimit1))) bar (i)
126+
whereonek.unique1=bar.i;
127+
128+
-- try VALUES in a subquery
129+
select*from onek
130+
where (unique1,ten)in (values (1,1), (20,0), (99,9), (17,99))
131+
order by unique1;
132+
133+
-- VALUES is also legal as a standalone query or a set-operation member
134+
VALUES (1,2), (3,4+4), (7,77.7);
135+
136+
VALUES (1,2), (3,4+4), (7,77.7)
137+
UNION ALL
138+
SELECT2+2,57
139+
UNION ALL
140+
SELECT*FROM int8_tbl;

‎src/test/regress/sql/update.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,15 @@ UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
2525

2626
SELECT*FROM update_test;
2727

28+
--
29+
-- Test VALUES in FROM
30+
--
31+
32+
UPDATE update_testSET a=v.iFROM (VALUES(100,20))AS v(i, j)
33+
WHEREupdate_test.b=v.j;
34+
35+
SELECT*FROM update_test;
36+
2837
-- if an alias for the target table is specified, don't allow references
2938
-- to the original table name
3039
BEGIN;

0 commit comments

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