~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/function.res

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

Lines of Context:
 
1
--------------
 
2
select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2,sign(-5)
 
3
--------------
 
4
 
 
5
1+1     1-1     1+1*2   8/5     8%5     mod(8,5)        mod(8,5)|0      -(1+1)*-2       sign(-5)
 
6
2       0       3       1.60    3       3       3       4       -1
 
7
--------------
 
8
select floor(5.5),floor(-5.5),ceiling(5.5),ceiling(-5.5),round(5.5),round(-5.5)
 
9
--------------
 
10
 
 
11
floor(5.5)      floor(-5.5)     ceiling(5.5)    ceiling(-5.5)   round(5.5)      round(-5.5)
 
12
5       -6      6       -5      6       -6
 
13
--------------
 
14
select abs(-10),log(exp(10)),ln(exp(10)),log2(65535),log(2,65535),exp(log(sqrt(10))*2),pow(10,log10(10)),rand(999999),rand()
 
15
--------------
 
16
 
 
17
abs(-10)        log(exp(10))    ln(exp(10))     log2(65535)     log(2,65535)    exp(log(sqrt(10))*2)    pow(10,log10(10))       rand(999999)    rand()
 
18
10      10.000000       10.000000       2.000000        2.000000        10.000000       10.000000       0.1844  0.7637
 
19
--------------
 
20
select least(6,1.0,2.0),greatest(3,4,5,0)
 
21
--------------
 
22
 
 
23
least(6,1.0,2.0)        greatest(3,4,5,0)
 
24
1.0     5
 
25
--------------
 
26
select 1 | (1+1),5 & 3,bit_count(7)
 
27
--------------
 
28
 
 
29
1 | (1+1)       5 & 3   bit_count(7)
 
30
3       1       3
 
31
--------------
 
32
select 0=0,1>0,1>=1,1<0,1<=0,strcmp("abc","abcd"),strcmp("b","a"),strcmp("a","a")
 
33
--------------
 
34
 
 
35
0=0     1>0     1>=1    1<0     1<=0    strcmp("abc","abcd")    strcmp("b","a") strcmp("a","a")
 
36
1       1       1       0       0       -1      1       0
 
37
--------------
 
38
select "a"<"b","a"<="b","b">="a","b">"a","a"="A","a"<>"b"
 
39
--------------
 
40
 
 
41
"a"<"b" "a"<="b"        "b">="a"        "b">"a" "a"="A" "a"<>"b"
 
42
1       1       1       1       1       1
 
43
--------------
 
44
select "abc" like "a%", "abc" not like "%d%", "ab" like "a\%", "a%" like "a\%","abcd" like "a%b_%d"
 
45
--------------
 
46
 
 
47
"abc" like "a%" "abc" not like "%d%"    "ab" like "a\%" "a%" like "a\%" "abcd" like "a%b_%d"
 
48
1       1       0       1       1
 
49
--------------
 
50
select "Det h�r �r svenska" regexp "h[[:alpha:]]+r", "aba" regexp "^(a|b)*$"
 
51
--------------
 
52
 
 
53
"Det h�r �r svenska" regexp "h[[:alpha:]]+r"    "aba" regexp "^(a|b)*$"
 
54
1       1
 
55
--------------
 
56
select !0,NOT 0=1,!(0=0),1 AND 1,1 && 0,0 OR 1,1 || NULL, 1=1 or 1=1 and 1=0
 
57
--------------
 
58
 
 
59
!0      NOT 0=1 !(0=0)  1 AND 1 1 && 0  0 OR 1  1 || NULL       1=1 or 1=1 and 1=0
 
60
1       1       0       1       0       1       1       1
 
61
--------------
 
62
select IF(0,"ERROR","this"),IF(1,"is","ERROR"),IF(NULL,"ERROR","a"),IF(1,2,3)|0,IF(1,2.0,3.0)+0
 
63
--------------
 
64
 
 
65
IF(0,"ERROR","this")    IF(1,"is","ERROR")      IF(NULL,"ERROR","a")    IF(1,2,3)|0     IF(1,2.0,3.0)+0
 
66
this    is      a       2       2.0
 
67
--------------
 
68
select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3
 
69
--------------
 
70
 
 
71
2 between 1 and 3       "monty" between "max" and "my"  2=2 and "monty" between "max" and "my" and 3=3
 
72
1       1       1
 
73
--------------
 
74
select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0)
 
75
--------------
 
76
 
 
77
2 in (3,2,5,9,5,1)      "monty" in ("david","monty","allan")    1.2 in (1.4,1.2,1.0)
 
78
1       1       1
 
79
--------------
 
80
select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'
 
81
--------------
 
82
 
 
83
hello   'hello' ""hello""       'h'e'l'l'o'     hel"lo  hel'lo
 
84
hello   'hello' ""hello""       'h'e'l'l'o'     hel"lo  hel'lo
 
85
--------------
 
86
select concat("monty"," was here ","again"),length("hello"),ascii("hello")
 
87
--------------
 
88
 
 
89
concat("monty"," was here ","again")    length("hello") ascii("hello")
 
90
monty was here again    5       104
 
91
--------------
 
92
select locate("he","hello"),locate("he","hello",2),locate("lo","hello",2)
 
93
--------------
 
94
 
 
95
locate("he","hello")    locate("he","hello",2)  locate("lo","hello",2)
 
96
1       0       4
 
97
--------------
 
98
select left("hello",2),right("hello",2),substring("hello",2,2),mid("hello",1,5)
 
99
--------------
 
100
 
 
101
left("hello",2) right("hello",2)        substring("hello",2,2)  mid("hello",1,5)
 
102
he      lo      el      hello
 
103
--------------
 
104
select concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1))
 
105
--------------
 
106
 
 
107
concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1))
 
108
happy
 
109
--------------
 
110
select concat("!",ltrim("  left  "),"!",rtrim("  right  "),"!")
 
111
--------------
 
112
 
 
113
concat("!",ltrim("  left  "),"!",rtrim("  right  "),"!")
 
114
!left  !  right!
 
115
--------------
 
116
select insert("txs",2,1,"hi"),insert("is ",4,0,"a"),insert("txxxxt",2,4,"es")
 
117
--------------
 
118
 
 
119
insert("txs",2,1,"hi")  insert("is ",4,0,"a")   insert("txxxxt",2,4,"es")
 
120
this    is a    test
 
121
--------------
 
122
select replace("aaaa","a","b"),replace("aaaa","aa","b"),replace("aaaa","a","bb"),replace("aaaa","","b"),replace("bbbb","a","c")
 
123
--------------
 
124
 
 
125
replace("aaaa","a","b") replace("aaaa","aa","b")        replace("aaaa","a","bb")        replace("aaaa","","b")  replace("bbbb","a","c")
 
126
bbbb    bb      bbbbbbbb        aaaa    bbbb
 
127
--------------
 
128
select replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL")
 
129
--------------
 
130
 
 
131
replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL")
 
132
this is a REAL test
 
133
--------------
 
134
select soundex(""),soundex("he"),soundex("hello all folks")
 
135
--------------
 
136
 
 
137
soundex("")     soundex("he")   soundex("hello all folks")
 
138
        H000    H4142
 
139
--------------
 
140
select password("test")
 
141
--------------
 
142
 
 
143
password("test")
 
144
378b243e220ca493
 
145
--------------
 
146
select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0
 
147
--------------
 
148
 
 
149
0x41    0x41+0  0x41 | 0x7fffffffffffffff | 0   0xffffffffffffffff | 0
 
150
A       65      9223372036854775807     -1
 
151
--------------
 
152
select interval(55,10,20,30,40,50,60,70,80,90,100),interval(3,1,1+1,1+1+1+1),field("IBM","NCA","ICL","SUN","IBM","DIGITAL"),field("A","B","C"),elt(2,"ONE","TWO","THREE"),interval(0,1,2,3,4),elt(1,1,2,3)|0,elt(1,1.1,1.2,1.3)+0
 
153
--------------
 
154
 
 
155
interval(55,10,20,30,40,50,60,70,80,90,100)     interval(3,1,1+1,1+1+1+1)       field("IBM","NCA","ICL","SUN","IBM","DIGITAL")  field("A","B","C")      elt(2,"ONE","TWO","THREE")      interval(0,1,2,3,4)     elt(1,1,2,3)|0  elt(1,1.1,1.2,1.3)+0
 
156
5       2       4       0       TWO     0       1       1.1
 
157
--------------
 
158
select format(1.5555,0),format(123.5555,1),format(1234.5555,2),format(12345.5555,3),format(123456.5555,4),format(1234567.5555,5),format("12345.2399",2)
 
159
--------------
 
160
 
 
161
format(1.5555,0)        format(123.5555,1)      format(1234.5555,2)     format(12345.5555,3)    format(123456.5555,4)   format(1234567.5555,5)  format("12345.2399",2)
 
162
2       123.6   1,234.56        12,345.556      123,456.5555    1,234,567.55550 12,345.24
 
163
--------------
 
164
select database(),user()
 
165
--------------
 
166
 
 
167
database()      user()
 
168
        monty
 
169
--------------
 
170
select null,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null
 
171
--------------
 
172
 
 
173
NULL    isnull(null)    isnull(1/0)     isnull(1/0 = null)      ifnull(null,1)  ifnull(null,"TRUE")     ifnull("TRUE","ERROR")  1/0 is null     1 is not null
 
174
NULL    1       1       1       1       TRUE    TRUE    1       1
 
175
--------------
 
176
select 1 | NULL,1 & NULL,1+NULL,1-NULL
 
177
--------------
 
178
 
 
179
1 | NULL        1 & NULL        1+NULL  1-NULL
 
180
NULL    NULL    NULL    NULL
 
181
--------------
 
182
select NULL=NULL,NULL<>NULL,NULL IS NULL, NULL IS NOT NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0
 
183
--------------
 
184
 
 
185
NULL=NULL       NULL<>NULL      NULL IS NULL    NULL IS NOT NULL        IFNULL(NULL,1.1)+0      IFNULL(NULL,1) | 0
 
186
NULL    NULL    1       0       1.1     1
 
187
--------------
 
188
select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null
 
189
--------------
 
190
 
 
191
strcmp("a",NULL)        (1<NULL)+0.0    NULL regexp "a" null like "a%"  "a%" like null
 
192
NULL    NULL    NULL    NULL    NULL
 
193
--------------
 
194
select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1)
 
195
--------------
 
196
 
 
197
concat("a",NULL)        replace(NULL,"a","b")   replace("string","i",NULL)      replace("string",NULL,"i")      insert("abc",1,1,NULL)  left(NULL,1)
 
198
NULL    NULL    NULL    NULL    NULL    NULL
 
199
--------------
 
200
select field(NULL,"a","b","c")
 
201
--------------
 
202
 
 
203
field(NULL,"a","b","c")
 
204
0
 
205
--------------
 
206
select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null,2 between null and 1,2 between 3 AND NULL
 
207
--------------
 
208
 
 
209
2 between null and 1    2 between 3 AND NULL    NULL between 1 and 2    2 between NULL and 3    2 between 1 AND null    2 between null and 1    2 between 3 AND NULL
 
210
0       0       NULL    NULL    NULL    0       0
 
211
--------------
 
212
select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2)
 
213
--------------
 
214
 
 
215
insert("aa",100,1,"b")  insert("aa",1,3,"b")    left("aa",-1)   substring("a",1,2)
 
216
aa      b               a
 
217
--------------
 
218
select elt(2,1),field(NULL,"a","b","c")
 
219
--------------
 
220
 
 
221
elt(2,1)        field(NULL,"a","b","c")
 
222
NULL    0
 
223
--------------
 
224
select locate("a","b",2),locate("","a",1),ltrim("a"),rtrim("a")
 
225
--------------
 
226
 
 
227
locate("a","b",2)       locate("","a",1)        ltrim("a")      rtrim("a")
 
228
0       1       a       a
 
229
--------------
 
230
select concat("1","2")|0,concat("1",".5")+0.0
 
231
--------------
 
232
 
 
233
concat("1","2")|0       concat("1",".5")+0.0
 
234
12      1.5
 
235
--------------
 
236
select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(curdate()+1)-to_days(curdate()),weekday("1997-01-01")
 
237
--------------
 
238
 
 
239
from_days(to_days("960101"))    to_days(960201)-to_days("19960101")     to_days(curdate()+1)-to_days(curdate()) weekday("1997-01-01")
 
240
1996-01-01      31      1       2
 
241
--------------
 
242
select period_add("9602",-12),period_diff(199505,"9404")
 
243
--------------
 
244
 
 
245
period_add("9602",-12)  period_diff(199505,"9404")
 
246
199502  13
 
247
--------------
 
248
select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now())
 
249
--------------
 
250
 
 
251
now()-now()     weekday(curdate())-weekday(now())       unix_timestamp()-unix_timestamp(now())
 
252
0       0       0
 
253
--------------
 
254
select now(),now()+0,curdate(),weekday(curdate()),weekday(now()),unix_timestamp(),unix_timestamp(now())
 
255
--------------
 
256
 
 
257
now()   now()+0 curdate()       weekday(curdate())      weekday(now())  unix_timestamp()        unix_timestamp(now())
 
258
1998-08-17 04:24:33     19980817042433  1998-08-17      0       0       903317073       903317073