[JBoss JIRA] (TEIID-4008) Sending teiid varbinary value (x'') to Microsoft SQL Server errors
by Debbie Steigner (JIRA)
Debbie Steigner created TEIID-4008:
--------------------------------------
Summary: Sending teiid varbinary value (x'') to Microsoft SQL Server errors
Key: TEIID-4008
URL: https://issues.jboss.org/browse/TEIID-4008
Project: Teiid
Issue Type: Bug
Affects Versions: 8.7.2.6_2
Reporter: Debbie Steigner
Assignee: Steven Hawkins
When sending a query to Microsoft SQL Server with a varbinary in criteria:
select * from debBinary where ipv6= x'FFFF6BBE85D8'
It pushes the criteria to Microsoft SQL Server, but as X'FFFF6BBE85D8'[1] so Microsoft gives the syntax error[2] and needs to just be select * from debBinary where ipv6= 'FFFF6BBE85D8':
[1]14:26:08,951 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue8) Source-specific command: SELECT g_0."id", g_0."ipv6" FROM "bqt2"."dbo"."debbinary" g_0 WHERE g_0."ipv6" = X'FFFF6BBE85D8'
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 10 months
[JBoss JIRA] (TEIID-3994) AliasSymbol wrapping AliasSymbol error
by Steve Tran (JIRA)
[ https://issues.jboss.org/browse/TEIID-3994?page=com.atlassian.jira.plugin... ]
Steve Tran updated TEIID-3994:
------------------------------
Attachment: Oracle.PNG
SQLServer.PNG
> AliasSymbol wrapping AliasSymbol error
> --------------------------------------
>
> Key: TEIID-3994
> URL: https://issues.jboss.org/browse/TEIID-3994
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.1.6_2
> Environment: Red Hat JBoss Data Virtualization 6.2 on EAP6.4.0 patched to version 6.4.3,
> JBoss Developer Studio 8.1.0GA with Teiid Designer plugin 9.0.3.Final.v20150810-1438-B1157
> 64-bit Windows 7 environment
> Reporter: Steve Tran
> Assignee: Steven Hawkins
> Attachments: Oracle.PNG, SQLServer.PNG
>
>
> I've defined a view and overrode the default triggers for Select, Insert, Update, and Delete. I overrode the triggers because the select is returning "hardcoded" values for a few fields (calling them virtual columns). The Update and Insert triggers will ignore these hardcoded values just in case the caller tries to update a virtual column.
> I'm trying to run a insert into <tableA> select * from <tableB> query. When I run this, I get an error org.teiid.jdbc.TeiidSQLException: Cannot create AliasSymbol wrapping AliasSymbol.
> Facts:
> -- I can insert a record if I only select one column from tableB.
> -- If I select two or more columns, it throws the error every time.
> -- If I remove the overridden triggers, the insert into <tableA> select * from <tableB> works fine.
> -- I can insert one or more columns into <tableA> when *not* using a insert into <tableA> select * from <tableB> query
> -- Table A is Oracle
> -- Table B is SQL Server
> My hunch is the generated query (hibernate, or whatever SQL generator) is choking with the overridden trigger. Perhaps this use-case slipped through testing, or even more probable, I made a mistake in the trigger creation.
> Here's a snip of the insert trigger, with a lot of fields removed for simplicity.
> {code}
> FOR EACH ROW
> BEGIN ATOMIC
> INSERT INTO <schema>.<tableA> (SourceSystemID, RecordTypeID, BatchID ...)
> VALUES (
> convert("NEW".SourceSystemID, short),
> convert("NEW".RecordTypeID, short),
> convert("NEW".BatchID, integer)
> ...
> )
> END
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 10 months
[JBoss JIRA] (TEIID-3994) AliasSymbol wrapping AliasSymbol error
by Steve Tran (JIRA)
[ https://issues.jboss.org/browse/TEIID-3994?page=com.atlassian.jira.plugin... ]
Steve Tran commented on TEIID-3994:
-----------------------------------
Query which throws the AliasSymbol wrapping AliasSymbol wrapping AliasSymbol exception. Might be hard to follow, but all it's doing is an insert into SQL Server View SELECT from Oracle View.
{code}
insert into Control.SQLServer_VBL.HSI_TEST_VIRTUALFIELD (TEST_ID, TEST_NBR, TEST_DT, TEST_STR, TEST_INT_NBR)
SELECT TEST_ID, TEST_NBR, TEST_DT, TEST_STR, TEST_INT_NBR FROM Control.Oracle2_VBL.HSI_TEST where test_id = 1050783767;
{code}
Select Trigger on Control.SQLServer_VBL.HSI_TEST_VIRTUALFIELD
{code}
SELECT
TEST_ID, TEST_NBR, TEST_DT, TEST_STR, TEST_INT_NBR, TEST_TS, 1 AS VirtField1, 'Steve' AS VirtField2
FROM
SQL_Server_Cdtssql587D_KF.HSI_TEST
{code}
Insertion Trigger Control.SQLServer_VBL.HSI_TEST_VIRTUALFIELD
{code}
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO SQL_Server_Cdtssql587D_KF.HSI_TEST (SQL_Server_Cdtssql587D_KF.HSI_TEST.TEST_ID, SQL_Server_Cdtssql587D_KF.HSI_TEST.TEST_NBR, SQL_Server_Cdtssql587D_KF.HSI_TEST.TEST_DT, SQL_Server_Cdtssql587D_KF.HSI_TEST.TEST_STR, SQL_Server_Cdtssql587D_KF.HSI_TEST.TEST_INT_NBR, SQL_Server_Cdtssql587D_KF.HSI_TEST.TEST_TS) VALUES ("NEW".TEST_ID, "NEW".TEST_NBR, "NEW".TEST_DT, "NEW".TEST_STR, "NEW".TEST_INT_NBR, "NEW".TEST_TS);
END
{code}
See screenshots for descriptions of "tableA" and "tableB"
> AliasSymbol wrapping AliasSymbol error
> --------------------------------------
>
> Key: TEIID-3994
> URL: https://issues.jboss.org/browse/TEIID-3994
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.1.6_2
> Environment: Red Hat JBoss Data Virtualization 6.2 on EAP6.4.0 patched to version 6.4.3,
> JBoss Developer Studio 8.1.0GA with Teiid Designer plugin 9.0.3.Final.v20150810-1438-B1157
> 64-bit Windows 7 environment
> Reporter: Steve Tran
> Assignee: Steven Hawkins
> Attachments: Oracle.PNG, SQLServer.PNG
>
>
> I've defined a view and overrode the default triggers for Select, Insert, Update, and Delete. I overrode the triggers because the select is returning "hardcoded" values for a few fields (calling them virtual columns). The Update and Insert triggers will ignore these hardcoded values just in case the caller tries to update a virtual column.
> I'm trying to run a insert into <tableA> select * from <tableB> query. When I run this, I get an error org.teiid.jdbc.TeiidSQLException: Cannot create AliasSymbol wrapping AliasSymbol.
> Facts:
> -- I can insert a record if I only select one column from tableB.
> -- If I select two or more columns, it throws the error every time.
> -- If I remove the overridden triggers, the insert into <tableA> select * from <tableB> works fine.
> -- I can insert one or more columns into <tableA> when *not* using a insert into <tableA> select * from <tableB> query
> -- Table A is Oracle
> -- Table B is SQL Server
> My hunch is the generated query (hibernate, or whatever SQL generator) is choking with the overridden trigger. Perhaps this use-case slipped through testing, or even more probable, I made a mistake in the trigger creation.
> Here's a snip of the insert trigger, with a lot of fields removed for simplicity.
> {code}
> FOR EACH ROW
> BEGIN ATOMIC
> INSERT INTO <schema>.<tableA> (SourceSystemID, RecordTypeID, BatchID ...)
> VALUES (
> convert("NEW".SourceSystemID, short),
> convert("NEW".RecordTypeID, short),
> convert("NEW".BatchID, integer)
> ...
> )
> END
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 10 months
[JBoss JIRA] (TEIID-3994) AliasSymbol wrapping AliasSymbol error
by Steve Tran (JIRA)
[ https://issues.jboss.org/browse/TEIID-3994?page=com.atlassian.jira.plugin... ]
Steve Tran commented on TEIID-3994:
-----------------------------------
Stack Trace
{code}
22:35:02,158 INFO [org.teiid.PLANNER] (Worker2_QueryProcessorQueue33)
============================================================================
USER COMMAND:
INSERT INTO SQLServer_VBL.HSI_TEST_VIRTUALFIELD (TEST_ID, TEST_NBR, TEST_DT, TEST_STR, TEST_INT_NBR) SELECT X.TEST_ID, X.TEST_NBR, X.TEST_DT AS TEST_DT, X.TEST_STR, X.TEST_INT_NBR FROM (SELECT Oracle2_VBL.HSI_TEST.TEST_ID, Oracle2_VBL.HSI_TEST.TEST_NBR, Oracle2_VBL.HSI_TEST.TEST_DT, Oracle2_VBL.HSI_TEST.TEST_STR, Oracle2_VBL.HSI_TEST.TEST_INT_NBR FROM Oracle2_VBL.HSI_TEST WHERE Oracle2_VBL.HSI_TEST.TEST_ID = 1050783767) AS X
----------------------------------------------------------------------------
OPTIMIZE:
INSERT INTO SQLServer_VBL.HSI_TEST_VIRTUALFIELD (TEST_ID, TEST_NBR, TEST_DT, TEST_STR, TEST_INT_NBR) SELECT X.TEST_ID, X.TEST_NBR, X.TEST_DT AS TEST_DT, X.TEST_STR, X.TEST_INT_NBR FROM (SELECT Oracle2_VBL.HSI_TEST.TEST_ID, Oracle2_VBL.HSI_TEST.TEST_NBR, Oracle2_VBL.HSI_TEST.TEST_DT, Oracle2_VBL.HSI_TEST.TEST_STR, Oracle2_VBL.HSI_TEST.TEST_INT_NBR FROM Oracle2_VBL.HSI_TEST WHERE Oracle2_VBL.HSI_TEST.TEST_ID = 1050783767) AS X
----------------------------------------------------------------------------
GENERATE CANONICAL:
INSERT INTO SQLServer_VBL.HSI_TEST_VIRTUALFIELD (TEST_ID, TEST_NBR, TEST_DT, TEST_STR, TEST_INT_NBR) SELECT X.TEST_ID, X.TEST_NBR, X.TEST_DT AS TEST_DT, X.TEST_STR, X.TEST_INT_NBR FROM (SELECT Oracle2_VBL.HSI_TEST.TEST_ID, Oracle2_VBL.HSI_TEST.TEST_NBR, Oracle2_VBL.HSI_TEST.TEST_DT, Oracle2_VBL.HSI_TEST.TEST_STR, Oracle2_VBL.HSI_TEST.TEST_INT_NBR FROM Oracle2_VBL.HSI_TEST WHERE Oracle2_VBL.HSI_TEST.TEST_ID = 1050783767) AS X
22:35:02,158 ERROR [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue33) TEIID30019 Unexpected exception for request vq8JhBO6ioDj.0: java.lang.AssertionError: Cannot create AliasSymbol wrapping AliasSymbol
at org.teiid.core.util.Assertion.failed(Assertion.java:73) [teiid-common-core-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.symbol.AliasSymbol.setSymbol(AliasSymbol.java:76) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.symbol.AliasSymbol.<init>(AliasSymbol.java:59) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.visitor.ExpressionMappingVisitor.replaceSymbol(ExpressionMappingVisitor.java:148) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.visitor.ExpressionMappingVisitor.visit(ExpressionMappingVisitor.java:82) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.lang.Select.acceptVisitor(Select.java:167) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:54) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:61) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:343) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.lang.Select.acceptVisitor(Select.java:167) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.sql.navigator.DeepPostOrderNavigator.doVisit(DeepPostOrderNavigator.java:40) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.optimizer.TriggerActionPlanner.rewritePlan(TriggerActionPlanner.java:166) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.optimizer.TriggerActionPlanner.optimize(TriggerActionPlanner.java:86) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.optimizer.relational.RelationalPlanner.addNestedProcedure(RelationalPlanner.java:762) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.optimizer.relational.RelationalPlanner.createUpdatePlan(RelationalPlanner.java:683) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.optimizer.relational.RelationalPlanner.generatePlan(RelationalPlanner.java:647) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:232) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:408) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:435) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:613) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:315) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [rt.jar:1.8.0_66]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [rt.jar:1.8.0_66]
at java.lang.Thread.run(Unknown Source) [rt.jar:1.8.0_66]
22:35:15,791 WARN [org.teiid.SECURITY] (New I/O worker #5) TEIID40011 Processing exception 'TEIID40042 Invalid Session ULuLLPkDM0G1. Session may have already been terminated.' for session null. Exception type org.teiid.client.security.InvalidSessionException thrown from org.teiid.services.SessionServiceImpl.getSessionInfo(SessionServiceImpl.java:365). Enable more detailed logging to see the entire stacktrace.
22:35:15,807 WARN [org.teiid.SECURITY] (New I/O worker #5) TEIID40011 Processing exception 'TEIID40042 Invalid Session y85orUzAiRMW. Session may have already been terminated.' for session null. Exception type org.teiid.client.security.InvalidSessionException thrown from org.teiid.services.SessionServiceImpl.getSessionInfo(SessionServiceImpl.java:365). Enable more detailed logging to see the entire stacktrace.
{code}
> AliasSymbol wrapping AliasSymbol error
> --------------------------------------
>
> Key: TEIID-3994
> URL: https://issues.jboss.org/browse/TEIID-3994
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.1.6_2
> Environment: Red Hat JBoss Data Virtualization 6.2 on EAP6.4.0 patched to version 6.4.3,
> JBoss Developer Studio 8.1.0GA with Teiid Designer plugin 9.0.3.Final.v20150810-1438-B1157
> 64-bit Windows 7 environment
> Reporter: Steve Tran
> Assignee: Steven Hawkins
>
> I've defined a view and overrode the default triggers for Select, Insert, Update, and Delete. I overrode the triggers because the select is returning "hardcoded" values for a few fields (calling them virtual columns). The Update and Insert triggers will ignore these hardcoded values just in case the caller tries to update a virtual column.
> I'm trying to run a insert into <tableA> select * from <tableB> query. When I run this, I get an error org.teiid.jdbc.TeiidSQLException: Cannot create AliasSymbol wrapping AliasSymbol.
> Facts:
> -- I can insert a record if I only select one column from tableB.
> -- If I select two or more columns, it throws the error every time.
> -- If I remove the overridden triggers, the insert into <tableA> select * from <tableB> works fine.
> -- I can insert one or more columns into <tableA> when *not* using a insert into <tableA> select * from <tableB> query
> -- Table A is Oracle
> -- Table B is SQL Server
> My hunch is the generated query (hibernate, or whatever SQL generator) is choking with the overridden trigger. Perhaps this use-case slipped through testing, or even more probable, I made a mistake in the trigger creation.
> Here's a snip of the insert trigger, with a lot of fields removed for simplicity.
> {code}
> FOR EACH ROW
> BEGIN ATOMIC
> INSERT INTO <schema>.<tableA> (SourceSystemID, RecordTypeID, BatchID ...)
> VALUES (
> convert("NEW".SourceSystemID, short),
> convert("NEW".RecordTypeID, short),
> convert("NEW".BatchID, integer)
> ...
> )
> END
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 10 months