← Back to branch summary

~vcs-imports/mammoth-replicator/trunk

« back to all changes in this revision

Viewing changes to src/test/regress/sql/strings.sql

  • Committer: alvherre
  • Date: 2005-12-16 21:24:52 UTC
  • Revision ID: svn-v4:db760fc0-0f08-0410-9d63-cc6633f64896:trunk:1
Initial import of the REL8_0_3 sources from the Pgsql CVS repository.

Show diffs side-by-side

added added

removed removed

Lines of Context:
src/test/regress/sql/strings.sql
 
1
--
 
2
-- STRINGS
 
3
-- Test various data entry syntaxes.
 
4
--
 
5
 
 
6
-- SQL92 string continuation syntax
 
7
-- E021-03 character string literals
 
8
SELECT 'first line'
 
9
' - next line'
 
10
        ' - third line'
 
11
        AS "Three lines to one";
 
12
 
 
13
-- illegal string continuation syntax
 
14
SELECT 'first line'
 
15
' - next line' /* this comment is not allowed here */
 
16
' - third line'
 
17
        AS "Illegal comment within continuation";
 
18
 
 
19
--
 
20
-- test conversions between various string types
 
21
-- E021-10 implicit casting among the character data types
 
22
--
 
23
 
 
24
SELECT CAST(f1 AS text) AS "text(char)" FROM CHAR_TBL;
 
25
 
 
26
SELECT CAST(f1 AS text) AS "text(varchar)" FROM VARCHAR_TBL;
 
27
 
 
28
SELECT CAST(name 'namefield' AS text) AS "text(name)";
 
29
 
 
30
-- since this is an explicit cast, it should truncate w/o error:
 
31
SELECT CAST(f1 AS char(10)) AS "char(text)" FROM TEXT_TBL;
 
32
-- note: implicit-cast case is tested in char.sql
 
33
 
 
34
SELECT CAST(f1 AS char(20)) AS "char(text)" FROM TEXT_TBL;
 
35
 
 
36
SELECT CAST(f1 AS char(10)) AS "char(varchar)" FROM VARCHAR_TBL;
 
37
 
 
38
SELECT CAST(name 'namefield' AS char(10)) AS "char(name)";
 
39
 
 
40
SELECT CAST(f1 AS varchar) AS "varchar(text)" FROM TEXT_TBL;
 
41
 
 
42
SELECT CAST(f1 AS varchar) AS "varchar(char)" FROM CHAR_TBL;
 
43
 
 
44
SELECT CAST(name 'namefield' AS varchar) AS "varchar(name)";
 
45
 
 
46
--
 
47
-- test SQL92 string functions
 
48
-- E### and T### are feature reference numbers from SQL99
 
49
--
 
50
 
 
51
-- E021-09 trim function
 
52
SELECT TRIM(BOTH FROM '  bunch o blanks  ') = 'bunch o blanks' AS "bunch o blanks";
 
53
 
 
54
SELECT TRIM(LEADING FROM '  bunch o blanks  ') = 'bunch o blanks  ' AS "bunch o blanks  ";
 
55
 
 
56
SELECT TRIM(TRAILING FROM '  bunch o blanks  ') = '  bunch o blanks' AS "  bunch o blanks";
 
57
 
 
58
SELECT TRIM(BOTH 'x' FROM 'xxxxxsome Xsxxxxx') = 'some Xs' AS "some Xs";
 
59
 
 
60
-- E021-06 substring expression
 
61
SELECT SUBSTRING('1234567890' FROM 3) = '34567890' AS "34567890";
 
62
 
 
63
SELECT SUBSTRING('1234567890' FROM 4 FOR 3) = '456' AS "456";
 
64
 
 
65
-- T581 regular expression substring (with SQL99's bizarre regexp syntax)
 
66
SELECT SUBSTRING('abcdefg' FROM 'a#"(b_d)#"%' FOR '#') AS "bcd";
 
67
 
 
68
-- No match should return NULL
 
69
SELECT SUBSTRING('abcdefg' FROM '#"(b_d)#"%' FOR '#') IS NULL AS "True";
 
70
 
 
71
-- Null inputs should return NULL
 
72
SELECT SUBSTRING('abcdefg' FROM '(b|c)' FOR NULL) IS NULL AS "True";
 
73
SELECT SUBSTRING(NULL FROM '(b|c)' FOR '#') IS NULL AS "True";
 
74
SELECT SUBSTRING('abcdefg' FROM NULL FOR '#') IS NULL AS "True";
 
75
 
 
76
-- PostgreSQL extension to allow omitting the escape character;
 
77
-- here the regexp is taken as Posix syntax
 
78
SELECT SUBSTRING('abcdefg' FROM 'c.e') AS "cde";
 
79
 
 
80
-- With a parenthesized subexpression, return only what matches the subexpr
 
81
SELECT SUBSTRING('abcdefg' FROM 'b(.*)f') AS "cde";
 
82
 
 
83
 
 
84
-- E021-11 position expression
 
85
SELECT POSITION('4' IN '1234567890') = '4' AS "4";
 
86
 
 
87
SELECT POSITION(5 IN '1234567890') = '5' AS "5";
 
88
 
 
89
-- T312 character overlay function
 
90
SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f";
 
91
 
 
92
SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5) AS "yabadaba";
 
93
 
 
94
SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5 FOR 0) AS "yabadabadoo";
 
95
 
 
96
SELECT OVERLAY('babosa' PLACING 'ubb' FROM 2 FOR 4) AS "bubba";
 
97
 
 
98
--
 
99
-- test LIKE
 
100
-- Be sure to form every test as a LIKE/NOT LIKE pair.
 
101
--
 
102
 
 
103
-- simplest examples
 
104
-- E061-04 like predicate
 
105
SELECT 'hawkeye' LIKE 'h%' AS "true";
 
106
SELECT 'hawkeye' NOT LIKE 'h%' AS "false";
 
107
 
 
108
SELECT 'hawkeye' LIKE 'H%' AS "false";
 
109
SELECT 'hawkeye' NOT LIKE 'H%' AS "true";
 
110
 
 
111
SELECT 'hawkeye' LIKE 'indio%' AS "false";
 
112
SELECT 'hawkeye' NOT LIKE 'indio%' AS "true";
 
113
 
 
114
SELECT 'hawkeye' LIKE 'h%eye' AS "true";
 
115
SELECT 'hawkeye' NOT LIKE 'h%eye' AS "false";
 
116
 
 
117
SELECT 'indio' LIKE '_ndio' AS "true";
 
118
SELECT 'indio' NOT LIKE '_ndio' AS "false";
 
119
 
 
120
SELECT 'indio' LIKE 'in__o' AS "true";
 
121
SELECT 'indio' NOT LIKE 'in__o' AS "false";
 
122
 
 
123
SELECT 'indio' LIKE 'in_o' AS "false";
 
124
SELECT 'indio' NOT LIKE 'in_o' AS "true";
 
125
 
 
126
-- unused escape character
 
127
SELECT 'hawkeye' LIKE 'h%' ESCAPE '#' AS "true";
 
128
SELECT 'hawkeye' NOT LIKE 'h%' ESCAPE '#' AS "false";
 
129
 
 
130
SELECT 'indio' LIKE 'ind_o' ESCAPE '$' AS "true";
 
131
SELECT 'indio' NOT LIKE 'ind_o' ESCAPE '$' AS "false";
 
132
 
 
133
-- escape character
 
134
-- E061-05 like predicate with escape clause
 
135
SELECT 'h%' LIKE 'h#%' ESCAPE '#' AS "true";
 
136
SELECT 'h%' NOT LIKE 'h#%' ESCAPE '#' AS "false";
 
137
 
 
138
SELECT 'h%wkeye' LIKE 'h#%' ESCAPE '#' AS "false";
 
139
SELECT 'h%wkeye' NOT LIKE 'h#%' ESCAPE '#' AS "true";
 
140
 
 
141
SELECT 'h%wkeye' LIKE 'h#%%' ESCAPE '#' AS "true";
 
142
SELECT 'h%wkeye' NOT LIKE 'h#%%' ESCAPE '#' AS "false";
 
143
 
 
144
SELECT 'h%awkeye' LIKE 'h#%a%k%e' ESCAPE '#' AS "true";
 
145
SELECT 'h%awkeye' NOT LIKE 'h#%a%k%e' ESCAPE '#' AS "false";
 
146
 
 
147
SELECT 'indio' LIKE '_ndio' ESCAPE '$' AS "true";
 
148
SELECT 'indio' NOT LIKE '_ndio' ESCAPE '$' AS "false";
 
149
 
 
150
SELECT 'i_dio' LIKE 'i$_d_o' ESCAPE '$' AS "true";
 
151
SELECT 'i_dio' NOT LIKE 'i$_d_o' ESCAPE '$' AS "false";
 
152
 
 
153
SELECT 'i_dio' LIKE 'i$_nd_o' ESCAPE '$' AS "false";
 
154
SELECT 'i_dio' NOT LIKE 'i$_nd_o' ESCAPE '$' AS "true";
 
155
 
 
156
SELECT 'i_dio' LIKE 'i$_d%o' ESCAPE '$' AS "true";
 
157
SELECT 'i_dio' NOT LIKE 'i$_d%o' ESCAPE '$' AS "false";
 
158
 
 
159
-- escape character same as pattern character
 
160
SELECT 'maca' LIKE 'm%aca' ESCAPE '%' AS "true";
 
161
SELECT 'maca' NOT LIKE 'm%aca' ESCAPE '%' AS "false";
 
162
 
 
163
SELECT 'ma%a' LIKE 'm%a%%a' ESCAPE '%' AS "true";
 
164
SELECT 'ma%a' NOT LIKE 'm%a%%a' ESCAPE '%' AS "false";
 
165
 
 
166
SELECT 'bear' LIKE 'b_ear' ESCAPE '_' AS "true";
 
167
SELECT 'bear' NOT LIKE 'b_ear' ESCAPE '_' AS "false";
 
168
 
 
169
SELECT 'be_r' LIKE 'b_e__r' ESCAPE '_' AS "true";
 
170
SELECT 'be_r' NOT LIKE 'b_e__r' ESCAPE '_' AS "false";
 
171
 
 
172
SELECT 'be_r' LIKE '__e__r' ESCAPE '_' AS "false";
 
173
SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true";
 
174
 
 
175
 
 
176
--
 
177
-- test ILIKE (case-insensitive LIKE)
 
178
-- Be sure to form every test as an ILIKE/NOT ILIKE pair.
 
179
--
 
180
 
 
181
SELECT 'hawkeye' ILIKE 'h%' AS "true";
 
182
SELECT 'hawkeye' NOT ILIKE 'h%' AS "false";
 
183
 
 
184
SELECT 'hawkeye' ILIKE 'H%' AS "true";
 
185
SELECT 'hawkeye' NOT ILIKE 'H%' AS "false";
 
186
 
 
187
SELECT 'hawkeye' ILIKE 'H%Eye' AS "true";
 
188
SELECT 'hawkeye' NOT ILIKE 'H%Eye' AS "false";
 
189
 
 
190
SELECT 'Hawkeye' ILIKE 'h%' AS "true";
 
191
SELECT 'Hawkeye' NOT ILIKE 'h%' AS "false";
 
192
 
 
193
--
 
194
-- test implicit type conversion
 
195
--
 
196
 
 
197
-- E021-07 character concatenation
 
198
SELECT 'unknown' || ' and unknown' AS "Concat unknown types";
 
199
 
 
200
SELECT text 'text' || ' and unknown' AS "Concat text to unknown type";
 
201
 
 
202
SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type";
 
203
 
 
204
SELECT text 'text' || char(20) ' and characters' AS "Concat text to char";
 
205
 
 
206
SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar";
 
207
 
 
208
--
 
209
-- test substr with toasted text values
 
210
--
 
211
CREATE TABLE toasttest(f1 text);
 
212
 
 
213
insert into toasttest values(repeat('1234567890',10000));
 
214
insert into toasttest values(repeat('1234567890',10000));
 
215
 
 
216
--
 
217
-- Ensure that some values are uncompressed, to test the faster substring
 
218
-- operation used in that case
 
219
--
 
220
alter table toasttest alter column f1 set storage external;
 
221
insert into toasttest values(repeat('1234567890',10000));
 
222
insert into toasttest values(repeat('1234567890',10000));
 
223
 
 
224
-- If the starting position is zero or less, then return from the start of the string
 
225
-- adjusting the length to be consistent with the "negative start" per SQL92.
 
226
SELECT substr(f1, -1, 5) from toasttest;
 
227
 
 
228
-- If the length is less than zero, an ERROR is thrown.
 
229
SELECT substr(f1, 5, -1) from toasttest;
 
230
 
 
231
-- If no third argument (length) is provided, the length to the end of the
 
232
-- string is assumed.
 
233
SELECT substr(f1, 99995) from toasttest;
 
234
 
 
235
-- If start plus length is > string length, the result is truncated to
 
236
-- string length
 
237
SELECT substr(f1, 99995, 10) from toasttest;
 
238
 
 
239
DROP TABLE toasttest;
 
240
 
 
241
--
 
242
-- test substr with toasted bytea values
 
243
--
 
244
CREATE TABLE toasttest(f1 bytea);
 
245
 
 
246
insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
 
247
insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
 
248
 
 
249
--
 
250
-- Ensure that some values are uncompressed, to test the faster substring
 
251
-- operation used in that case
 
252
--
 
253
alter table toasttest alter column f1 set storage external;
 
254
insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
 
255
insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
 
256
 
 
257
-- If the starting position is zero or less, then return from the start of the string
 
258
-- adjusting the length to be consistent with the "negative start" per SQL92.
 
259
SELECT substr(f1, -1, 5) from toasttest;
 
260
 
 
261
-- If the length is less than zero, an ERROR is thrown.
 
262
SELECT substr(f1, 5, -1) from toasttest;
 
263
 
 
264
-- If no third argument (length) is provided, the length to the end of the
 
265
-- string is assumed.
 
266
SELECT substr(f1, 99995) from toasttest;
 
267
 
 
268
-- If start plus length is > string length, the result is truncated to
 
269
-- string length
 
270
SELECT substr(f1, 99995, 10) from toasttest;
 
271
 
 
272
DROP TABLE toasttest;
 
273
 
 
274
--
 
275
-- test length
 
276
--
 
277
 
 
278
SELECT length('abcdef') AS "length_6";
 
279
 
 
280
--
 
281
-- test strpos
 
282
--
 
283
 
 
284
SELECT strpos('abcdef', 'cd') AS "pos_3";
 
285
 
 
286
SELECT strpos('abcdef', 'xy') AS "pos_0";
 
287
 
 
288
--
 
289
-- test replace
 
290
--
 
291
SELECT replace('abcdef', 'de', '45') AS "abc45f";
 
292
 
 
293
SELECT replace('yabadabadoo', 'ba', '123') AS "ya123da123doo";
 
294
 
 
295
SELECT replace('yabadoo', 'bad', '') AS "yaoo";
 
296
 
 
297
--
 
298
-- test split_part
 
299
--
 
300
select split_part('joeuser@mydatabase','@',0) AS "an error";
 
301
 
 
302
select split_part('joeuser@mydatabase','@',1) AS "joeuser";
 
303
 
 
304
select split_part('joeuser@mydatabase','@',2) AS "mydatabase";
 
305
 
 
306
select split_part('joeuser@mydatabase','@',3) AS "empty string";
 
307
 
 
308
select split_part('@joeuser@mydatabase@','@',2) AS "joeuser";
 
309
 
 
310
--
 
311
-- test to_hex
 
312
--
 
313
select to_hex(256*256*256 - 1) AS "ffffff";
 
314
 
 
315
select to_hex(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "ffffffff";
 
316
 
 
317
--
 
318
-- MD5 test suite - from IETF RFC 1321
 
319
-- (see: ftp://ftp.rfc-editor.org/in-notes/rfc1321.txt)
 
320
--
 
321
select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE";
 
322
 
 
323
select md5('a') = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE";
 
324
 
 
325
select md5('abc') = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE";
 
326
 
 
327
select md5('message digest') = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE";
 
328
 
 
329
select md5('abcdefghijklmnopqrstuvwxyz') = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE";
 
330
 
 
331
select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE";
 
332
 
 
333
select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890') = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE";