Lines Matching refs:a
4 # a legal notice, here is a blessing:
24 CREATE TABLE t1(a int, b int);
38 execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
41 execsql {SELECT a FROM t1 WHERE b NOT BETWEEN 10 AND 50 ORDER BY a}
44 execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 ORDER BY a}
47 execsql {SELECT a FROM t1 WHERE b NOT BETWEEN a AND a*5 ORDER BY a}
50 execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}
53 execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}
68 execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}
71 execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a}
74 execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a}
77 execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a}
80 execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b}
84 execsql {SELECT a FROM t1 WHERE b IN (b+8,64)}
87 execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}
90 execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}
93 execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}
96 execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}
99 set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]
103 # Testing the IN operator where the right-hand side is a SELECT
107 SELECT a FROM t1
108 WHERE b IN (SELECT b FROM t1 WHERE a<5)
109 ORDER BY a
114 SELECT a FROM t1
115 WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512
116 ORDER BY a
121 SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b
130 WHERE b IN (SELECT b FROM t1 WHERE a>8)
136 DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)
138 execsql {SELECT a FROM t1 ORDER BY a}
142 DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4)
144 execsql {SELECT a FROM t1 ORDER BY a}
147 # Do an IN with a constant RHS but where the RHS has many, many
155 WHERE a IN (
156 'Do','an','IN','with','a','constant','RHS','but','where','the',
167 CREATE TABLE ta(a INTEGER PRIMARY KEY, b);
175 'This is a key that is long enough to require a malloc in the VDBE');
176 SELECT * FROM ta WHERE a<10;
181 CREATE TABLE tb(a INTEGER PRIMARY KEY, b);
189 'This is a key that is long enough to require a malloc in the VDBE');
190 SELECT * FROM tb WHERE a<10;
195 SELECT a FROM ta WHERE b IN (SELECT a FROM tb);
200 SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb);
205 SELECT a FROM ta WHERE b IN (SELECT b FROM tb);
210 SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb);
215 SELECT a FROM ta WHERE a IN (SELECT a FROM tb);
220 SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb);
225 SELECT a FROM ta WHERE a IN (SELECT b FROM tb);
230 SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb);
238 SELECT a FROM t1 WHERE a IN ();
243 SELECT a FROM t1 WHERE a IN (5);
248 SELECT a FROM t1 WHERE a NOT IN () ORDER BY a;
253 SELECT a FROM t1 WHERE a IN (5) AND b IN ();
258 SELECT a FROM t1 WHERE a IN (5) AND b NOT IN ();
263 SELECT a FROM ta WHERE a IN ();
271 SELECT a FROM ta WHERE a NOT IN ();
277 SELECT * FROM ta LEFT JOIN tb ON (ta.b=tb.b) WHERE ta.a IN ();
286 SELECT b FROM t1 WHERE a IN ('hello','there')
292 SELECT b FROM t1 WHERE a IN ("hello",'there')
300 CREATE TABLE t4 AS SELECT a FROM tb;
306 SELECT b FROM t1 WHERE a IN t4;
311 SELECT b FROM t1 WHERE a NOT IN t4;
316 SELECT b FROM t1 WHERE a NOT IN tb;
325 a INTEGER,
326 CHECK( a IN (111,222,333) )
336 } {1 {CHECK constraint failed: a IN (111,222,333)}}
344 CREATE TABLE t6(a,b NUMERIC);
363 # No coercion because column a as affinity NONE
365 SELECT * FROM t6 WHERE a IN ('2');
370 SELECT * FROM t6 WHERE a IN (2);
374 # No coercion because column a as affinity NONE
376 SELECT * FROM t6 WHERE +a IN ('2');
385 CREATE TABLE t2(a, b, c);
386 CREATE TABLE t3(a, b, c);
391 SELECT * FROM t2 WHERE a IN (
392 SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2
398 SELECT * FROM t2 WHERE a IN (
399 SELECT a, b FROM t3 UNION SELECT a, b FROM t2
405 SELECT * FROM t2 WHERE a IN (
406 SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2
412 SELECT * FROM t2 WHERE a IN (
413 SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2
419 SELECT * FROM t2 WHERE a IN (
420 SELECT a, b FROM t3 UNION ALL SELECT a FROM t2
426 SELECT * FROM t2 WHERE a IN (
427 SELECT a, b FROM t3 UNION SELECT a FROM t2
433 SELECT * FROM t2 WHERE a IN (
434 SELECT a, b FROM t3 EXCEPT SELECT a FROM t2
440 SELECT * FROM t2 WHERE a IN (
441 SELECT a, b FROM t3 INTERSECT SELECT a FROM t2
450 SELECT * FROM t2 WHERE a IN (
451 SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
457 SELECT * FROM t2 WHERE a IN (
458 SELECT a FROM t3 UNION SELECT a, b FROM t2
464 SELECT * FROM t2 WHERE a IN (
465 SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
471 SELECT * FROM t2 WHERE a IN (
472 SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
478 SELECT * FROM t2 WHERE a IN (
479 SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2
485 SELECT * FROM t2 WHERE a IN (
486 SELECT a, b FROM t3 UNION ALL SELECT a FROM t2
495 # appears as part of a set of values on the right-hand side of an
498 # When it appears in such a set, NULL is handled as an "unknown value".
508 1 IN (NULL, 1, 2), -- The value 1 is a member of the set, return true.
510 1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.
517 CREATE TABLE t7(a, b, c NOT NULL);
527 execsql { SELECT 2 IN (SELECT a FROM t7) }
530 execsql { SELECT 6 IN (SELECT a FROM t7) }
550 2 NOT IN (SELECT a FROM t7),
551 6 NOT IN (SELECT a FROM t7),
562 SELECT inside.a
573 SELECT inside.a
583 CREATE INDEX i1 ON t7(a);
589 2 IN (SELECT a FROM t7),
590 6 IN (SELECT a FROM t7),
601 2 NOT IN (SELECT a FROM t7),
602 6 NOT IN (SELECT a FROM t7),
613 CREATE TABLE a(id INTEGER);
614 INSERT INTO a VALUES(1);
615 INSERT INTO a VALUES(2);
616 INSERT INTO a VALUES(3);
623 SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
629 SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
644 # At one point the following was causing valgrind to report a "jump
648 CREATE TABLE c1(a);
652 SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1
661 SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3);
665 CREATE TABLE t2(a INTEGER PRIMARY KEY,b);
669 SELECT b, a IN (3,4,5) FROM t2 ORDER BY b;
684 CREATE TABLE t4(a INTEGER PRIMARY KEY, b INT);
686 INSERT INTO t4(a,b) SELECT x, x+100 FROM c;
687 SELECT b FROM t4 WHERE a IN (3,null,8) ORDER BY +b;
690 SELECT b FROM t4 WHERE a NOT IN (3,null,8);
699 SELECT a.*
700 FROM t5 AS 'a' JOIN t5 AS 'b' ON b.id=a.id
721 CREATE TABLE x1(a, b);
722 INSERT INTO x1(a) VALUES(1), (2), (3), (4), (5), (6);
723 CREATE INDEX x1i ON x1(a, b);
728 WHERE a IN (SELECT a FROM x1 WHERE (a%2)==0)
729 ORDER BY a DESC, b;
734 WHERE a IN (SELECT a FROM x1 WHERE (a%7)==0)
735 ORDER BY a DESC, b;