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
137
138
139
|
--disable_warnings
DROP TABLE IF EXISTS graph_base;
DROP TABLE IF EXISTS graph;
DROP TABLE IF EXISTS graph2;
--enable_warnings
call mtr.add_suppression("graph_base is open on delete");
CREATE TABLE graph2 (
latch VARCHAR(32) NULL,
origid BIGINT UNSIGNED NULL,
destid BIGINT UNSIGNED NULL,
weight DOUBLE NULL,
seq BIGINT UNSIGNED NULL,
linkid BIGINT UNSIGNED NULL,
KEY (latch, origid, destid) USING HASH,
KEY (latch, destid, origid) USING HASH
) ENGINE=OQGRAPH DATA_TABLE='graph_base' ORIGID='from_id', DESTID='to_id';
# Because the backing store graph_base doesnt exist yet, the select should fail
--error S42S02
SELECT * FROM graph2 WHERE latch='dijkstras' AND origid=1 AND destid=6;
DROP TABLE graph2;
# Create the backing store
CREATE TABLE graph_base (
from_id INT UNSIGNED NOT NULL,
to_id INT UNSIGNED NOT NULL,
PRIMARY KEY (from_id,to_id),
INDEX (to_id)
) ENGINE=MyISAM;
CREATE TABLE graph (
latch VARCHAR(32) NULL,
origid BIGINT UNSIGNED NULL,
destid BIGINT UNSIGNED NULL,
weight DOUBLE NULL,
seq BIGINT UNSIGNED NULL,
linkid BIGINT UNSIGNED NULL,
KEY (latch, origid, destid) USING HASH,
KEY (latch, destid, origid) USING HASH
) ENGINE=OQGRAPH DATA_TABLE='graph_base' ORIGID='from_id', DESTID='to_id';
INSERT INTO graph_base(from_id, to_id) VALUES (1,2), (2,1);
INSERT INTO graph_base(from_id, to_id) VALUES (1,3), (3,1);
INSERT INTO graph_base(from_id, to_id) VALUES (3,4), (4,3);
INSERT INTO graph_base(from_id, to_id) VALUES (5,6), (6,5);
--echo # Expect no result, because of autocast
SELECT * FROM graph WHERE latch=0 ;
SELECT * FROM graph WHERE latch=0 and destid=2 and origid=1;
SELECT * FROM graph WHERE latch=0 and origid=1;
SELECT * FROM graph WHERE latch=0 and destid=1;
SELECT * FROM graph WHERE latch=0 and origid=666;
SELECT * FROM graph WHERE latch=0 and origid is NULL;
SELECT * FROM graph WHERE latch=1 ;
SELECT * FROM graph WHERE latch=1 and destid=2 and origid=1;
SELECT * FROM graph WHERE latch=1 and origid=1;
SELECT * FROM graph WHERE latch=1 and destid=1;
SELECT * FROM graph WHERE latch=1 and origid=666;
SELECT * FROM graph WHERE latch=1 and origid is NULL;
SELECT * FROM graph WHERE latch=2 ;
SELECT * FROM graph WHERE latch=2 and destid=2 and origid=1;
SELECT * FROM graph WHERE latch=2 and origid=1;
SELECT * FROM graph WHERE latch=2 and destid=1;
SELECT * FROM graph WHERE latch=2 and origid=666;
SELECT * FROM graph WHERE latch=2 and origid is NULL;
--echo # Should this return an error? it seems we treat it as just another bogus latch
SELECT * FROM graph WHERE latch='ThisExceeds32Characters456789012';
--echo # Expect no result, because of invalid latch
SELECT * FROM graph WHERE latch='bogus';
SELECT * FROM graph WHERE latch='bogus' and destid=2 and origid=1;
SELECT * FROM graph WHERE latch='bogus' and origid=1;
SELECT * FROM graph WHERE latch='bogus' and destid=1;
SELECT * FROM graph WHERE latch='bogus' and origid=666;
SELECT * FROM graph WHERE latch='bogus' and origid is NULL;
#-- Note the next line couter-intuitively produces no warning
SELECT * FROM graph WHERE latch='666';
SELECT * FROM graph WHERE latch='666' and destid=2 and origid=1;
SELECT * FROM graph WHERE latch='666' and origid=1;
SELECT * FROM graph WHERE latch='666' and destid=1;
SELECT * FROM graph WHERE latch='666' and origid=666;
#-- Note the next line couter-intuitively produces no warning
SELECT * FROM graph WHERE latch='666' and origid is NULL;
SELECT * FROM graph WHERE latch='-1';
SELECT * FROM graph WHERE latch='-1' and destid=2 and origid=1;
SELECT * FROM graph WHERE latch='-1' and origid=1;
SELECT * FROM graph WHERE latch='-1' and destid=1;
SELECT * FROM graph WHERE latch='-1' and origid=666;
SELECT * FROM graph WHERE latch='-1' and origid is NULL;
--echo # Make sure we don't crash if someone passed in a UTF string
#-- Note the next line couter-intuitively produces no warning
SELECT * FROM graph WHERE latch='Ω Ohms Tennis Ball 〄';
SELECT * FROM graph WHERE latch='Ω Ohms Tennis Ball 〄' and destid=2 and origid=1;
SELECT * FROM graph WHERE latch='Ω Ohms Tennis Ball 〄' and origid=1;
SELECT * FROM graph WHERE latch='Ω Ohms Tennis Ball 〄' and destid=1;
SELECT * FROM graph WHERE latch='Ω Ohms Tennis Ball 〄' and origid=666;
#-- Note the next line couter-intuitively produces no warning
SELECT * FROM graph WHERE latch='Ω Ohms Tennis Ball 〄' and origid is NULL;
#--echo # Expect no result, because of NULL latch
#-- FIXME - in v2 according to http://openquery.com/graph/doc NULL latch should
#-- FIXME - return same as select * from graph;
#--https://bugs.launchpad.net/oqgraph/+bug/1196021
--echo # Return all edges when latch is NULL
SELECT * FROM graph WHERE latch is NULL;
SELECT * FROM graph WHERE latch is NULL and destid=2 and origid=1;
SELECT * FROM graph WHERE latch is NULL and origid=1;
SELECT * FROM graph WHERE latch is NULL and destid=1;
SELECT * FROM graph WHERE latch is NULL and origid=666;
SELECT * FROM graph WHERE latch is NULL and origid is NULL;
#-- what happens if we have two links the same? primay key violation...
--error 1062
INSERT INTO graph_base(from_id, to_id) VALUES (1,2);
DELETE FROM graph_base;
#-- Uncomment the following after fixing https://bugs.launchpad.net/oqgraph/+bug/1195735
SELECT * FROM graph;
FLUSH TABLES;
TRUNCATE TABLE graph_base;
#-- Uncomment the following after fixing https://bugs.launchpad.net/oqgraph/+bug/xxxxxxx - Causes the later select to not fail!
#-- For now don't report a separate bug as it may be a manifestation of https://bugs.launchpad.net/oqgraph/+bug/1195735
SELECT * FROM graph;
#-- Expect error if we pull the table out from under
DROP TABLE graph_base;
FLUSH TABLES;
--error S42S02
SELECT * FROM graph WHERE latch='dijkstras' AND origid=1 AND destid=6;
DROP TABLE graph;
|
