[JBoss JIRA] Created: (TEIID-923) Oracle queries with a TimeStamp in the WHERE clause are failing if multiple TimeStamps are used in "AND", "OR', and "IN" situations
by Warren Gibson (JIRA)
Oracle queries with a TimeStamp in the WHERE clause are failing if multiple TimeStamps are used in "AND", "OR', and "IN" situations
-----------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-923
URL: https://jira.jboss.org/jira/browse/TEIID-923
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 7.0
Environment: CTC Query Suite running Teiid 7.0 with ojdbc6 driver
Reporter: Warren Gibson
Assignee: Steven Hawkins
The following queries are failing in CTC with an ORCL errors "invalid characters" or "missing right parenthesis". The ojdbc6 driver is being used.
SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.TimeStampValue IN (convert('2000-01-01 00:00:12.0', timestamp), convert('2000-01-01 00:00:45.0', timestamp)) ORDER BY IntKey
SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.TimestampValue > '2000-01-01 00:00:25.0' AND BQT1.SmallA.TimeStampValue < '2000-01-01 00:00:30.0' ORDER BY IntKey
SELECT IntKey, TimestampValue FROM BQT1.SmallA WHERE TimestampValue > '2000-01-01 00:00:47.0' OR TimestampValue < '2000-01-01 00:00:02.0' ORDER BY IntKey
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 6 months
[JBoss JIRA] Created: (TEIID-919) Planner created incomplete identifiers when GROUP BY of inline view with UNION ALL
by Paul Nittel (JIRA)
Planner created incomplete identifiers when GROUP BY of inline view with UNION ALL
----------------------------------------------------------------------------------
Key: TEIID-919
URL: https://jira.jboss.org/jira/browse/TEIID-919
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Environment: Fedora 12, Teiid from 01/04
Reporter: Paul Nittel
Assignee: Steven Hawkins
Attachments: teiid.log, TPCRperf.vdb
Using the performance test suite VDB which uses TPCR, there were questions of the number of rows which should be returned for a specific query. To determine the correct nummber of rows to be returned, This query was executed through AdminShell:
SELECT count(*) from ( select P_PARTKEY, P_NAME, P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE FROM TPCR01_SqlServerVirt.TPCR01.PART WHERE (P_RETAILPRICE > 911) AND (P_RETAILPRICE < 1400) UNION all SELECT CONVERT(P_PARTKEY,integer), P_NAME, P_MFGR, P_BRAND, P_TYPE, CONVERT(P_SIZE,integer), P_CONTAINER, P_RETAILPRICE FROM TPCR01_OracleVirt.PART WHERE (P_RETAILPRICE > 1200) AND (P_RETAILPRICE < 1500)) as x group by P_PARTKEY, P_NAME, P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE;
The result was this:
org.teiid.connector.jdbc.JDBCExecutionException: Error Code:904 Message:ORA-00904: "V_0"."P_SIZE": invalid identifier
Executing statement:
[SQL: SELECT v_0.P_PARTKEY, v_0.c_7, v_0.c_0, v_0.c_1, v_0.c_3, v_0.P_SIZE, v_0.c_2, v_0.c_5, COUNT(*) FROM (SELECT g_0.P_MFGR AS c_0, g_0.P_BRAND AS c_1, g_0.P_CONTAINER AS c_2, g_0.P_TYPE AS c_3, g_0.P_PARTKEY AS c_4, g_0.P_RETAILPRICE AS c_5, g_0.P_SIZE AS c_6, g_0.P_NAME AS c_7 FROM PART g_0 WHERE (g_0.P_RETAILPRICE > 1200) AND (g_0.P_RETAILPRICE < 1500)) v_0 GROUP BY v_0.c_0, v_0.c_1, v_0.c_2, v_0.c_3, v_0.P_PARTKEY, v_0.c_5, v_0.P_SIZE, v_0.c_7]
at org.teiid.connector.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:98)
at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:293)
at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:163)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:60)
at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:636)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 6 months
[JBoss JIRA] Created: (TEIID-894) Query inexplicably failing Query Testing only with MySQL
by Paul Nittel (JIRA)
Query inexplicably failing Query Testing only with MySQL
--------------------------------------------------------
Key: TEIID-894
URL: https://jira.jboss.org/jira/browse/TEIID-894
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Environment: Fedora 10, Teiid 7.0.0 M1
Reporter: Paul Nittel
Assignee: Steven Hawkins
Series 9000, #7 fails with MySql and passes with all the other sources.
Original Query
SELECT BQT1.SmallA.IntKey, BQT2.SmallB.FloatNum FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.FloatNum AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntKey;
It's supposed to return 25 rows, but returns 0. Let's ignore the "ORDER BY" since it just adds to the code (and doesn't factor into the issue since it works the same with or without).
If I remove the "AND BQT2.SmallB.IntKey >= 0", it works! I get 25 rows.
SELECT BQT1.SmallA.IntKey, BQT2.SmallB.FloatNum FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.FloatNum AND BQT1.SmallA.IntKey >= 0;
If I switch it so the first condition is removed and the second remains, it FAILS
SELECT BQT1.SmallA.IntKey, BQT2.SmallB.FloatNum FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.FloatNum AND BQT2.SmallB.IntKey >= 0;
What doesn't it like about "BQT2.SmallB >= 0" ?? I ran this query--and the one with only "BQT1.SmallA >= 0"--with OPTION DEBUG.
Stuff is attached.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 6 months
[JBoss JIRA] Created: (TEIID-754) LOCATE() function isn't being translated correctly by Oracle Connector
by Larry O'Leary (JIRA)
LOCATE() function isn't being translated correctly by Oracle Connector
----------------------------------------------------------------------
Key: TEIID-754
URL: https://jira.jboss.org/jira/browse/TEIID-754
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 6.1.0, 6.0.0, 6.2.0
Reporter: Larry O'Leary
Assignee: Larry O'Leary
Fix For: 6.2.0
The rewritten/translated query for the MMx LOCATE() function to Oracle's instr() function does not appear to be correct.
SELECT locate(INTNUM, '234567890', 1) FROM SMALLA WHERE INTKEY = 26
Is being rewritten for Oracle as:
SELECT instr('234567890', to_char(SmallA.IntNum), 2) FROM SmallA WHERE SmallA.IntKey = 26
In this case Oracle will return 0 for instr() because Oracle starts at position 1.
The query should be:
SELECT instr('234567890', to_char(SmallA.IntNum), 1) FROM SmallA WHERE SmallA.IntKey = 26
Furthermore, if I pass a negative value to LOCATE() it appears we assume position 1. If the negative value is sent to Oracle, Oracle goes from the end of the string. We should prevent this (if not already).
In 5.5.1 the rewritten query is even different and may ore may not be correct. I have not checked 5.5.3/5.5.4 and/or Westport/Teiid.
Here are the test cases that should cover this issue:
public void testRewriteLocate() throws Exception {
String input = "SELECT locate(INTNUM, 'chimp', 1) FROM SMALLA"; //$NON-NLS-1$
String output = "SELECT instr('chimp', to_char(SmallA.IntNum), 1) FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestVDBPath(),
input,
new Integer(TranslatedCommand.EXEC_TYPE_QUERY),
output);
}
public void testRewriteLocate2() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp') FROM SMALLA"; //$NON-NLS-1$
String output = "SELECT instr('chimp', SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestVDBPath(),
input,
new Integer(TranslatedCommand.EXEC_TYPE_QUERY),
output);
}
public void testRewriteLocate3() throws Exception {
String input = "SELECT locate(INTNUM, '234567890', 1) FROM SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$
String output = "SELECT instr('234567890', to_char(SmallA.IntNum), 1) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$
helpTestVisitor(getTestVDBPath(),
input,
new Integer(TranslatedCommand.EXEC_TYPE_QUERY),
output);
}
public void testRewriteLocate4() throws Exception {
String input = "SELECT locate('c', 'chimp', 1) FROM SMALLA"; //$NON-NLS-1$
String output = "SELECT 1 FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestVDBPath(),
input,
new Integer(TranslatedCommand.EXEC_TYPE_QUERY),
output);
}
public void testRewriteLocate5() throws Exception {
String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM SMALLA"; //$NON-NLS-1$
String output = "SELECT instr('chimp', SmallA.StringNum, 1) FROM SmallA"; //$NON-NLS-1$
helpTestVisitor(getTestVDBPath(),
input,
new Integer(TranslatedCommand.EXEC_TYPE_QUERY),
output);
}
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 6 months