Project

General

Profile

associated_data_query_weirdness.txt

Derik Barseghian, 06/19/2009 05:02 PM

 
1
Example showing weird behavior of selects on associated_data table...
2

    
3

    
4

    
5
sql> select * from associated_data;
6
DATA_ID                           ID  NAME  VAL                                      WF_EXEC_ID
7
--------------------------------  --  ----  ---------------------------------------  ----------
8
90a78030dfc3bce9891981d3da00a66c   0  type  org.kepler.reporting.roml.ReportLayout            0
9
21f5fa5b8d1134ad420deaa4d72d1162   1  type  org.kepler.reporting.rio.ReportInstance           0
10
afd9cb2707c118957ab28b17fb8f7377   2  type  java.io.File                                      0
11
90a78030dfc3bce9891981d3da00a66c   3  type  org.kepler.reporting.roml.ReportLayout            1
12
a6768a0d6cac98f4f990fd7e148f96c4   4  type  org.kepler.reporting.rio.ReportInstance           1
13
8ec4a8979433724a25484766acec4336   5  type  java.io.File                                      1
14
60c172334743249addf0afcdb2e20895   6  type  org.kepler.reporting.roml.ReportLayout            2
15
73d1d6609851d83979b54e7b94751938   7  type  org.kepler.reporting.rio.ReportInstance           2
16
5ff6e45ddcbb8c029efdd39b84b8d1fd   8  type  java.io.File                                      2
17

    
18
9 rows
19
sql> select * from associated_data where id=8;
20
DATA_ID                           ID  NAME  VAL           WF_EXEC_ID
21
--------------------------------  --  ----  ------------  ----------
22
5ff6e45ddcbb8c029efdd39b84b8d1fd   8  type  java.io.File           2
23
sql> select * from associated_data where wf_exec_id = 2;
24
DATA_ID  ID  NAME  VAL  WF_EXEC_ID
25
-------  --  ----  ---  ----------
26

    
27
0 rows
28
sql> select * from associated_data where wf_exec_id = 0;
29
DATA_ID                           ID  NAME  VAL                                      WF_EXEC_ID
30
--------------------------------  --  ----  ---------------------------------------  ----------
31
21f5fa5b8d1134ad420deaa4d72d1162   1  type  org.kepler.reporting.rio.ReportInstance           0
32
sql> elect * from associated_data where wf_exec_id = 1;
33
SQL Error at 'stdin' line 12:
34
"elect * from associated_data where wf_exec_id = 1"
35
Unexpected token: ELECT in statement [elect]
36
sql> select * from associated_data where wf_exec_id = 1;
37
DATA_ID                           ID  NAME  VAL                                      WF_EXEC_ID
38
--------------------------------  --  ----  ---------------------------------------  ----------
39
90a78030dfc3bce9891981d3da00a66c   3  type  org.kepler.reporting.roml.ReportLayout            1
40
a6768a0d6cac98f4f990fd7e148f96c4   4  type  org.kepler.reporting.rio.ReportInstance           1
41

    
42
2 rows
43
sql> select * from associated_data where wf_exec_id = 2;
44
DATA_ID  ID  NAME  VAL  WF_EXEC_ID
45
-------  --  ----  ---  ----------
46

    
47
0 rows
48
sql> select wf_exec_id from associated_data;
49
0
50
0
51
0
52
1
53
1
54
1
55
2
56
2
57
2
58

    
59
9 rows
60

    
61
sql> \dt
62
ACTOR
63
ACTOR_FIRE
64
ASSOCIATED_DATA
65
DATA
66
DIRECTOR
67
ENTITY
68
PARAMETER
69
PORT
70
PORT_EVENT
71
WORKFLOW
72
WORKFLOW_CHANGE
73
WORKFLOW_EXEC
74

    
75
12 rows
76
sql> select * from workflow;
77
ID  NAME           LSID
78
--  -------------  ----------------------------------------------
79
0  testWorkflow   urn:lsid:gamma.msi.ucsb.edu/OpenAuth/:260:1936
80
1  testWorkflow2  urn:lsid:gamma.msi.ucsb.edu/OpenAuth/:260:2015
81

    
82
2 rows
83

    
84
6 rows
85
sql> select * from associated_data where wf_exec_id LIKE '%0%';
86
DATA_ID                           ID  NAME  VAL                                      WF_EXEC_ID
87
--------------------------------  --  ----  ---------------------------------------  ----------
88
90a78030dfc3bce9891981d3da00a66c   0  type  org.kepler.reporting.roml.ReportLayout            0
89
21f5fa5b8d1134ad420deaa4d72d1162   1  type  org.kepler.reporting.rio.ReportInstance           0
90
afd9cb2707c118957ab28b17fb8f7377   2  type  java.io.File                                      0
91

    
92
3 rows
93
sql> select * from associated_data where wf_exec_id LIKE '%1%';
94
DATA_ID                           ID  NAME  VAL                                      WF_EXEC_ID
95
--------------------------------  --  ----  ---------------------------------------  ----------
96
90a78030dfc3bce9891981d3da00a66c   3  type  org.kepler.reporting.roml.ReportLayout            1
97
a6768a0d6cac98f4f990fd7e148f96c4   4  type  org.kepler.reporting.rio.ReportInstance           1
98
8ec4a8979433724a25484766acec4336   5  type  java.io.File                                      1
99

    
100
3 rows
101
sql> select * from associated_data where wf_exec_id LIKE '%2%';
102
DATA_ID                           ID  NAME  VAL                                      WF_EXEC_ID
103
--------------------------------  --  ----  ---------------------------------------  ----------
104
60c172334743249addf0afcdb2e20895   6  type  org.kepler.reporting.roml.ReportLayout            2
105
73d1d6609851d83979b54e7b94751938   7  type  org.kepler.reporting.rio.ReportInstance           2
106
5ff6e45ddcbb8c029efdd39b84b8d1fd   8  type  java.io.File                                      2