[JBoss JIRA] (TEIID-5097) Cannot run time-based queries against Osisoft PI
by Andrej Šmigala (JIRA)
Andrej Šmigala created TEIID-5097:
-------------------------------------
Summary: Cannot run time-based queries against Osisoft PI
Key: TEIID-5097
URL: https://issues.jboss.org/browse/TEIID-5097
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 8.12.x-6.4
Reporter: Andrej Šmigala
Assignee: Steven Hawkins
Priority: Blocker
Osisoft PI supports a relative time literals syntax, e.g.
{code:sql}
select * from dvqe.Data.Archive a where a.time between '*-14d' and '*'
{code}
will select all data between right now and 14 days ago, and
{code:sql}
select * from dvqe.Data.Archive a where a.time > 'y'
{code}
will select all data after yesterday midnight.
The string literals are converted to time values in the PI Server
Running the same queries through teiid however returns incorrect results, because teiid pushes a cast to string on the Time column, which results in string comparison on the datasource:
{code:sql|title=Pushed query}
SELECT TOP 100 cast(g_0.[ElementAttributeID] as String), g_0.[Time] AS c_1, g_0.[Value] AS c_2, g_0.[ValueInt] AS c_3, g_0.
[ValueDbl] AS c_4, g_0.[ValueStr] AS c_5, cast(g_0.[ValueGuid] as String), g_0.[ValueDateTime] AS c_7, g_0.[Status] AS c_8, g_0.[Annotated] AS c_9, g_0.[IsGood] A
S c_10, g_0.[Questionable] AS c_11, g_0.[Substituted] AS c_12 FROM [dvqe].[Data].[Archive] AS g_0 WHERE cast(g_0.[Time] AS String) > 'y'
{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 5 months
[JBoss JIRA] (TEIID-5014) Unable to use parameterized query with node pg module and Teiid/JBoss VDB
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5014?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5014:
---------------------------------
Fix Version/s: (was: 8.12.13.6_3)
> Unable to use parameterized query with node pg module and Teiid/JBoss VDB
> -------------------------------------------------------------------------
>
> Key: TEIID-5014
> URL: https://issues.jboss.org/browse/TEIID-5014
> Project: Teiid
> Issue Type: Bug
> Components: ODBC
> Affects Versions: 8.7.11.6_2
> Environment: Teiid runtime 8.7.11
> JBoss Data Virtualization 6.10
> NodeJS 6.9.4
> node pg 6.4.1
> Mac OSX 10.11.6
> Reporter: Brian M
> Assignee: Steven Hawkins
> Fix For: 10.0, 8.12.x-6.4, 8.7.13.6_2, 9.2.6, 9.3.3
>
>
> When using node pg 6.4.1 in a node application to connect to a JBoss VDB with Teiid, we are unable to successfully return data with a parameterized query.
> Example from node js application (failure):
> client.query("SELECT * FROM records WHERE recordType = $1", ['TYPE1'], function(err, result) { done(); })
> The above code will actually retrieve rows of data, but the connection will be terminated unexpectedly from the JBoss side, thus resulting in a failure. We can confirm though that the parameter is properly inserted into the variable, and rows of data can be retrieved without issue.
> Also, we have confirmed that when sending a static query, we are able to successfully return data without JBoss terminating the connection. We found this approach from the Teiid blog: http://teiid.blogspot.com/2013/02/access-teiid-from-nodejs.html
> Example from node js application (success):
> client.query("SELECT * FROM records WHERE recordType = 'TYPE1'", function(err, result) { done(); })
> Is there something different about a parameterized query vs. a static query that may cause this issue?
> Are you aware of other individuals who have successfully used parameterized queries with the node pg module and Teiid/JBoss VDB?
> Is it possible that how the ODBC settings for Teiid are configured may have an impact on parameterized queries vs. static queries?
> Link: https://teiid.gitbooks.io/documents/content/client-dev/ODBC_Support.html
> Thank you so much for all your help!
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 5 months
[JBoss JIRA] (TEIID-5014) Unable to use parameterized query with node pg module and Teiid/JBoss VDB
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5014?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5014:
---------------------------------
Fix Version/s: 8.7.13.6_2
> Unable to use parameterized query with node pg module and Teiid/JBoss VDB
> -------------------------------------------------------------------------
>
> Key: TEIID-5014
> URL: https://issues.jboss.org/browse/TEIID-5014
> Project: Teiid
> Issue Type: Bug
> Components: ODBC
> Affects Versions: 8.7.11.6_2
> Environment: Teiid runtime 8.7.11
> JBoss Data Virtualization 6.10
> NodeJS 6.9.4
> node pg 6.4.1
> Mac OSX 10.11.6
> Reporter: Brian M
> Assignee: Steven Hawkins
> Fix For: 10.0, 8.12.x-6.4, 8.7.13.6_2, 9.2.6, 9.3.3, 8.12.13.6_3
>
>
> When using node pg 6.4.1 in a node application to connect to a JBoss VDB with Teiid, we are unable to successfully return data with a parameterized query.
> Example from node js application (failure):
> client.query("SELECT * FROM records WHERE recordType = $1", ['TYPE1'], function(err, result) { done(); })
> The above code will actually retrieve rows of data, but the connection will be terminated unexpectedly from the JBoss side, thus resulting in a failure. We can confirm though that the parameter is properly inserted into the variable, and rows of data can be retrieved without issue.
> Also, we have confirmed that when sending a static query, we are able to successfully return data without JBoss terminating the connection. We found this approach from the Teiid blog: http://teiid.blogspot.com/2013/02/access-teiid-from-nodejs.html
> Example from node js application (success):
> client.query("SELECT * FROM records WHERE recordType = 'TYPE1'", function(err, result) { done(); })
> Is there something different about a parameterized query vs. a static query that may cause this issue?
> Are you aware of other individuals who have successfully used parameterized queries with the node pg module and Teiid/JBoss VDB?
> Is it possible that how the ODBC settings for Teiid are configured may have an impact on parameterized queries vs. static queries?
> Link: https://teiid.gitbooks.io/documents/content/client-dev/ODBC_Support.html
> Thank you so much for all your help!
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 5 months
[JBoss JIRA] (TEIID-5094) Is DISTINCT FROM evaluation with two null values
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-5094?page=com.atlassian.jira.plugin... ]
Jan Stastny commented on TEIID-5094:
------------------------------------
[~shawkins] What is the expectation for update count here? Should it reflect only number of rows being changed, effectively meaning only those where
{code:sql}
IF ("new" IS DISTINCT FROM "old")
{code}
holds?
Because currently the update count counts also rows, that haven't changed.
> Is DISTINCT FROM evaluation with two null values
> ------------------------------------------------
>
> Key: TEIID-5094
> URL: https://issues.jboss.org/browse/TEIID-5094
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 10.0, 8.12.x-6.4, 9.3.4, 9.2.7
>
>
> 'row(null) is distinct from row(null)" evaluates to true when false is expected.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 5 months
[JBoss JIRA] (TEIID-5096) Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
by Bram Gadeyne (JIRA)
Bram Gadeyne created TEIID-5096:
-----------------------------------
Summary: Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
Key: TEIID-5096
URL: https://issues.jboss.org/browse/TEIID-5096
Project: Teiid
Issue Type: Bug
Affects Versions: 9.3.3
Reporter: Bram Gadeyne
Assignee: Steven Hawkins
Hi,
I've created a vdb that is depoyed using a vdb-ddl.ddl file.
The deployment just stops and returned no error message when adding the SQL part below. Subsequent deployments are not handles. It seems like the deployment process just hangs. The only way to resolve this was to stop the wildfly server, remove the latest added content from the standalone/data/content directory and restart wildfly.
{code:sql}
CREATE VIEW tv_retrieve_monvals(
admissionid integer not null,
VariableID integer not null,
Datetime timestamp not null,
Entertime timestamp not null,
varvalue double not null,
primary key (admissionid, VariableID, Datetime)
)
AS
SELECT v.PatientID AS admissionid, v.VariableID, v.Datetime, v.Entertime, v."Value" AS varvalue
FROM (
SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
FROM izisprod.P_GeneralData AS gd
INNER JOIN /*+ MAKEDEP */ izisprod.P_MonVals AS mv ON
gd.PatientID = mv.PatientID AND
bitand(mv.Status, 8) = 8 AND
bitand(mv.Status, 2) <> 2
WHERE gd.Status = 1 OR (gd.Status >= 4 AND gd.Status <> 5)
UNION
SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
FROM iziswh.P_GeneralData AS gd
INNER JOIN /*+ MAKEDEP */ iziswh.P_MonVals AS mv ON
gd.PatientID = mv.PatientID AND
bitand(mv.Status, 8) = 8 AND
bitand(mv.Status, 2) <> 2
) AS v
WHERE v.rang = 1;
{code}
Removing the /*+ MAKDEP */ references resolves the issue.
I've tried creating a small ddl script that uses /*+ MAKEDEP */ to reproduce this issue but this script does deploy so currently I can not make a small example.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 5 months
[JBoss JIRA] (TEIID-5093) Amazon s3 csv/xml lookup querry exception
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-5093?page=com.atlassian.jira.plugin... ]
Jan Stastny commented on TEIID-5093:
------------------------------------
[~rareddy]
The thing is that the LOOKUP function is not working.
What I tried before was simple querying of the data. It seems like handling of the LOOKUP function execution is somewhat problematic when working with s3.
> Amazon s3 csv/xml lookup querry exception
> -----------------------------------------
>
> Key: TEIID-5093
> URL: https://issues.jboss.org/browse/TEIID-5093
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.x-6.4
> Reporter: Mario Majernik
> Assignee: Steven Hawkins
>
> Querry :
> {code:java}
> SELECT IntKey, StringKey, lookup('BQT1.SmallB', 'DoubleNum', 'IntKey', IntKey) AS DoubleNum FROM BQT1.SmallA UNION SELECT IntKey, lookup('BQT1.SmallA', 'StringKey', 'IntKey', IntKey) AS StringKey, DoubleNum FROM BQT1.SmallA
> {code}
> returns TeiidSQLException for csv and xml files from amazon s3.
> Stacktrace:
> {code:java}
> WARN [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue41) TEIID30020 Processing exception for request 8O9K6VJIjd9y.7 'TEIID30180 java.sql.SQLException: java.sql.SQLException: TEIID60019 Streaming result has already been read once. Ensure that only one read operation needs to be performed, for example XMLPARSE without the WELLFORMED operation must read the entire stream to validate its contents. Or you may choose to use a non-streaming result. '. Originally TeiidProcessingException BinaryWSProcedureExecution.java:78. Enable more detailed logging to see the entire stacktrace.
> {code}
> Teiid command log for querry:
> {code:java}
> 15:11:34,160 INFO [org.teiid.COMMAND_LOG] (New I/O worker #2) 8O9K6VJIjd9y START USER COMMAND: startTime=2017-10-09 15:11:34.16 requestID=8O9K6VJIjd9y.7 txID=null sessionID=8O9K6VJIjd9y applicationName=JDBC principal=user@teiid-security vdbName=csv vdbVersion=1 sql=SELECT IntKey, StringKey, lookup('BQT1.SmallB', 'DoubleNum', 'IntKey', IntKey) AS DoubleNum FROM BQT1.SmallA UNION SELECT IntKey, lookup('BQT1.SmallA', 'StringKey', 'IntKey', IntKey) AS StringKey, DoubleNum FROM BQT1.SmallA
> 15:11:34,192 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue40) 8O9K6VJIjd9y START DATA SRC COMMAND: startTime=2017-10-09 15:11:34.192 requestID=8O9K6VJIjd9y.7 sourceCommandID=6 executionID=2 txID=null modelName=sourceModel translatorName=user-s3 sessionID=8O9K6VJIjd9y principal=user@teiid-security sql=EXEC sourceModel.getTextFile('csv/smallaCsv.csv')
> 15:11:34,856 DEBUG [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y END SRC COMMAND: endTime=2017-10-09 15:11:34.856 requestID=8O9K6VJIjd9y.7 sourceCommandID=6 executionID=2 txID=null modelName=sourceModel translatorName=user-s3 sessionID=8O9K6VJIjd9y principal=user@teiid-security finalRowCount=1 cpuTime(ns)=34673443
> 15:11:34,862 INFO [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y ERROR USER COMMAND: endTime=2017-10-09 15:11:34.862 requestID=8O9K6VJIjd9y.7 txID=null sessionID=8O9K6VJIjd9y principal=user@teiid-security vdbName=csv vdbVersion=1 finalRowCount=null
> 15:11:34,876 INFO [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y END USER COMMAND: endTime=2017-10-09 15:11:34.876 requestID=8O9K6VJIjd9y.7 txID=null sessionID=8O9K6VJIjd9y principal=user@teiid-security vdbName=csv vdbVersion=1 finalRowCount=0
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 5 months
[JBoss JIRA] (TEIID-4957) Setting Connection Type on VDB of a Domain Managed server gets set back to default after server restart
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4957?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4957:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1461399|https://bugzilla.redhat.com/show_bug.cgi?id=1461399] from NEW to CLOSED
> Setting Connection Type on VDB of a Domain Managed server gets set back to default after server restart
> -------------------------------------------------------------------------------------------------------
>
> Key: TEIID-4957
> URL: https://issues.jboss.org/browse/TEIID-4957
> Project: Teiid
> Issue Type: Bug
> Components: VDB
> Affects Versions: 8.12.10.6_3
> Reporter: Debbie Steigner
> Assignee: Ramesh Reddy
> Fix For: 10.0
>
>
> After deploying a VDB to a managed server group, set the Connection type to anything but the default of By_Version, so None or Any. Now restart the server and you'll see the Connection type is reset to the default.
> What I see is that when the change is made a vdb.xml with that new connection-type is written to the /DVserverhome/domain/servers/server-two/data/teiid-data/SampleVDB_1 folder, this folder is deleted upon a restart though so the change is not kept.
> This only happens in Domain mode, running in Standalone mode saves the change because the teiid-data is not deleted on a restart.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 5 months