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
|
DROP TABLE dups IF EXISTS;
CREATE TABLE dups(pk INTEGER NOT NULL PRIMARY KEY,val VARCHAR(10) NOT NULL);
INSERT INTO dups VALUES (1, 'first');
INSERT INTO dups VALUES (2, 'second');
INSERT INTO dups VALUES (3, 'third');
INSERT INTO dups VALUES (4, 'first');
INSERT INTO dups VALUES (5, 'first');
INSERT INTO dups VALUES (6, 'second');
SELECT sa.pk FROM dups sa, dups sb WHERE sa.val=sb.val AND sa.pk!=sb.pk;
SELECT a.pk, a.val FROM dups a WHERE a.pk in (1, 2, 4, 5, 6);
SELECT a.pk, a.val FROM dups a WHERE a.pk in (SELECT sa.pk FROM dups sa, dups sb WHERE sa.val=sb.val AND sa.pk!=sb.pk);
-- ALL
/*r
1,first
4,first
5,first
*/SELECT a.pk, a.val FROM dups a WHERE a.val =
ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'first') ORDER BY a.pk;
/*r
1,first
2,second
3,third
4,first
5,first
6,second
*/SELECT a.pk, a.val FROM dups a WHERE a.val >= ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'first') ORDER BY a.pk;
/*r
2,second
3,third
6,second
*/SELECT a.pk, a.val FROM dups a WHERE a.val > ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'first') ORDER BY a.pk;
/*r
1,first
2,second
4,first
5,first
6,second
*/SELECT a.pk, a.val FROM dups a WHERE a.val
<= ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'second') ORDER BY a.pk;
/*r
1,first
2,second
4,first
5,first
6,second
*/SELECT a.pk, a.val FROM dups a WHERE a.val
< ALL(SELECT sa.val FROM dups sa WHERE sa.val = 'third') ORDER BY a.pk;
/*r
1,first
4,first
5,first
*/SELECT a.pk, a.val FROM dups a WHERE a.val
<= ALL(SELECT sa.val FROM dups sa) ORDER BY a.pk;
-- ANY
/*r
1,first
2,second
4,first
5,first
6,second
*/SELECT a.pk, a.val FROM dups a WHERE a.val
< ANY(SELECT sa.val FROM dups sa) ORDER BY a.pk;
/*r
2,second
3,third
6,second
*/SELECT a.pk, a.val FROM dups a WHERE a.val
> ANY(SELECT sa.val FROM dups sa) ORDER BY a.pk;
/*r
1,first
2,second
3,third
4,first
5,first
6,second
*/SELECT a.pk, a.val FROM dups a WHERE a.val
<= ANY(SELECT sa.val FROM dups sa) ORDER BY a.pk;
/*c0*/SELECT a.pk, a.val FROM dups a WHERE a.val
<= ANY(SELECT NULL FROM dups sa) ORDER BY a.pk;
/*c0*/SELECT a.pk, a.val FROM dups a WHERE a.val
<= ANY(SELECT NULL FROM dups sa WHERE sa.val ='fourth') ORDER BY a.pk;
-- non-correlated single value
/*r
1,first
4,first
5,first
*/SELECT a.pk, a.val FROM dups a WHERE a.val
= (SELECT sa.val FROM dups sa WHERE sa.pk = 4) ORDER BY a.pk;
-- bug item #1100384
drop table a if exists;
drop table b if exists;
drop table m if exists;
create table a(a_id integer);
create table b(b_id integer);
create table m(m_a_id integer, m_b_id integer);
insert into a(a_id) values(1);
insert into b(b_id) values(10);
insert into m(m_a_id, m_b_id) values(1, 5);
insert into m(m_a_id, m_b_id) values(1, 10);
insert into m(m_a_id, m_b_id) values(1, 20);
/*r1,10*/select a.a_id, b.b_id from a, b
where a.a_id in (select m.m_a_id from m where b.b_id = m.m_b_id);
/*R1,10*/select a.a_id, b.b_id from a join b on
a.a_id in (select m.m_a_id from m where b.b_id = m.m_b_id);
--
DROP TABLE T IF EXISTS;
CREATE TABLE T(C VARCHAR_IGNORECASE(10));
INSERT INTO T VALUES ('felix');
/*r
felix
*/SELECT * FROM T WHERE C IN ('Felix', 'Feline');
/*c0*/SELECT * FROM T WHERE C IN ('Pink', 'Feline');
DROP TABLE T;
CREATE TABLE T(C CHAR(10));
INSERT INTO T VALUES ('felix');
/*c1*/SELECT * FROM T WHERE C IN ('felix', 'pink');
DROP TABLE T;
|
