1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
# 2015-06-02
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is type affinity in comparison operations.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix affinity2
do_execsql_test affinity2-100 {
CREATE TABLE t1(
xi INTEGER,
xr REAL,
xb BLOB,
xn NUMERIC,
xt TEXT
);
INSERT INTO t1(rowid,xi,xr,xb,xn,xt) VALUES(1,1,1,1,1,1);
INSERT INTO t1(rowid,xi,xr,xb,xn,xt) VALUES(2,'2','2','2','2','2');
INSERT INTO t1(rowid,xi,xr,xb,xn,xt) VALUES(3,'03','03','03','03','03');
} {}
do_execsql_test affinity2-110 {
SELECT xi, typeof(xi) FROM t1 ORDER BY rowid;
} {1 integer 2 integer 3 integer}
do_execsql_test affinity2-120 {
SELECT xr, typeof(xr) FROM t1 ORDER BY rowid;
} {1.0 real 2.0 real 3.0 real}
do_execsql_test affinity2-130 {
SELECT xb, typeof(xb) FROM t1 ORDER BY rowid;
} {1 integer 2 text 03 text}
do_execsql_test affinity2-140 {
SELECT xn, typeof(xn) FROM t1 ORDER BY rowid;
} {1 integer 2 integer 3 integer}
do_execsql_test affinity2-150 {
SELECT xt, typeof(xt) FROM t1 ORDER BY rowid;
} {1 text 2 text 03 text}
do_execsql_test affinity2-200 {
SELECT rowid, xi==xt, xi==xb, xi==+xt FROM t1 ORDER BY rowid;
} {1 1 1 1 2 1 1 1 3 1 1 1}
do_execsql_test affinity2-210 {
SELECT rowid, xr==xt, xr==xb, xr==+xt FROM t1 ORDER BY rowid;
} {1 1 1 1 2 1 1 1 3 1 1 1}
do_execsql_test affinity2-220 {
SELECT rowid, xn==xt, xn==xb, xn==+xt FROM t1 ORDER BY rowid;
} {1 1 1 1 2 1 1 1 3 1 1 1}
do_execsql_test affinity2-300 {
SELECT rowid, xt==+xi, xt==xi, xt==xb FROM t1 ORDER BY rowid;
} {1 1 1 0 2 1 1 1 3 0 1 1}
#-------------------------------------------------------------------------
do_execsql_test 400 {
CREATE TABLE ttt(c0, c1);
CREATE INDEX ii ON ttt(CAST(c0 AS NUMERIC));
INSERT INTO ttt VALUES('abc', '-1');
}
do_execsql_test 410 {
SELECT * FROM ttt WHERE CAST(c0 AS NUMERIC) > c1 GROUP BY rowid;
} {abc -1}
do_execsql_test 420 {
SELECT * FROM ttt INDEXED BY ii WHERE CAST(c0 AS NUMERIC) > c1 GROUP BY rowid;
} {abc -1}
do_execsql_test 430 {
CREATE TABLE t3(a, b, c INTEGER);
CREATE INDEX t3ac ON t3(a, c-1);
INSERT INTO t3 VALUES(1, 1, 1);
INSERT INTO t3 VALUES(2, 1, 0);
INSERT INTO t3 VALUES(3, 1, 1);
INSERT INTO t3 VALUES(4, 1, 0);
INSERT INTO t3 VALUES(5, 1, 1);
}
do_execsql_test 440 {
SELECT * FROM t3 WHERE c='0' ORDER BY a;
} {2 1 0 4 1 0}
# 2019-08-22 ticket https://sqlite.org/src/info/d99f1ffe836c591ac57f
# False positive in sqlite3ExprNeedsNoAffinityChange()
#
do_execsql_test 500 {
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 TEXT UNIQUE, c1);
INSERT INTO t0(c0) VALUES (-1);
SELECT quote(- x'ce'), quote(t0.c0), quote(- x'ce' >= t0.c0) FROM t0;
} {0 '-1' 1}
do_execsql_test 501 {
SELECT * FROM t0 WHERE - x'ce' >= t0.c0;
} {-1 {}}
do_execsql_test 502 {
SELECT quote(+-+x'ce'), quote(t0.c0), quote(+-+x'ce' >= t0.c0) FROM t0;
} {0 '-1' 1}
do_execsql_test 503 {
SELECT * FROM t0 WHERE +-+x'ce' >= t0.c0;
} {-1 {}}
do_execsql_test 504 {
SELECT quote(- 'ce'), quote(t0.c0), quote(- 'ce' >= t0.c0) FROM t0;
} {0 '-1' 1}
do_execsql_test 505 {
SELECT * FROM t0 WHERE - 'ce' >= t0.c0;
} {-1 {}}
do_execsql_test 506 {
SELECT quote(+-+'ce'), quote(t0.c0), quote(+-+'ce' >= t0.c0) FROM t0;
} {0 '-1' 1}
do_execsql_test 507 {
SELECT * FROM t0 WHERE +-+'ce' >= t0.c0;
} {-1 {}}
# 2019-08-30 ticket https://www.sqlite.org/src/info/40812aea1fde9594
#
# Due to some differences in floating point computations, these tests do not
# work under valgrind.
#
if {![info exists ::G(valgrind)]} {
do_execsql_test 600 {
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 REAL UNIQUE);
INSERT INTO t0(c0) VALUES (3175546974276630385);
SELECT 3175546974276630385 < c0 FROM t0;
} {1}
do_execsql_test 601 {
SELECT 1 FROM t0 WHERE 3175546974276630385 < c0;
} {1}
}
finish_test
|
