[JBoss JIRA] (TEIID-3384) SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3384?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3384.
-----------------------------------
Resolution: Done
We can do further rewrite here if needed, but for now just ensured that the inline view is preserved if it looks like we'll be grouping by a literal. This prevents this source exception and issues with sources that interpret integer values as column references.
> SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference
> ------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-3384
> URL: https://issues.jboss.org/browse/TEIID-3384
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.11
>
>
> Running this query (where "test_a" is a table in MS SQL Server):
> {code:sql}
> select test2
> from
> (select 'aaa' as test2 from ms.test_a) b
> group by test2
> {code}
> the following exception is thrown:
> {code:sql}
> 01:36:55,198 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue4) ks49tw3LLVqR Connector worker process failed for atomic-request=ks49tw3LLVqR.1.1.1: org.teiid.translator.jdbc.JDBCExecutionException: 164 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 'aaa' FROM "test_tables"."dbo"."test_a" g_0 GROUP BY 'aaa']
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.10.0.Beta2.jar:8.10.0.Beta2]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:335)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_51]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_51]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_51]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:209)
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_51]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
> at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference.
> at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
> at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.10.0.Beta2.jar:8.10.0.Beta2]
> ... 18 more
> {code}
> The connection to the datasource is defined in standalone-teiid.xml as:
> {code:sql}
> <datasource jndi-name="java:/mssql-test-tables" pool-name="sqlserverDS1" enabled="true">
> <connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_tables</connection-url>
> <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
> <driver>com.microsoft.sqlserver</driver>
> <pool>
> <prefill>false</prefill>
> <use-strict-min>false</use-strict-min>
> <flush-strategy>FailingConnectionOnly</flush-strategy>
> </pool>
> <security>
> <user-name>test</user-name>
> <password>test</password>
> </security>
> <validation>
> <check-valid-connection-sql>select 1</check-valid-connection-sql>
> </validation>
> </datasource>
> {code}
> Driver and translator definitions are respectively:
> {code:sql}
> ...
> <driver name="com.microsoft.sqlserver" module="com.microsoft.sqlserver"/>
> ...
> <translator name="sqlserver" module="org.jboss.teiid.translator.jdbc"/>
> ...
> {code}
> and the "ms" model is defined in a dynamic vdb as:
> {code:sql}
> <model name="ms">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="importer.tableTypes" value="TABLE,VIEW"/>
> <property name="importer.importKeys" value="false"/>
> <property name="importer.schemaPattern" value="dbo"/>
> <source name="test_tables_ms" translator-name="sqlserver" connection-jndi-name="java:/mssql-test-tables"/>
> </model>
> {code}
> I could reproduce this error in Teiid-8.10.0.Beta2 running SQL Server Express 2012 - 64bit (11.0.3128.0).
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3384) SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3384?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3384:
----------------------------------
Fix Version/s: 8.11
Affects Version/s: 8.7
Component/s: Query Engine
> SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference
> ------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-3384
> URL: https://issues.jboss.org/browse/TEIID-3384
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.11
>
>
> Running this query (where "test_a" is a table in MS SQL Server):
> {code:sql}
> select test2
> from
> (select 'aaa' as test2 from ms.test_a) b
> group by test2
> {code}
> the following exception is thrown:
> {code:sql}
> 01:36:55,198 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue4) ks49tw3LLVqR Connector worker process failed for atomic-request=ks49tw3LLVqR.1.1.1: org.teiid.translator.jdbc.JDBCExecutionException: 164 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 'aaa' FROM "test_tables"."dbo"."test_a" g_0 GROUP BY 'aaa']
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.10.0.Beta2.jar:8.10.0.Beta2]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:335)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_51]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_51]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_51]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:209)
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_51]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
> at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference.
> at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
> at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.10.0.Beta2.jar:8.10.0.Beta2]
> ... 18 more
> {code}
> The connection to the datasource is defined in standalone-teiid.xml as:
> {code:sql}
> <datasource jndi-name="java:/mssql-test-tables" pool-name="sqlserverDS1" enabled="true">
> <connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_tables</connection-url>
> <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
> <driver>com.microsoft.sqlserver</driver>
> <pool>
> <prefill>false</prefill>
> <use-strict-min>false</use-strict-min>
> <flush-strategy>FailingConnectionOnly</flush-strategy>
> </pool>
> <security>
> <user-name>test</user-name>
> <password>test</password>
> </security>
> <validation>
> <check-valid-connection-sql>select 1</check-valid-connection-sql>
> </validation>
> </datasource>
> {code}
> Driver and translator definitions are respectively:
> {code:sql}
> ...
> <driver name="com.microsoft.sqlserver" module="com.microsoft.sqlserver"/>
> ...
> <translator name="sqlserver" module="org.jboss.teiid.translator.jdbc"/>
> ...
> {code}
> and the "ms" model is defined in a dynamic vdb as:
> {code:sql}
> <model name="ms">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="importer.tableTypes" value="TABLE,VIEW"/>
> <property name="importer.importKeys" value="false"/>
> <property name="importer.schemaPattern" value="dbo"/>
> <source name="test_tables_ms" translator-name="sqlserver" connection-jndi-name="java:/mssql-test-tables"/>
> </model>
> {code}
> I could reproduce this error in Teiid-8.10.0.Beta2 running SQL Server Express 2012 - 64bit (11.0.3128.0).
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3302) Implement ST_Transform
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3302?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3302:
---------------------------------------
I pulled the commit to correct the view name against TEIID-3300. But the proj4j jar that I'm getting from maven is empty. I'll have to look more into what could be going on there. Also we'll need the source jar to jts2geojson to be in the repository as well, so that productization builds can be fully from source.
> Implement ST_Transform
> ----------------------
>
> Key: TEIID-3302
> URL: https://issues.jboss.org/browse/TEIID-3302
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Tom Arnold
> Assignee: Steven Hawkins
> Labels: spatial
>
> Implement [ST_Transform|http://postgis.org/docs/ST_Transform.html] system function. This would be used to transform geometry between different coordinate systems. This needs SPATIAL_REF_SYS table and a supporting library.
> There are a couple of options for implementing this:
> * http://trac.osgeo.org/proj4j/ - Port of the C library used by PostGIS. Fewest dependencies and should keep us close to PostGIS in capabilities. There will be a little boilerplate to interface with JTS.
> * http://docs.geotools.org/latest/userguide/library/referencing/index.html - GeoTools referencing module. I think we would need to develop a simple plugin to interface with our SPATIAL_REF_SYS table. GeoTools is active and has good docs, but brings in a lot of dependencies.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3383) Add NPE handling in WSConnectionImpl.invoke
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3383?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3383.
-----------------------------------
Resolution: Duplicate Issue
Duplicate of TEIID-2956
> Add NPE handling in WSConnectionImpl.invoke
> -------------------------------------------
>
> Key: TEIID-3383
> URL: https://issues.jboss.org/browse/TEIID-3383
> Project: Teiid
> Issue Type: Bug
> Components: Connector API
> Affects Versions: 8.4.1
> Reporter: Bas Piepers
> Assignee: Steven Hawkins
> Priority: Minor
>
> In the WSConnectionImpl.java class in the org.teiid.resource.adapter.ws package (of connector-ws-8.4.1-redhat-9.jar) in the invoke(DataSource ds) method at the end it tries to get the content-type from the response in line 164 and then returns the first item in the list of content types:
> ArrayList contentTypes = (ArrayList)this.responseContext.get("content-type"); //$NON-NLS-1$
> return new HttpDataSource(url, (InputStream)response.getEntity(), (String)contentTypes.get(0));
> However if the web service that it calls returns an error code (like 415, for example) it does not always contain a content-type in its header. Basically this method expects a result to be returned all the time with a corresponding content-type which is not right. The line where it calls contentTypes.get(0) may result in a NPE.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months