xref: /illumos-gate/usr/src/lib/libsqlite/test/expr.test

22 execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
30 test_expr expr-1.1 {i1=10, i2=20} {i1+i2} 30
31 test_expr expr-1.2 {i1=10, i2=20} {i1-i2} -10
32 test_expr expr-1.3 {i1=10, i2=20} {i1*i2} 200
33 test_expr expr-1.4 {i1=10, i2=20} {i1/i2} 0.5
34 test_expr expr-1.5 {i1=10, i2=20} {i2/i1} 2
35 test_expr expr-1.6 {i1=10, i2=20} {i2<i1} 0
36 test_expr expr-1.7 {i1=10, i2=20} {i2<=i1} 0
37 test_expr expr-1.8 {i1=10, i2=20} {i2>i1} 1
38 test_expr expr-1.9 {i1=10, i2=20} {i2>=i1} 1
39 test_expr expr-1.10 {i1=10, i2=20} {i2!=i1} 1
40 test_expr expr-1.11 {i1=10, i2=20} {i2=i1} 0
41 test_expr expr-1.12 {i1=10, i2=20} {i2<>i1} 1
42 test_expr expr-1.13 {i1=10, i2=20} {i2==i1} 0
43 test_expr expr-1.14 {i1=20, i2=20} {i2<i1} 0
44 test_expr expr-1.15 {i1=20, i2=20} {i2<=i1} 1
45 test_expr expr-1.16 {i1=20, i2=20} {i2>i1} 0
46 test_expr expr-1.17 {i1=20, i2=20} {i2>=i1} 1
47 test_expr expr-1.18 {i1=20, i2=20} {i2!=i1} 0
48 test_expr expr-1.19 {i1=20, i2=20} {i2=i1} 1
49 test_expr expr-1.20 {i1=20, i2=20} {i2<>i1} 0
50 test_expr expr-1.21 {i1=20, i2=20} {i2==i1} 1
51 test_expr expr-1.22 {i1=1, i2=2, r1=3.0} {i1+i2*r1} {7}
52 test_expr expr-1.23 {i1=1, i2=2, r1=3.0} {(i1+i2)*r1} {9}
53 test_expr expr-1.24 {i1=1, i2=2} {min(i1,i2,i1+i2,i1-i2)} {-1}
54 test_expr expr-1.25 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
55 test_expr expr-1.26 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
56 test_expr expr-1.27 {i1=1, i2=2} {i1==1 AND i2=2} {1}
57 test_expr expr-1.28 {i1=1, i2=2} {i1=2 AND i2=1} {0}
58 test_expr expr-1.29 {i1=1, i2=2} {i1=1 AND i2=1} {0}
59 test_expr expr-1.30 {i1=1, i2=2} {i1=2 AND i2=2} {0}
60 test_expr expr-1.31 {i1=1, i2=2} {i1==1 OR i2=2} {1}
61 test_expr expr-1.32 {i1=1, i2=2} {i1=2 OR i2=1} {0}
62 test_expr expr-1.33 {i1=1, i2=2} {i1=1 OR i2=1} {1}
63 test_expr expr-1.34 {i1=1, i2=2} {i1=2 OR i2=2} {1}
64 test_expr expr-1.35 {i1=1, i2=2} {i1-i2=-1} {1}
65 test_expr expr-1.36 {i1=1, i2=0} {not i1} {0}
66 test_expr expr-1.37 {i1=1, i2=0} {not i2} {1}
67 test_expr expr-1.38 {i1=1} {-i1} {-1}
68 test_expr expr-1.39 {i1=1} {+i1} {1}
69 test_expr expr-1.40 {i1=1, i2=2} {+(i2+i1)} {3}
70 test_expr expr-1.41 {i1=1, i2=2} {-(i2+i1)} {-3}
71 test_expr expr-1.42 {i1=1, i2=2} {i1|i2} {3}
72 test_expr expr-1.42b {i1=1, i2=2} {4|2} {6}
73 test_expr expr-1.43 {i1=1, i2=2} {i1&i2} {0}
74 test_expr expr-1.43b {i1=1, i2=2} {4&5} {4}
75 test_expr expr-1.44 {i1=1} {~i1} {-2}
76 test_expr expr-1.45 {i1=1, i2=3} {i1<<i2} {8}
77 test_expr expr-1.46 {i1=32, i2=3} {i1>>i2} {4}
78 test_expr expr-1.47 {i1=9999999999, i2=8888888888} {i1<i2} 0
79 test_expr expr-1.48 {i1=9999999999, i2=8888888888} {i1=i2} 0
80 test_expr expr-1.49 {i1=9999999999, i2=8888888888} {i1>i2} 1
81 test_expr expr-1.50 {i1=99999999999, i2=99999999998} {i1<i2} 0
82 test_expr expr-1.51 {i1=99999999999, i2=99999999998} {i1=i2} 0
83 test_expr expr-1.52 {i1=99999999999, i2=99999999998} {i1>i2} 1
84 test_expr expr-1.53 {i1=099999999999, i2=99999999999} {i1<i2} 0
85 test_expr expr-1.54 {i1=099999999999, i2=99999999999} {i1=i2} 1
86 test_expr expr-1.55 {i1=099999999999, i2=99999999999} {i1>i2} 0
87 test_expr expr-1.56 {i1=25, i2=11} {i1%i2} 3
88 test_expr expr-1.58 {i1=NULL, i2=1} {coalesce(i1+i2,99)} 99
89 test_expr expr-1.59 {i1=1, i2=NULL} {coalesce(i1+i2,99)} 99
90 test_expr expr-1.60 {i1=NULL, i2=NULL} {coalesce(i1+i2,99)} 99
91 test_expr expr-1.61 {i1=NULL, i2=1} {coalesce(i1-i2,99)} 99
92 test_expr expr-1.62 {i1=1, i2=NULL} {coalesce(i1-i2,99)} 99
93 test_expr expr-1.63 {i1=NULL, i2=NULL} {coalesce(i1-i2,99)} 99
94 test_expr expr-1.64 {i1=NULL, i2=1} {coalesce(i1*i2,99)} 99
95 test_expr expr-1.65 {i1=1, i2=NULL} {coalesce(i1*i2,99)} 99
96 test_expr expr-1.66 {i1=NULL, i2=NULL} {coalesce(i1*i2,99)} 99
97 test_expr expr-1.67 {i1=NULL, i2=1} {coalesce(i1/i2,99)} 99
98 test_expr expr-1.68 {i1=1, i2=NULL} {coalesce(i1/i2,99)} 99
99 test_expr expr-1.69 {i1=NULL, i2=NULL} {coalesce(i1/i2,99)} 99
100 test_expr expr-1.70 {i1=NULL, i2=1} {coalesce(i1<i2,99)} 99
101 test_expr expr-1.71 {i1=1, i2=NULL} {coalesce(i1>i2,99)} 99
102 test_expr expr-1.72 {i1=NULL, i2=NULL} {coalesce(i1<=i2,99)} 99
103 test_expr expr-1.73 {i1=NULL, i2=1} {coalesce(i1>=i2,99)} 99
104 test_expr expr-1.74 {i1=1, i2=NULL} {coalesce(i1!=i2,99)} 99
105 test_expr expr-1.75 {i1=NULL, i2=NULL} {coalesce(i1==i2,99)} 99
106 test_expr expr-1.76 {i1=NULL, i2=NULL} {coalesce(not i1,99)} 99
107 test_expr expr-1.77 {i1=NULL, i2=NULL} {coalesce(-i1,99)} 99
108 test_expr expr-1.78 {i1=NULL, i2=NULL} {coalesce(i1 IS NULL AND i2=5,99)} 99
109 test_expr expr-1.79 {i1=NULL, i2=NULL} {coalesce(i1 IS NULL OR i2=5,99)} 1
110 test_expr expr-1.80 {i1=NULL, i2=NULL} {coalesce(i1=5 AND i2 IS NULL,99)} 99
111 test_expr expr-1.81 {i1=NULL, i2=NULL} {coalesce(i1=5 OR i2 IS NULL,99)} 1
112 test_expr expr-1.82 {i1=NULL, i2=3} {coalesce(min(i1,i2,1),99)} 99
113 test_expr expr-1.83 {i1=NULL, i2=3} {coalesce(max(i1,i2,1),99)} 99
114 test_expr expr-1.84 {i1=3, i2=NULL} {coalesce(min(i1,i2,1),99)} 99
115 test_expr expr-1.85 {i1=3, i2=NULL} {coalesce(max(i1,i2,1),99)} 99
116 test_expr expr-1.86 {i1=3, i2=8} {5 between i1 and i2} 1
117 test_expr expr-1.87 {i1=3, i2=8} {5 not between i1 and i2} 0
118 test_expr expr-1.88 {i1=3, i2=8} {55 between i1 and i2} 0
119 test_expr expr-1.89 {i1=3, i2=8} {55 not between i1 and i2} 1
120 test_expr expr-1.90 {i1=3, i2=NULL} {5 between i1 and i2} {{}}
121 test_expr expr-1.91 {i1=3, i2=NULL} {5 not between i1 and i2} {{}}
122 test_expr expr-1.92 {i1=3, i2=NULL} {2 between i1 and i2} 0
123 test_expr expr-1.93 {i1=3, i2=NULL} {2 not between i1 and i2} 1
124 test_expr expr-1.94 {i1=NULL, i2=8} {2 between i1 and i2} {{}}
125 test_expr expr-1.95 {i1=NULL, i2=8} {2 not between i1 and i2} {{}}
126 test_expr expr-1.94 {i1=NULL, i2=8} {55 between i1 and i2} 0
127 test_expr expr-1.95 {i1=NULL, i2=8} {55 not between i1 and i2} 1
128 test_expr expr-1.96 {i1=NULL, i2=3} {coalesce(i1<<i2,99)} 99
129 test_expr expr-1.97 {i1=32, i2=NULL} {coalesce(i1>>i2,99)} 99
130 test_expr expr-1.98 {i1=NULL, i2=NULL} {coalesce(i1|i2,99)} 99
131 test_expr expr-1.99 {i1=32, i2=NULL} {coalesce(i1&i2,99)} 99
132 test_expr expr-1.100 {i1=1, i2=''} {i1=i2} 0
133 test_expr expr-1.101 {i1=0, i2=''} {i1=i2} 0
399 test_expr expr-case.1 {i1=1, i2=2} \
400 	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne
401 test_expr expr-case.2 {i1=2, i2=2} \
402 	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} eq
403 test_expr expr-case.3 {i1=NULL, i2=2} \
404 	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne
405 test_expr expr-case.4 {i1=2, i2=NULL} \
406 	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne
407 test_expr expr-case.5 {i1=2} \
408 	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} two
409 test_expr expr-case.6 {i1=1} \
410 	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} one
411 test_expr expr-case.7 {i1=2} \
412 	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} error
413 test_expr expr-case.8 {i1=3} \
414 	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} error
415 test_expr expr-case.9 {i1=3} \
416 	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} error
417 test_expr expr-case.10 {i1=3} \
418 	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END} {{}}
419 test_expr expr-case.11 {i1=null} \
420 	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 3 END} 3
421 test_expr expr-case.12 {i1=1} \
422 	{CASE i1 WHEN 1 THEN null WHEN 2 THEN 'two' ELSE 3 END} {{}}
423 test_expr expr-case.13 {i1=7} \
424 	{ CASE WHEN i1 < 5 THEN 'low'
425 	       WHEN i1 < 10 THEN 'medium'
426                WHEN i1 < 15 THEN 'high' ELSE 'error' END} medium

Last Index update Wed Apr 24 02:18:52 UTC 2024