JCR-SQl2 测试语句集合

1 # Licensed to the Apache Software Foundation (ASF) under one or more

2 # contributor license agreements.  See the NOTICE file distributed with

3 # this work for additional information regarding copyright ownership.

4 # The ASF licenses this file to You under the Apache License, Version 2.0

5 # (the "License"); you may not use this file except in compliance with

6 # the License.  You may obtain a copy of the License at

7 #

8 #      http://www.apache.org/licenses/LICENSE-2.0

9 #

10 # Unless required by applicable law or agreed to in writing, software

11 # distributed under the License is distributed on an "AS IS" BASIS,

12 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

13 # See the License for the specific language governing permissions and

14 # limitations under the License.

15 
16 # File encoding: UTF-8

17 
18 # See also jcr-spec.pdf

19 
20 select * from test where name=$nt:name

21 
22 # 6.7.1 Query (p 99)

23 select * from test

24 SELECT * FROM TEST

25 SeLeCt * FrOm test

26 select * from test where id=1

27 select * from test where id=1 order by id

28 select * from test order by id

29 
30 # 6.7.2 Source (p 99)

31 # 6.7.3 Selector (p 100)

32 # 6.7.4 Name (p 100)

33 select * from test as t

34 select * from ["Test"]

35 select * from [test]

36 select * from [test] as [t]

37 select * from test as ["t"]

38 select * from ["test"] as ["t"]

39 
40 # 6.7.5 Join (p 102)

41 # 6.7.6 JoinType (p 102)

42 # 6.7.7 JoinCondition (p 103)

43 # 6.7.8 EquiJoinCondition (p 103)

44 select * from parent inner join child on parent.id=child.parentid

45 select * from parent as p inner join child as c on p.id=c.parentid

46 select * from parent as p inner join child as c on p.id=c.parentid

47 select * from parent as p left outer join child as c on p.id=c.parentid

48 select * from parent as p right outer join child as c on p.id=c.parentid

49 
50 # 6.7.9 SameNodeJoinCondition (p 105)

51 select * from parent as p inner join child as c on issamenode(p, c)

52 select * from parent as p inner join child as c on issamenode(p, c, a)

53 select * from parent as p inner join child as c on issamenode(p, c, [/a/b/c])

54 select * from parent as p inner join child as c on issamenode(p, c, ['/a/b/c'])

55 
56 # 6.7.10 ChildNodeJoinCondition (p 106)

57 select * from parent as p inner join child as c on ischildnode(p, c)

58 
59 # 6.7.11 DescendantNodeJoinCondition (p 107)

60 select * from parent as p inner join child as c on isdescendantnode(p, c)

61 select * from parent as p right outer join child as c on p.id=c.parentid inner join other as x on p.id = x.id

62 
63 # 6.7.12 Constraint (p 108)

64 # 6.7.13 And (p 108)

65 # 6.7.14 Or (p 109)

66 select * from test where id<1 and id>1

67 select * from test where id=2 or name='Hello'

68 select * from test where id<1 and (id>1 or id<2)

69 select * from test where (id<1 and id>1) or id<2

70 select * from test where id<1 and id>1 or id<2

71 select * from test where (id<1 or id>1) and id<2

72 select * from test where id<1 or (id>1 and id<2)

73 select * from test where id<1 or id>1 and id<2

74 
75 # 6.7.15 Not (p 110)

76 select * from test where not id=2

77 select * from test where not (id=2 and name='Hello')

78 select * from test where not ([id]=2 and [name]='Hello')

79 select * from test where id=2 or not (name='Hello' and id=3)

80 select * from test where (not name='Hello') and id=3

81 select * from test where id = 3 and (not name='Hello')

82 
83 # 6.7.16 Comparison (p 110)

84 # 6.7.17 Operator (p 112)

85 select * from test where id<=2 or id>=3 and name<'a' or name>'c'

86 select * from test where id<>2

87 select * from [test] where [id]<>2

88 select * from test where name like 'H%'

89 
90 # 6.7.18 PropertyExistence (p 113)

91 select * from test where name is not null

92 select * from test as t where t.name is not null and t.id<>0

93 select * from test as t where not t.name is not null

94 select * from test as t where t.name is null

95 select * from test as t where not t.name is null

96 
97 # 6.7.19 FullTextSearch (p 113)

98 select * from test where contains(name, 'hello -world')

99 select * from test where contains(name, $x)

100 select * from test as t where contains(t.*, 'hello -world')

101 select * from test as t where contains([t].name, 'hello -world')

102 
103 # 6.7.20 SameNode (p 115)

104 select * from test where issamenode([/a/b/c])

105 select * from test as a where issamenode(['/a'])

106 select * from test as x where issamenode(x, ['/a[2]/b/c'])

107 
108 # 6.7.21 ChildNode (p 116)

109 select * from test where ischildnode([/a[1]/b])

110 select * from test as a where ischildnode(['/a'])

111 select * from test as x where ischildnode(x, [/])

112 select * from test as x where ischildnode(x, ['/a[1]'])

113 
114 # 6.7.22 DescendantNode (p 117)

115 # 6.7.23 Path (p 118)

116 select * from test where ISDESCENDANTNODE([/a[1]])

117 select * from test as a where ISDESCENDANTNODE([/a])

118 select * from test as x where ISDESCENDANTNODE(x, [/a/b/c])

119 
120 # 6.7.24 Operand (p 118)

121 # 6.7.25 StaticOperand (p 119)

122 # 6.7.26 DynamicOperand (p 119)

123 # 6.7.27 PropertyValue (p 119)

124 # 6.7.28 Length (p 120)

125 select * from test where length(name)=5

126 select * from test as t where length(t.name)=5

127 select * from test as t where length(name)=5

128 SELECT * FROM [my:thing] WHERE [my:property] = 'abc'

129 SELECT * FROM [my:thing] AS thing WHERE [my:property] = 'abc'

130 SELECT * FROM [my:thing] AS [thing] WHERE [thing].[my:property] = 'abc'

131 
132 # 6.7.29 NodeName (p 121)

133 select * from test where name()='test'

134 select * from test as x where name(x)='test'

135 
136 # 6.7.30 NodeLocalName (p 121)

137 select * from test where localname()='test'

138 select * from test as x where localname(x)='test'

139 
140 # 6.7.31 FullTextSearchScore (p 122)

141 select * from test where score()>4

142 select * from test as x where score(x)<1

143 
144 # 6.7.32 LowerCase (p 122)

145 select * from test where lower(name)='test'

146 select * from test where lower(upper(name))='test'

147 select * from test where lower(localname(test))='test'

148 select * from test where lower(name(test))='test'

149 select * from test as x where lower(x.name)='test'

150 
151 # 6.7.33 UpperCase (p 123)

152 select * from test where upper(name)='test'

153 select * from test where upper(lower(name))='test'

154 select * from test where upper(localname(test))='test'

155 select * from test where upper(name(test))='test'

156 select * from test as x where upper(x.name)='test'

157 
158 # 6.7.34 Literal (p 123)

159 select * from test where amount=0.01

160 select * from test where amount=10.

161 select * from test where amount=.01

162 select * from test where amount=.01e-1

163 select * from test where amount=-.01e1

164 select * from test where amount=-0.01e1

165 select * from test where amount=+10

166 select * from test where amount=-10e10

167 select * from test where amount=+10e-10

168 select * from test where amount=+10e+10

169 select * from test where active=true

170 select * from test where active=false

171 select * from test where name='test''test'

172 select * from test where name=''''

173 select * from test where active="True"

174 select * from test where active=""

175 select * from test where a=cast('0.01' as string)

176 select * from test where a=cast('abcdef' as binary)

177 select * from test where a=cast('[phone]T01:02:03.000Z' as date)

178 select * from test where a=cast('10' as long)

179 select * from test where a=cast('100.5' as double)

180 select * from test where a=cast('3.11' as decimal)

181 select * from test where a=cast('true' as boolean)

182 select * from test where a=cast('firstName' as name)

183 select * from test where a=cast('a/b/c' as path)

184 select * from test where a=cast('[123]' as reference)

185 select * from test where a=cast('[123]' as weakreference)

186 select * from test where a=cast("x://y/z" as uri)

187 
188 # 6.7.35 BindVariable (p 124)

189 select * from test where name=$name

190 select * from test where name=$x and id=$y

191 select * from test where name=$x14

192 select * from test where name=$_

193 
194 # 6.7.36 Prefix (p 124)

195 select * from test where name=$nt:name

196 select * from test where name=$_:name

197 select * from test where name=$_1:name

198 
199 # 6.7.37 Ordering (p 125)

200 # 6.7.38 Order (p 126)

201 select * from test order by name

202 select * from test order by name asc

203 select * from test order by name desc

204 select * from test order by id, name

205 select * from test order by id, name, id, name

206 select * from test order by id desc, name asc, id, name desc

207 select * from test order by id desc, name asc, id asc

208 
209 # 6.7.39 Column (p 127)

210 select name from test

211 select id, name from test

212 select x.id from test as x

213 select x.id, name from test as x

214 select x.id, y.id from test as x inner join test as y on x.id=y.id

215 select x.id as i from test as x inner join test as y on x.id=y.id

216 select x.id as i, y.name as n from test as x inner join test as y on x.id=y.id

217 select x.id, y.name as n from test as x inner join test as y on x.id=y.id

218 select x.* from test as x

219 select x.*, y.* from test as x inner join test as y on x.id=y.id

220 select x.*, x.id as i, y.*, y.name from test as x inner join test as y on x.id=y.id

221 
222 #errors

223 select * from parent as p inner join child as c on issamenode(p, c, a/b)

224 > exception

225 select * from parent as p inner join child as c on issamenode(p, c, d, e)

226 > exception

227 select * from

228 > exception

229 select * from parent as p inner join child as c on ischildnode(p, c, a)

230 > exception

231 select * from parent as p inner join child as c on isdescendantnode(p)

232 > exception

233 select * from parent as p inner join child as c on isdescendantnode(a, b, c)

234 > exception