[JBoss JIRA] (TEIID-4021) MSSQL Pagination
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-4021?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-4021:
----------------------------------
The problem is reproduced when having 2 Teiid models, one connected on MSSQL 2012 and the second on MSSQL 2008.
If the first query is executed on MSSQL 2012, Teiid is considering globally that all other MSSQL connections are 2012 and thus the exception sent above is reproduced when running a query on MSSQL 2008 afterward.
If first query is …
[View More]executed on MSSQL 2008, it executes successfully.
> MSSQL Pagination
> ----------------
>
> Key: TEIID-4021
> URL: https://issues.jboss.org/browse/TEIID-4021
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> I'm using Teiid 8.11.3 with mssql translator.
> I have a huge table over sql server 2008 on which i'm applying pagination.
> I noticed slowliness in the query execution over this table at each time I increase the pagination.
> I monitored the JDBC queries execute by teiid and found out that it is using top n according to the limit used into the query.
> For example:
> in teiid : Select * from mytable LIMIT 90 , 10
> is translated in JDBC: select top 100 from mytable
> Since my table contains millions of records, when fetching the last page, the executed jdbc query is retrieving the whole table top n, to return in result the last 10 records. And the execution is taking too much time.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
[View Less]
8 years, 7 months
[JBoss JIRA] (TEIID-2578) add/remove schema elements
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-2578?page=com.atlassian.jira.plugin... ]
Kylin Soong edited comment on TEIID-2578 at 8/28/16 11:10 PM:
--------------------------------------------------------------
h2. 1. Unit test error
The unit test still have some error
{code}
Tests in error:
TestMetadataValidator.testCreateTrigger:142->buildModel:71 » Metadata TEIID312...
TestMetadataValidator.testCreateTriggerFails:153->buildModel:71 » Metadata TEI...
TestMetadataValidator.…
[View More]testExternalMaterializationValidate:327->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateColumnTypes:365->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateColumns:347->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateLoadScripts:411->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateMissingColumns:395->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateMissingStatus:380->buildModel:71 » Metadata
TestMetadataValidator.testInternalMaterializationValidate:309->buildModel:71 » Metadata
TestMetadataValidator.testModelArtifactsSucess:108->buildModel:71 » Metadata T...
TestMetadataValidator.testProcMetadata:178->buildModel:71 » Metadata TEIID3123...
TestMetadataValidator.testProcMetadataValidationError:187->buildModel:71 » Metadata
TestMetadataValidator.testResolveMetadata:130->buildModel:71 » Metadata TEIID3...
TestMetadataValidator.testResolveMetadataError:206->buildModel:71 » Metadata T...
TestMetadataValidator.testResolveTempMetadata:197->buildModel:71 » Metadata TE...
TestMetadataValidator.testViewKeys:488->buildModel:71 » Metadata TEIID31236 No...
TestMetadataValidator.testViewModelArtifacts:100->buildModel:71 » Metadata TEI...
TestDDLParser.testAlterDatabase:949->helpParse:763 » Metadata TEIID31214 DataW...
TestDDLParser.testAlterProcedureOptions:886->helpParse:747 » Parse TEIID30386 ...
TestDDLParser.testAlterTableAddColumnOptions:844->helpParse:747 » Metadata TEI...
TestDDLParser.testAlterTableAddOptions:798->helpParse:747 » Metadata TEIID3035...
TestDDLParser.testAlterTableDropOptions:828->helpParse:747 » Metadata TEIID303...
TestDDLParser.testAlterTableModifyOptions:813->helpParse:747 » Metadata TEIID3...
TestDDLParser.testAlterTableRemoveColumnOptions:864->helpParse:747 » Parse TEI...
TestDDLParser.testAlterView:1114->helpParse:763 » Metadata TEIID30357 G1 does ...
Tests run: 6043, Failures: 0, Errors: 25, Skipped: 65
{code}
h2. 2. create database failed
{code}
/subsystem=teiid:db-store(ddl="create database foo")
{
"outcome" => "failed",
"result" => undefined,
"failure-description" => "WFLYCTL0158: Operation handler failed: org.jboss.msc.service.ServiceNotFoundException: Service service jboss.teiid.database-storage not found",
"rolled-back" => true
}
{code}
The stacktrace in server
{code}
11:07:59,592 ERROR [org.jboss.as.controller.management-operation] (management-handler-thread - 19) WFLYCTL0013: Operation ("db-store") failed - address: ([("subsystem" => "teiid")]): org.jboss.msc.service.ServiceNotFoundException: Service service jboss.teiid.database-storage not found
at org.jboss.msc.service.ServiceContainerImpl.getRequiredService(ServiceContainerImpl.java:669)
at org.jboss.as.controller.OperationContextImpl$OperationContextServiceRegistry.getRequiredService(OperationContextImpl.java:2211)
at org.teiid.jboss.TeiidOperationHandler.getDatabaseStorage(TeiidOperationHandler.java:165)
at org.teiid.jboss.DDLExecutor.executeOperation(TeiidOperationHandler.java:1721)
at org.teiid.jboss.DDLExecutor.executeOperation(TeiidOperationHandler.java:1684)
at org.teiid.jboss.BaseOperationHandler$1.execute(BaseOperationHandler.java:79)
at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:890)
at org.jboss.as.controller.AbstractOperationContext.processStages(AbstractOperationContext.java:659)
at org.jboss.as.controller.AbstractOperationContext.executeOperation(AbstractOperationContext.java:370)
at org.jboss.as.controller.OperationContextImpl.executeOperation(OperationContextImpl.java:1344)
at org.jboss.as.controller.ModelControllerImpl.internalExecute(ModelControllerImpl.java:392)
at org.jboss.as.controller.ModelControllerImpl.execute(ModelControllerImpl.java:217)
at org.jboss.as.controller.remote.ModelControllerClientOperationHandler$ExecuteRequestHandler.doExecute(ModelControllerClientOperationHandler.java:208)
at org.jboss.as.controller.remote.ModelControllerClientOperationHandler$ExecuteRequestHandler.access$300(ModelControllerClientOperationHandler.java:130)
at org.jboss.as.controller.remote.ModelControllerClientOperationHandler$ExecuteRequestHandler$1$1.run(ModelControllerClientOperationHandler.java:152)
at org.jboss.as.controller.remote.ModelControllerClientOperationHandler$ExecuteRequestHandler$1$1.run(ModelControllerClientOperationHandler.java:148)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.jboss.as.controller.AccessAuditContext.doAs(AccessAuditContext.java:92)
at org.jboss.as.controller.remote.ModelControllerClientOperationHandler$ExecuteRequestHandler$1.execute(ModelControllerClientOperationHandler.java:148)
at org.jboss.as.protocol.mgmt.AbstractMessageHandler$ManagementRequestContextImpl$1.doExecute(AbstractMessageHandler.java:363)
at org.jboss.as.protocol.mgmt.AbstractMessageHandler$AsyncTaskRunner.run(AbstractMessageHandler.java:472)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
at org.jboss.threads.JBossThread.run(JBossThread.java:320)
{code}
was (Author: kylin):
h2. 1. Unit test error
The unit test still have some error
{code}
Tests in error:
TestMetadataValidator.testCreateTrigger:142->buildModel:71 » Metadata TEIID312...
TestMetadataValidator.testCreateTriggerFails:153->buildModel:71 » Metadata TEI...
TestMetadataValidator.testExternalMaterializationValidate:327->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateColumnTypes:365->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateColumns:347->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateLoadScripts:411->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateMissingColumns:395->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateMissingStatus:380->buildModel:71 » Metadata
TestMetadataValidator.testInternalMaterializationValidate:309->buildModel:71 » Metadata
TestMetadataValidator.testModelArtifactsSucess:108->buildModel:71 » Metadata T...
TestMetadataValidator.testProcMetadata:178->buildModel:71 » Metadata TEIID3123...
TestMetadataValidator.testProcMetadataValidationError:187->buildModel:71 » Metadata
TestMetadataValidator.testResolveMetadata:130->buildModel:71 » Metadata TEIID3...
TestMetadataValidator.testResolveMetadataError:206->buildModel:71 » Metadata T...
TestMetadataValidator.testResolveTempMetadata:197->buildModel:71 » Metadata TE...
TestMetadataValidator.testViewKeys:488->buildModel:71 » Metadata TEIID31236 No...
TestMetadataValidator.testViewModelArtifacts:100->buildModel:71 » Metadata TEI...
TestDDLParser.testAlterDatabase:949->helpParse:763 » Metadata TEIID31214 DataW...
TestDDLParser.testAlterProcedureOptions:886->helpParse:747 » Parse TEIID30386 ...
TestDDLParser.testAlterTableAddColumnOptions:844->helpParse:747 » Metadata TEI...
TestDDLParser.testAlterTableAddOptions:798->helpParse:747 » Metadata TEIID3035...
TestDDLParser.testAlterTableDropOptions:828->helpParse:747 » Metadata TEIID303...
TestDDLParser.testAlterTableModifyOptions:813->helpParse:747 » Metadata TEIID3...
TestDDLParser.testAlterTableRemoveColumnOptions:864->helpParse:747 » Parse TEI...
TestDDLParser.testAlterView:1114->helpParse:763 » Metadata TEIID30357 G1 does ...
Tests run: 6043, Failures: 0, Errors: 25, Skipped: 65
{code}
> add/remove schema elements
> --------------------------
>
> Key: TEIID-2578
> URL: https://issues.jboss.org/browse/TEIID-2578
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Fix For: 9.1
>
>
> Schemas are currently static after load. Modifications can only happen with restarts or new versions. We should allow add/drop at runtime.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
[View Less]
8 years, 7 months
[JBoss JIRA] (TEIID-2578) add/remove schema elements
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-2578?page=com.atlassian.jira.plugin... ]
Kylin Soong commented on TEIID-2578:
------------------------------------
h2. 1. Unit test error
The unit test still have some error
{code}
Tests in error:
TestMetadataValidator.testCreateTrigger:142->buildModel:71 » Metadata TEIID312...
TestMetadataValidator.testCreateTriggerFails:153->buildModel:71 » Metadata TEI...
TestMetadataValidator.testExternalMaterializationValidate:327->buildModel:71 » …
[View More]Metadata
TestMetadataValidator.testExternalMaterializationValidateColumnTypes:365->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateColumns:347->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateLoadScripts:411->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateMissingColumns:395->buildModel:71 » Metadata
TestMetadataValidator.testExternalMaterializationValidateMissingStatus:380->buildModel:71 » Metadata
TestMetadataValidator.testInternalMaterializationValidate:309->buildModel:71 » Metadata
TestMetadataValidator.testModelArtifactsSucess:108->buildModel:71 » Metadata T...
TestMetadataValidator.testProcMetadata:178->buildModel:71 » Metadata TEIID3123...
TestMetadataValidator.testProcMetadataValidationError:187->buildModel:71 » Metadata
TestMetadataValidator.testResolveMetadata:130->buildModel:71 » Metadata TEIID3...
TestMetadataValidator.testResolveMetadataError:206->buildModel:71 » Metadata T...
TestMetadataValidator.testResolveTempMetadata:197->buildModel:71 » Metadata TE...
TestMetadataValidator.testViewKeys:488->buildModel:71 » Metadata TEIID31236 No...
TestMetadataValidator.testViewModelArtifacts:100->buildModel:71 » Metadata TEI...
TestDDLParser.testAlterDatabase:949->helpParse:763 » Metadata TEIID31214 DataW...
TestDDLParser.testAlterProcedureOptions:886->helpParse:747 » Parse TEIID30386 ...
TestDDLParser.testAlterTableAddColumnOptions:844->helpParse:747 » Metadata TEI...
TestDDLParser.testAlterTableAddOptions:798->helpParse:747 » Metadata TEIID3035...
TestDDLParser.testAlterTableDropOptions:828->helpParse:747 » Metadata TEIID303...
TestDDLParser.testAlterTableModifyOptions:813->helpParse:747 » Metadata TEIID3...
TestDDLParser.testAlterTableRemoveColumnOptions:864->helpParse:747 » Parse TEI...
TestDDLParser.testAlterView:1114->helpParse:763 » Metadata TEIID30357 G1 does ...
Tests run: 6043, Failures: 0, Errors: 25, Skipped: 65
{code}
> add/remove schema elements
> --------------------------
>
> Key: TEIID-2578
> URL: https://issues.jboss.org/browse/TEIID-2578
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Fix For: 9.1
>
>
> Schemas are currently static after load. Modifications can only happen with restarts or new versions. We should allow add/drop at runtime.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
[View Less]
8 years, 7 months
[JBoss JIRA] (TEIID-4393) Add new Geospatial functions
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4393?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4393.
-----------------------------------
Resolution: Done
Added docs and a release note as well.
> Add new Geospatial functions
> ----------------------------
>
> Key: TEIID-4393
> URL: https://issues.jboss.org/browse/TEIID-4393
> Project: Teiid
> Issue Type: Feature Request
> Components: …
[View More]Server
> Affects Versions: 8.12.x, 9.x
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> Adding a list of geospatial features to be added. Used the following specification: https://portal.opengeospatial.org/files/?artifact_id=829
> st_Dimension
> st_GeometryType
> st_IsEmpty
> st_IsSimple
> st_Boundary
> st_Envelope
> st_X
> st_Y
> st_StartPoint
> st_EndPoint
> st_Boundary
> st_Boundary
> st_Length
> st_NumPoints
> st_PointN
> st_Centroid
> st_PointOnSurface
> st_Area
> st_ExteriorRing
> st_NumInteriorRings
> st_InteriorRingN
> st_NumGeometries
> st_GeometryN
> st_IsClosed
> st_Length
> st_Centroid
> st_PointOnSurface
> st_Area
> st_Within
> st_Relate
> st_Intersection
> st_Difference
> st_Union
> st_SymmetricDifference
> st_Buffer
> st_ConvexHull
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
[View Less]
8 years, 7 months
[JBoss JIRA] (TEIID-4021) MSSQL Pagination
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4021?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4021:
---------------------------------------
Are you manually setting the database version translator property? With the current code if 2008 is detected it should use row_number approach. I didn't have a 2005 instance to test against, but that may work there as well.
> MSSQL Pagination
> ----------------
>
> Key: TEIID-4021
> …
[View More]URL: https://issues.jboss.org/browse/TEIID-4021
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> I'm using Teiid 8.11.3 with mssql translator.
> I have a huge table over sql server 2008 on which i'm applying pagination.
> I noticed slowliness in the query execution over this table at each time I increase the pagination.
> I monitored the JDBC queries execute by teiid and found out that it is using top n according to the limit used into the query.
> For example:
> in teiid : Select * from mytable LIMIT 90 , 10
> is translated in JDBC: select top 100 from mytable
> Since my table contains millions of records, when fetching the last page, the executed jdbc query is retrieving the whole table top n, to return in result the last 10 records. And the execution is taking too much time.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
[View Less]
8 years, 7 months
[JBoss JIRA] (TEIID-4415) OData4 contains(columnname, 'value') eq true filter fails
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4415?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4415:
-------------------------------------
Pls add valid syntax you found out for next person.
> OData4 contains(columnname,'value') eq true filter fails
> --------------------------------------------------------
>
> Key: TEIID-4415
> URL: https://issues.jboss.org/browse/TEIID-4415
> Project: Teiid
> …
[View More]Issue Type: Bug
> Components: OData
> Affects Versions: 9.0.2
> Environment: WildFly 10
> Teiid 9.0.2
> Reporter: J Price
> Assignee: Steven Hawkins
>
> I am receiving the following error message when using a contains filter on an odata4 endpoint:
> <error><code>null</code><message>TEIID31100 Parsing error: Encountered ") >= 1 [*]=[*] CONVERT(" at line 1, column 247.
> Was expecting: "and" | "or" | "," | ")" | "*" | "/" | "+" | "-" | "||" | "&&" ...</message></error>
> The URI to reproduce this is as folows:
> /odata4/Sandbox.1/Compatibility/USERLIST?$top=6&$filter=contains(FIRSTNAME,'peter') eq true and contains(LASTNAME,'peter') eq false
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
[View Less]
8 years, 7 months
[JBoss JIRA] (TEIID-4415) OData4 contains(columnname, 'value') eq true filter fails
by J Price (JIRA)
[ https://issues.jboss.org/browse/TEIID-4415?page=com.atlassian.jira.plugin... ]
J Price resolved TEIID-4415.
----------------------------
Resolution: Won't Fix
This bug is invalid for 2 reasons... the grammar parsing is in olingo instead of teiid code and the abnf for this expression states that the reported format is invalid.
> OData4 contains(columnname,'value') eq true filter fails
> --------------------------------------------------------
>
> Key: TEIID-…
[View More]4415
> URL: https://issues.jboss.org/browse/TEIID-4415
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 9.0.2
> Environment: WildFly 10
> Teiid 9.0.2
> Reporter: J Price
> Assignee: Steven Hawkins
>
> I am receiving the following error message when using a contains filter on an odata4 endpoint:
> <error><code>null</code><message>TEIID31100 Parsing error: Encountered ") >= 1 [*]=[*] CONVERT(" at line 1, column 247.
> Was expecting: "and" | "or" | "," | ")" | "*" | "/" | "+" | "-" | "||" | "&&" ...</message></error>
> The URI to reproduce this is as folows:
> /odata4/Sandbox.1/Compatibility/USERLIST?$top=6&$filter=contains(FIRSTNAME,'peter') eq true and contains(LASTNAME,'peter') eq false
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
[View Less]
8 years, 7 months