WebSVN – wasCSharpSQLite – Blame – /test/tkt-80e031a00f.test

1

office

1

# 2010 July 14

2

#

3

# The author disclaims copyright to this source code. In place of

4

# a legal notice, here is a blessing:

5

#

6

# May you do good and not evil.

7

# May you find forgiveness for yourself and forgive others.

8

# May you share freely, never taking more than you give.

9

#

10

#***********************************************************************

11

# This file implements regression tests for SQLite library. Specifically,

12

# it tests that ticket [80e031a00f45dca877ed92b225209cfa09280f4f] has been

13

# resolved. That ticket is about IN and NOT IN operators with empty-set

14

# right-hand sides. Such expressions should always return TRUE or FALSE

15

# even if the left-hand side is NULL.

16

#

17

18

set testdir [file dirname $argv0]

19

source $testdir/tester.tcl

20

source $testdir/lock_common.tcl

21

source $testdir/malloc_common.tcl

22

23

# EVIDENCE-OF: R-58875-56087 The IN and NOT IN operators take a single

24

# scalar operand on the left and a vector operand on the right formed by

25

# an explicit list of zero or more scalars or by a single subquery.

26

#

27

# EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the

28

# result of IN is false and the result of NOT IN is true, regardless of

29

# the left operand and even if the left operand is NULL.

30

#

31

# EVIDENCE-OF: R-13595-45863 Note that SQLite allows the parenthesized

32

# list of scalar values on the right-hand side of an IN or NOT IN

33

# operator to be an empty list but most other SQL database database

34

# engines and the SQL92 standard require the list to contain at least

35

# one element.

36

#

37

do_execsql_test tkt-80e031a00f.1 {SELECT 1 IN ()} 0

38

do_execsql_test tkt-80e031a00f.1b {SELECT 1 IN (2)} 0

39

do_execsql_test tkt-80e031a00f.1c {SELECT 1 IN (2,3,4,5,6,7,8,9)} 0

40

do_execsql_test tkt-80e031a00f.2 {SELECT 1 NOT IN ()} 1

41

do_execsql_test tkt-80e031a00f.2b {SELECT 1 NOT IN (2)} 1

42

do_execsql_test tkt-80e031a00f.2c {SELECT 1 NOT IN (2,3,4,5,6,7,8,9)} 1

43

do_execsql_test tkt-80e031a00f.3 {SELECT null IN ()} 0

44

do_execsql_test tkt-80e031a00f.4 {SELECT null NOT IN ()} 1

45

do_execsql_test tkt-80e031a00f.5 {

46

CREATE TABLE t1(x);

47

SELECT 1 IN t1;

48

} 0

49

do_execsql_test tkt-80e031a00f.6 {SELECT 1 NOT IN t1} 1

50

do_execsql_test tkt-80e031a00f.7 {SELECT null IN t1} 0

51

do_execsql_test tkt-80e031a00f.8 {SELECT null NOT IN t1} 1

52

do_execsql_test tkt-80e031a00f.9 {

53

CREATE TABLE t2(y INTEGER PRIMARY KEY);

54

SELECT 1 IN t2;

55

} 0

56

do_execsql_test tkt-80e031a00f.10 {SELECT 1 NOT IN t2} 1

57

do_execsql_test tkt-80e031a00f.11 {SELECT null IN t2} 0

58

do_execsql_test tkt-80e031a00f.12 {SELECT null NOT IN t2} 1

59

do_execsql_test tkt-80e031a00f.13 {

60

CREATE TABLE t3(z INT UNIQUE);

61

SELECT 1 IN t3;

62

} 0

63

do_execsql_test tkt-80e031a00f.14 {SELECT 1 NOT IN t3} 1

64

do_execsql_test tkt-80e031a00f.15 {SELECT null IN t3} 0

65

do_execsql_test tkt-80e031a00f.16 {SELECT null NOT IN t3} 1

66

do_execsql_test tkt-80e031a00f.17 {SELECT 1 IN (SELECT x+y FROM t1, t2)} 0

67

do_execsql_test tkt-80e031a00f.18 {SELECT 1 NOT IN (SELECT x+y FROM t1,t2)} 1

68

do_execsql_test tkt-80e031a00f.19 {SELECT null IN (SELECT x+y FROM t1,t2)} 0

69

do_execsql_test tkt-80e031a00f.20 {SELECT null NOT IN (SELECT x+y FROM t1,t2)} 1

70

do_execsql_test tkt-80e031a00f.21 {SELECT 1.23 IN ()} 0

71

do_execsql_test tkt-80e031a00f.22 {SELECT 1.23 NOT IN ()} 1

72

do_execsql_test tkt-80e031a00f.23 {SELECT 1.23 IN t1} 0

73

do_execsql_test tkt-80e031a00f.24 {SELECT 1.23 NOT IN t1} 1

74

do_execsql_test tkt-80e031a00f.25 {SELECT 'hello' IN ()} 0

75

do_execsql_test tkt-80e031a00f.26 {SELECT 'hello' NOT IN ()} 1

76

do_execsql_test tkt-80e031a00f.27 {SELECT 'hello' IN t1} 0

77

do_execsql_test tkt-80e031a00f.28 {SELECT 'hello' NOT IN t1} 1

78

do_execsql_test tkt-80e031a00f.29 {SELECT x'303132' IN ()} 0

79

do_execsql_test tkt-80e031a00f.30 {SELECT x'303132' NOT IN ()} 1

80

do_execsql_test tkt-80e031a00f.31 {SELECT x'303132' IN t1} 0

81

do_execsql_test tkt-80e031a00f.32 {SELECT x'303132' NOT IN t1} 1

82

83

# EVIDENCE-OF: R-50221-42915 The result of an IN or NOT IN operator is

84

# determined by the following matrix: Left operand is NULL Right operand

85

# contains NULL Right operand is an empty set Left operand found within

86

# right operand Result of IN operator Result of NOT IN operator no no no

87

# no false true does not matter no yes no false true no does not matter

88

# no yes true false no yes no no NULL NULL yes does not matter no does

89

# not matter NULL NULL

90

#

91

# Row 1:

92

do_execsql_test tkt-80e031a00f.100 {SELECT 1 IN (2,3,4)} 0

93

do_execsql_test tkt-80e031a00f.101 {SELECT 1 NOT IN (2,3,4)} 1

94

do_execsql_test tkt-80e031a00f.102 {SELECT 'a' IN ('b','c','d')} 0

95

do_execsql_test tkt-80e031a00f.103 {SELECT 'a' NOT IN ('b','c','d')} 1

96

do_test tkt-80e031a00f.104 {

97

db eval {

98

CREATE TABLE t4(a UNIQUE);

99

CREATE TABLE t5(b INTEGER PRIMARY KEY);

100

CREATE TABLE t6(c);

101

INSERT INTO t4 VALUES(2);

102

INSERT INTO t4 VALUES(3);

103

INSERT INTO t4 VALUES(4);

104

INSERT INTO t5 SELECT * FROM t4;

105

INSERT INTO t6 SELECT * FROM t4;

106

CREATE TABLE t4n(a UNIQUE);

107

CREATE TABLE t6n(c);

108

INSERT INTO t4n SELECT * FROM t4;

109

INSERT INTO t4n VALUES(null);

110

INSERT INTO t6n SELECT * FROM t4n;

111

CREATE TABLE t7(a UNIQUE);

112

CREATE TABLE t8(c);

113

INSERT INTO t7 VALUES('b');

114

INSERT INTO t7 VALUES('c');

115

INSERT INTO t7 VALUES('d');

116

INSERT INTO t8 SELECT * FROM t7;

117

CREATE TABLE t7n(a UNIQUE);

118

CREATE TABLE t8n(c);

119

INSERT INTO t7n SELECT * FROM t7;

120

INSERT INTO t7n VALUES(null);

121

INSERT INTO t8n SELECT * FROM t7n;

122

}

123

execsql {SELECT 1 IN t4}

124

} 0

125

do_execsql_test tkt-80e031a00f.105 {SELECT 1 NOT IN t4} 1

126

do_execsql_test tkt-80e031a00f.106 {SELECT 1 IN t5} 0

127

do_execsql_test tkt-80e031a00f.107 {SELECT 1 NOT IN t5} 1

128

do_execsql_test tkt-80e031a00f.108 {SELECT 1 IN t6} 0

129

do_execsql_test tkt-80e031a00f.109 {SELECT 1 NOT IN t6} 1

130

do_execsql_test tkt-80e031a00f.110 {SELECT 'a' IN t7} 0

131

do_execsql_test tkt-80e031a00f.111 {SELECT 'a' NOT IN t7} 1

132

do_execsql_test tkt-80e031a00f.112 {SELECT 'a' IN t8} 0

133

do_execsql_test tkt-80e031a00f.113 {SELECT 'a' NOT IN t8} 1

134

#

135

# Row 2 is tested by cases 1-32 above.

136

# Row 3:

137

do_execsql_test tkt-80e031a00f.300 {SELECT 2 IN (2,3,4,null)} 1

138

do_execsql_test tkt-80e031a00f.301 {SELECT 3 NOT IN (2,3,4,null)} 0

139

do_execsql_test tkt-80e031a00f.302 {SELECT 4 IN (2,3,4)} 1

140

do_execsql_test tkt-80e031a00f.303 {SELECT 2 NOT IN (2,3,4)} 0

141

do_execsql_test tkt-80e031a00f.304 {SELECT 'b' IN ('b','c','d')} 1

142

do_execsql_test tkt-80e031a00f.305 {SELECT 'c' NOT IN ('b','c','d')} 0

143

do_execsql_test tkt-80e031a00f.306 {SELECT 'd' IN ('b','c',null,'d')} 1

144

do_execsql_test tkt-80e031a00f.307 {SELECT 'b' NOT IN (null,'b','c','d')} 0

145

do_execsql_test tkt-80e031a00f.308 {SELECT 2 IN t4} 1

146

do_execsql_test tkt-80e031a00f.309 {SELECT 3 NOT IN t4} 0

147

do_execsql_test tkt-80e031a00f.310 {SELECT 4 IN t4n} 1

148

do_execsql_test tkt-80e031a00f.311 {SELECT 2 NOT IN t4n} 0

149

do_execsql_test tkt-80e031a00f.312 {SELECT 2 IN t5} 1

150

do_execsql_test tkt-80e031a00f.313 {SELECT 3 NOT IN t5} 0

151

do_execsql_test tkt-80e031a00f.314 {SELECT 2 IN t6} 1

152

do_execsql_test tkt-80e031a00f.315 {SELECT 3 NOT IN t6} 0

153

do_execsql_test tkt-80e031a00f.316 {SELECT 4 IN t6n} 1

154

do_execsql_test tkt-80e031a00f.317 {SELECT 2 NOT IN t6n} 0

155

do_execsql_test tkt-80e031a00f.318 {SELECT 'b' IN t7} 1

156

do_execsql_test tkt-80e031a00f.319 {SELECT 'c' NOT IN t7} 0

157

do_execsql_test tkt-80e031a00f.320 {SELECT 'c' IN t7n} 1

158

do_execsql_test tkt-80e031a00f.321 {SELECT 'd' NOT IN t7n} 0

159

do_execsql_test tkt-80e031a00f.322 {SELECT 'b' IN t8} 1

160

do_execsql_test tkt-80e031a00f.323 {SELECT 'c' NOT IN t8} 0

161

do_execsql_test tkt-80e031a00f.324 {SELECT 'c' IN t8n} 1

162

do_execsql_test tkt-80e031a00f.325 {SELECT 'd' NOT IN t8n} 0

163

#

164

# Row 4:

165

do_execsql_test tkt-80e031a00f.400 {SELECT 1 IN (2,3,4,null)} {{}}

166

do_execsql_test tkt-80e031a00f.401 {SELECT 1 NOT IN (2,3,4,null)} {{}}

167

do_execsql_test tkt-80e031a00f.402 {SELECT 'a' IN ('b','c',null,'d')} {{}}

168

do_execsql_test tkt-80e031a00f.403 {SELECT 'a' NOT IN (null,'b','c','d')} {{}}

169

do_execsql_test tkt-80e031a00f.404 {SELECT 1 IN t4n} {{}}

170

do_execsql_test tkt-80e031a00f.405 {SELECT 5 NOT IN t4n} {{}}

171

do_execsql_test tkt-80e031a00f.406 {SELECT 6 IN t6n} {{}}

172

do_execsql_test tkt-80e031a00f.407 {SELECT 7 NOT IN t6n} {{}}

173

do_execsql_test tkt-80e031a00f.408 {SELECT 'a' IN t7n} {{}}

174

do_execsql_test tkt-80e031a00f.409 {SELECT 'e' NOT IN t7n} {{}}

175

do_execsql_test tkt-80e031a00f.410 {SELECT 'f' IN t8n} {{}}

176

do_execsql_test tkt-80e031a00f.411 {SELECT 'g' NOT IN t8n} {{}}

177

#

178

# Row 5:

179

do_execsql_test tkt-80e031a00f.500 {SELECT null IN (2,3,4,null)} {{}}

180

do_execsql_test tkt-80e031a00f.501 {SELECT null NOT IN (2,3,4,null)} {{}}

181

do_execsql_test tkt-80e031a00f.502 {SELECT null IN (2,3,4)} {{}}

182

do_execsql_test tkt-80e031a00f.503 {SELECT null NOT IN (2,3,4)} {{}}

183

do_execsql_test tkt-80e031a00f.504 {SELECT null IN ('b','c','d')} {{}}

184

do_execsql_test tkt-80e031a00f.505 {SELECT null NOT IN ('b','c','d')} {{}}

185

do_execsql_test tkt-80e031a00f.506 {SELECT null IN ('b','c',null,'d')} {{}}

186

do_execsql_test tkt-80e031a00f.507 {SELECT null NOT IN (null,'b','c','d')} {{}}

187

do_execsql_test tkt-80e031a00f.508 {SELECT null IN t4} {{}}

188

do_execsql_test tkt-80e031a00f.509 {SELECT null NOT IN t4} {{}}

189

do_execsql_test tkt-80e031a00f.510 {SELECT null IN t4n} {{}}

190

do_execsql_test tkt-80e031a00f.511 {SELECT null NOT IN t4n} {{}}

191

do_execsql_test tkt-80e031a00f.512 {SELECT null IN t5} {{}}

192

do_execsql_test tkt-80e031a00f.513 {SELECT null NOT IN t5} {{}}

193

do_execsql_test tkt-80e031a00f.514 {SELECT null IN t6} {{}}

194

do_execsql_test tkt-80e031a00f.515 {SELECT null NOT IN t6} {{}}

195

do_execsql_test tkt-80e031a00f.516 {SELECT null IN t6n} {{}}

196

do_execsql_test tkt-80e031a00f.517 {SELECT null NOT IN t6n} {{}}

197

do_execsql_test tkt-80e031a00f.518 {SELECT null IN t7} {{}}

198

do_execsql_test tkt-80e031a00f.519 {SELECT null NOT IN t7} {{}}

199

do_execsql_test tkt-80e031a00f.520 {SELECT null IN t7n} {{}}

200

do_execsql_test tkt-80e031a00f.521 {SELECT null NOT IN t7n} {{}}

201

do_execsql_test tkt-80e031a00f.522 {SELECT null IN t8} {{}}

202

do_execsql_test tkt-80e031a00f.523 {SELECT null NOT IN t8} {{}}

203

do_execsql_test tkt-80e031a00f.524 {SELECT null IN t8n} {{}}

204

do_execsql_test tkt-80e031a00f.525 {SELECT null NOT IN t8n} {{}}

205

206

finish_test

wasCSharpSQLite – Blame information for rev