[JBoss JIRA] (TEIID-4297) Add UDF functions based on OSDQ for POC
by Arun Yadav (JIRA)
[ https://issues.jboss.org/browse/TEIID-4297?page=com.atlassian.jira.plugin... ]
Arun Yadav commented on TEIID-4297:
-----------------------------------
Hi [~kylin], please try now, I have fixed the parent pom issue as well "system" scope dependency. Hope it should work now. Please ping me, if you face any problem.
> Add UDF functions based on OSDQ for POC
> ---------------------------------------
>
> Key: TEIID-4297
> URL: https://issues.jboss.org/browse/TEIID-4297
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Ramesh Reddy
> Assignee: JieRen JieRen
> Fix For: 9.1
>
>
> Please add following UDF functions to Teiid library based on maven module
> http://search.maven.org/#search%7Cga%7C1%7Carrahtec
> The UDF functions needs to from this class below
> {code}
> public class Maskutil {
> /**
> * @param a
> * The string that need to randomize
> * vivek singh' will become 'ihg vkeivh'
> */
> public static String toRandomValue(String a) {
> return ShuffleRTM.shuffleString(a);
> }
> /**
> * @param a
> * This function will retrun MD5 hashcode of the string
> * @return String
> */
> public static String toHashValue(String a) {
> if (a == null)
> return "d41d8cd98f00b204e9800998ecf8427e"; // null MD5 value
> return ResultsetToRTM.getMD5(a).toString();
> }
> /**
> * @param a
> * This function will return digit characters of the string
> * @return
> *
> */
> public static String toDigitValue(String a) {
> return StringCaseFormatUtil.digitString(a);
> }
> }
> {code}
> for example Teiid should have UDF functions like
> {code}
> string random(string)
> string hash(string)
> string digit(string)
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 5 months
[JBoss JIRA] (TEIID-3754) Remove OData V2 implementation of the Server
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3754?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-3754.
---------------------------------
Resolution: Done
RIP OData V2
One option is to reinstate this to separate repo under Teiid organization, for users who can't switch it over and would like to support themselves? What you guys think?
> Remove OData V2 implementation of the Server
> --------------------------------------------
>
> Key: TEIID-3754
> URL: https://issues.jboss.org/browse/TEIID-3754
> Project: Teiid
> Issue Type: Task
> Components: OData
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Fix For: 9.1
>
>
> We need to remove server implementation of OData V2 specification from Teiid in favor of OData V4.
> The OData V2 translator, we should keep, however that may needs to be updated/re-written using the Olingo. However that is separate issue.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 5 months
[JBoss JIRA] (TEIID-4312) INNER and LEFT joins of CTEs fail or return incorrect results
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4312?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4312.
-----------------------------------
Resolution: Done
Both of these issues were due to deficiencies with inlining logic - not being accounted for in the projection minimization or in the equality check. The fix also allows the plan to be seen as having virtual layers with inlining.
> INNER and LEFT joins of CTEs fail or return incorrect results
> -------------------------------------------------------------
>
> Key: TEIID-4312
> URL: https://issues.jboss.org/browse/TEIID-4312
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 9.1, 9.0.1
>
>
> I defined a table "test_a" with the same data and structure in PostgreSQL and MySQL:
> {code:sql}
> CREATE TABLE test_a(a integer, b integer);
> INSERT INTO test_a VALUES (1, 1);
> INSERT INTO test_a VALUES (1, 2);
> INSERT INTO test_a VALUES (2, 1);
> INSERT INTO test_a VALUES (2, 2);
> INSERT INTO test_a VALUES (3, 2);
> INSERT INTO test_a VALUES (3, 10);
> {code}
> The following query, based on the table in *PostgreSQL*, fails:
> {code:sql}
> with
> CTE1 as (
> WITH
> CTE11 as (SELECT a from pg.test_a),
> CTE21 as (select t1.a from CTE11 t1 join CTE11 t2 on t1.a=t2.a),
> CTE31 as (select a from CTE21)
> SELECT CTE31.a FROM CTE21 join CTE31 on CTE31.a=CTE21.a
> )
> select * from CTE1
> {code}
> with this exception:
> {code:sql}
> 16:36:28,752 WARN [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue114) eYLiZgMIChSF Connector worker process failed for atomic-request=eYLiZgMIChSF.11.2.24: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH CTE11 (a) AS (SELECT NULL FROM "public"."test_a" AS g_0), CTE21 (a) AS (SELECT g_0.a FROM CTE11 AS g_0, CTE11 AS g_1 WHERE g_0.a = g_1.a) SELECT g_1.a FROM CTE21 AS g_0, CTE21 AS g_1 WHERE g_1.a = g_0.a]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> at sun.reflect.GeneratedMethodAccessor94.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy56.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> 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)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.postgresql.util.PSQLException: ERROR: failed to find conversion function from unknown to text
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 17 more
> {code}
> The same query based on the table in *MySQL* wrongly returns an empty result.
> The main differences is that the query is fully pushed down to PostgreSQL but it isn't in MySQL.
> Regarding wrong results, I am also experiencing a similar problem with the following query:
> {code:sql}
> with
> CTE1 as (
> WITH
> alias as (SELECT a from pg.test_a),
> alias2 as (select t2.a as a1, t1.a from alias t1 join (SELECT 1 as a) t2 on t1.a=t2.a),
> CTE31 as (select t2.a as a1 from alias2 t2)
> SELECT CTE31.a1 FROM alias2 join CTE31 on CTE31.a1=alias2.a
> ),
> CTE2 as (
> WITH
> alias as (SELECT 1 as a),
> alias2 as (select t2.a a1, t1.a from alias t1 join (SELECT 1 as a) t2 on t1.a=t2.a),
> CTE32 as (select t2.a from alias2 t2)
> SELECT CTE32.a FROM alias2 join CTE32 on CTE32.a=alias2.a
> )
> select * from CTE1 as T1 join CTE2 as T2 on T1.a1=T2.a
> {code}
> It returns 4 rows (as expected) if based on MySQL table but it returns 16 rows if pushed down to PostgreSQL.
> I don't know if the two behaviors are related or not, but I can create a different ticket for the second issue, if needed.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 5 months
[JBoss JIRA] (TEIID-4312) INNER and LEFT joins of CTEs fail or return incorrect results
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4312?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-4312:
----------------------------------
Component/s: Query Engine
Fix Version/s: 9.1
9.0.1
Affects Version/s: 9.0
> INNER and LEFT joins of CTEs fail or return incorrect results
> -------------------------------------------------------------
>
> Key: TEIID-4312
> URL: https://issues.jboss.org/browse/TEIID-4312
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 9.1, 9.0.1
>
>
> I defined a table "test_a" with the same data and structure in PostgreSQL and MySQL:
> {code:sql}
> CREATE TABLE test_a(a integer, b integer);
> INSERT INTO test_a VALUES (1, 1);
> INSERT INTO test_a VALUES (1, 2);
> INSERT INTO test_a VALUES (2, 1);
> INSERT INTO test_a VALUES (2, 2);
> INSERT INTO test_a VALUES (3, 2);
> INSERT INTO test_a VALUES (3, 10);
> {code}
> The following query, based on the table in *PostgreSQL*, fails:
> {code:sql}
> with
> CTE1 as (
> WITH
> CTE11 as (SELECT a from pg.test_a),
> CTE21 as (select t1.a from CTE11 t1 join CTE11 t2 on t1.a=t2.a),
> CTE31 as (select a from CTE21)
> SELECT CTE31.a FROM CTE21 join CTE31 on CTE31.a=CTE21.a
> )
> select * from CTE1
> {code}
> with this exception:
> {code:sql}
> 16:36:28,752 WARN [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue114) eYLiZgMIChSF Connector worker process failed for atomic-request=eYLiZgMIChSF.11.2.24: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH CTE11 (a) AS (SELECT NULL FROM "public"."test_a" AS g_0), CTE21 (a) AS (SELECT g_0.a FROM CTE11 AS g_0, CTE11 AS g_1 WHERE g_0.a = g_1.a) SELECT g_1.a FROM CTE21 AS g_0, CTE21 AS g_1 WHERE g_1.a = g_0.a]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> at sun.reflect.GeneratedMethodAccessor94.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy56.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> 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)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.postgresql.util.PSQLException: ERROR: failed to find conversion function from unknown to text
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 17 more
> {code}
> The same query based on the table in *MySQL* wrongly returns an empty result.
> The main differences is that the query is fully pushed down to PostgreSQL but it isn't in MySQL.
> Regarding wrong results, I am also experiencing a similar problem with the following query:
> {code:sql}
> with
> CTE1 as (
> WITH
> alias as (SELECT a from pg.test_a),
> alias2 as (select t2.a as a1, t1.a from alias t1 join (SELECT 1 as a) t2 on t1.a=t2.a),
> CTE31 as (select t2.a as a1 from alias2 t2)
> SELECT CTE31.a1 FROM alias2 join CTE31 on CTE31.a1=alias2.a
> ),
> CTE2 as (
> WITH
> alias as (SELECT 1 as a),
> alias2 as (select t2.a a1, t1.a from alias t1 join (SELECT 1 as a) t2 on t1.a=t2.a),
> CTE32 as (select t2.a from alias2 t2)
> SELECT CTE32.a FROM alias2 join CTE32 on CTE32.a=alias2.a
> )
> select * from CTE1 as T1 join CTE2 as T2 on T1.a1=T2.a
> {code}
> It returns 4 rows (as expected) if based on MySQL table but it returns 16 rows if pushed down to PostgreSQL.
> I don't know if the two behaviors are related or not, but I can create a different ticket for the second issue, if needed.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 5 months
[JBoss JIRA] (TEIID-4032) Olingo V4
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4032?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-4032.
---------------------------------
Fix Version/s: 8.12.5
Resolution: Done
> Olingo V4
> ---------
>
> Key: TEIID-4032
> URL: https://issues.jboss.org/browse/TEIID-4032
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.12.5
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Fix For: 8.12.5
>
>
> This is the root JIRA for all bugs in Oingo V4 we have found during testing. We will add separate JIRAs for every issue as a subtask.
> All sub-JIRAs have in common:
> *DDL for H2 database:*
> {code:sql}
> -- simple table for basic tests
> DROP TABLE IF EXISTS SimpleTable;
> CREATE TABLE SimpleTable(
> intkey int PRIMARY KEY,
> intnum int,
> stringkey varchar(20),
> stringval varchar(20),
> booleanval boolean,
> decimalval decimal(20, 10),
> timeval time,
> dateval date,
> timestampval timestamp,
> clobval clob);
> INSERT INTO SimpleTable (intkey, intnum, stringkey, stringval, booleanval, decimalval, timeval, dateval, timestampval, clobval) VALUES
> (1, null, '1', 'value_1', 'true', 20.1, '00:00:01', '2000-01-01', '2001-01-01 00:00:01.01', 'clob_value_00001'),
> (2, 2, null, 'value_2', 'false', -20.2, '00:00:02', '2000-02-02', '2002-01-01 00:00:02.02', 'clob_value_00002'),
> (3, 3, '3', null, 'true', 20.3, '00:00:03', '2000-03-03', '2003-01-01 00:00:03.03', 'clob_value_00003'),
> (4, 4, '4', 'value_4', null, -20.4, '00:00:04', '2000-04-04', '2004-01-01 00:00:04.04', 'clob_value_00004'),
> (5, 5, '5', 'value_5', 'true', null, '00:00:05', '2000-05-05', '2005-01-01 00:00:05.05', 'clob_value_00005'),
> (6, 6, '6', 'value_6', 'false', -20.6, null, '2000-06-06', '2006-01-01 00:00:06.06', 'clob_value_00006'),
> (7, 7, '7', 'value_7', 'true', 20.7, '00:00:07', null, '2007-01-01 00:00:07.07', 'clob_value_00007'),
> (8, 8, '8', 'value_8', 'false', -20.8, '00:00:08', '2000-08-08', null, 'clob_value_00008'),
> (9, 9, '9', 'value_9', 'true', 20.9, '00:00:09', '2000-07-09', '2009-01-01 00:00:09.09', null),
> (10, null, null, null, null, null, null, null, null, null),
> (11, 11, '11', 'value_0', 'true', 21.1, '00:00:11', '2000-11-11', '2011-11-11 00:00:11.11', 'clob_value_00011');
> -- end simple table
> -- simple data table for filter functions tests
> DROP TABLE IF EXISTS SimpleDataTable;
> CREATE TABLE SimpleDataTable(
> intkey int PRIMARY KEY,
> doubleval double,
> realval real,
> decimalval decimal(20, 10),
> stringval varchar(20),
> timeval time,
> dateval date,
> timestampval timestamp);
> INSERT INTO SimpleDataTable (intkey, doubleval, realval, decimalval, stringval, timeval, dateval, timestampval) VALUES
> (1, 42.22, 42.22, 42.22, 'value_contains', '01:01:01', '2001-01-01', '2001-01-01 01:01:01.01'),
> (2, -42.22, -42.22, -42.22, 'value_endswith', '02:02:02', '2002-02-02', '2002-02-02 02:02:02.02'),
> (3, 42.32, 42.32, 42.32, 'value_startswith', '03:03:03', '2003-03-03', '2003-03-03 03:03:03.03'),
> (4, -42.32, -42.32, -42.32, 'value_length', '04:04:04', '2004-04-04', '2004-04-04 04:04:04.04'),
> (5, 42.42, 42.42, 42.42, 'VALUE_LOWER', '05:05:05', '2015-05-05', '2015-05-05 05:05:05.05'),
> (6, -42.42, -42.42, -42.42, ' value_trim ', '06:06:06', '2016-06-06', '2016-06-06 06:06:06.06'),
> (7, 42.52, 42.52, 42.52, ' value_trim_l', '17:17:17', '2017-07-07', '2017-07-07 17:17:17.07'),
> (8, -42.52, -42.52, -42.52, 'value_trim_r ', '18:18:18', '2018-08-08', '2017-08-08 18:18:18.08'),
> (9, 42.62, 42.62, 42.62, 'value_abcd', '19:19:19', '2019-09-09', '2018-09-09 19:19:19.09'),
> (10, -42.62, -42.62, -42.62, 'value_asdf', '20:20:20', '2020-10-20', '2019-10-20 20:20:20.10'),
> (11, 42.72, 42.72, 42.72, 'value_dabc', '21:21:21', '2021-11-25', '2021-11-25 21:21:21.11'),
> (12, -42.72, -42.72, -42.72, 'value_end', '23:23:23', '2042-12-30', '2042-12-30 23:23:23.12');
> -- end simple data table
> -- CUD tables
> -- delete
> DROP TABLE IF EXISTS DeleteTable;
> CREATE TABLE DeleteTable(id int PRIMARY KEY, name varchar(10));
> INSERT INTO DeleteTable (id, name) VALUES
> (1, 'name1'),
> (2, 'name2'),
> (3, 'name3');
> DROP TABLE IF EXISTS DeletePropTable;
> CREATE TABLE DeletePropTable(id int PRIMARY KEY, name varchar(10));
> INSERT INTO DeletePropTable (id, name) VALUES
> (1, 'name1');
> -- put
> DROP TABLE IF EXISTS PutTable;
> CREATE TABLE PutTable(
> intkey int PRIMARY KEY, intnum int, stringkey varchar(20), stringval varchar(20), booleanval boolean,
> decimalval decimal(20, 10), timeval time, dateval date, timestampval timestamp, clobval clob);
> INSERT INTO PutTable (intkey, intnum, stringkey, stringval, booleanval, decimalval, timeval, dateval, timestampval, clobval) VALUES
> (1, null, '1', 'value_1', 'true', 20.1, '00:00:01', '2000-01-01', '2001-01-01 00:00:01.01', 'clob_value_00001'),
> (2, 2, '2', 'value_2', 'false', -20.2, '00:00:02', '2000-02-02', '2002-01-01 00:00:02.02', 'clob_value_00002'),
> (3, null, null, null, null, null, null, null, null, null);
> DROP TABLE IF EXISTS PutPropTable;
> CREATE TABLE PutPropTable(id int PRIMARY KEY, name varchar(10));
> INSERT INTO PutPropTable (id, name) VALUES
> (1, 'name1');
> -- patch
> DROP TABLE IF EXISTS PatchTable;
> CREATE TABLE PatchTable(
> intkey int PRIMARY KEY, intnum int, stringkey varchar(20), stringval varchar(20), booleanval boolean,
> decimalval decimal(20, 10), timeval time, dateval date, timestampval timestamp, clobval clob);
> INSERT INTO PatchTable (intkey, intnum, stringkey, stringval, booleanval, decimalval, timeval, dateval, timestampval, clobval) VALUES
> (1, null, '1', 'value_1', 'true', 20.1, '00:00:01', '2000-01-01', '2001-01-01 00:00:01.01', 'clob_value_00001'),
> (2, 2, '2', 'value_2', 'false', -20.2, '00:00:02', '2000-02-02', '2002-01-01 00:00:02.02', 'clob_value_00002'),
> (3, null, null, null, null, null, null, null, null, null);
> -- post
> DROP TABLE IF EXISTS PostTable;
> CREATE TABLE PostTable(
> intkey int PRIMARY KEY, intnum int, stringkey varchar(20), stringval varchar(20), booleanval boolean,
> decimalval decimal(20, 10), timeval time, dateval date, timestampval timestamp, clobval clob);
> INSERT INTO PostTable (intkey, intnum, stringkey, stringval, booleanval, decimalval, timeval, dateval, timestampval, clobval) VALUES
> (1, null, '1', 'value_1', 'true', 20.1, '00:00:01', '2000-01-01', '2001-01-01 00:00:01.01', 'clob_value_00001'),
> (2, 2, '2', 'value_2', 'false', -20.2, '00:00:02', '2000-02-02', '2002-01-01 00:00:02.02', 'clob_value_00002'),
> (3, null, null, null, null, null, null, null, null, null);
> -- end CUD tables
> -- not updatable
> DROP TABLE IF EXISTS DeleteTableUnmod;
> CREATE TABLE DeleteTableUnmod(id int PRIMARY KEY, name varchar(10));
> INSERT INTO DeleteTableUnmod (id, name) VALUES
> (1, 'name1');
> DROP TABLE IF EXISTS PutTableUnmod;
> CREATE TABLE PutTableUnmod(id int PRIMARY KEY, name varchar(10));
> INSERT INTO PutTableUnmod (id, name) VALUES
> (1, 'name1');
> DROP TABLE IF EXISTS PostTableUnmod;
> CREATE TABLE PostTableUnmod(id int PRIMARY KEY, name varchar(10));
> INSERT INTO PostTableUnmod (id, name) VALUES
> (1, 'name1');
> DROP TABLE IF EXISTS PatchTableUnmod;
> CREATE TABLE PatchTableUnmod(id int PRIMARY KEY, name varchar(10));
> INSERT INTO PatchTableUnmod (id, name) VALUES
> (1, 'name1');
> -- table Customers and table Orders
> DROP TABLE IF EXISTS Customers;
> DROP TABLE IF EXISTS Orders;
> CREATE TABLE Customers(id int PRIMARY KEY, name varchar(10));
> CREATE TABLE Orders(id int PRIMARY KEY, customerid int, place varchar(10), FOREIGN KEY (customerid) REFERENCES Customers(id));
> INSERT INTO Customers (id, name) VALUES
> (1, 'customer1'),
> (2, 'customer2'),
> (3, 'customer3'),
> (4, 'customer4');
> INSERT INTO Orders (id, customerid, place) VALUES
> (1, 1, 'town'),
> (2, 1, 'state'),
> (3, 1, 'country'),
> (4, 1, 'abroad'),
> (5, 2, 'state'),
> (6, 2, 'country'),
> (7, 3, 'town'),
> (8, 3, 'town');
> -- end table Customers and table Orders
> {code}
> *VDB:*
> {code:xml}
> <vdb name="olingo_basic" version="1">
> <model name="Source" type="PHYSICAL">
> <source name="local_h2_db" connection-jndi-name="java:/localH2DB" translator-name="h2"/>
> <metadata type="DDL">
> CREATE FOREIGN TABLE SimpleTable(
> intkey integer PRIMARY KEY,
> intnum integer,
> stringkey varchar(20),
> stringval varchar(20),
> booleanval boolean,
> decimalval decimal(20, 10),
> timeval time,
> dateval date,
> timestampval timestamp,
> clobval clob) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.SIMPLETABLE');
>
> CREATE FOREIGN TABLE SimpleDataTable(
> intkey integer PRIMARY KEY,
> doubleval double,
> realval real,
> decimalval decimal(20, 10),
> stringval varchar(20),
> timeval time,
> dateval date,
> timestampval timestamp) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.SIMPLEDATATABLE');
> CREATE FOREIGN TABLE PutTable(
> intkey integer PRIMARY KEY,
> intnum integer,
> stringkey varchar(20),
> stringval varchar(20),
> booleanval boolean,
> decimalval decimal(20, 10),
> timeval time,
> dateval date,
> timestampval timestamp,
> clobval object) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.PUTTABLE', UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE PostTable(
> intkey integer PRIMARY KEY,
> intnum integer,
> stringkey varchar(20),
> stringval varchar(20),
> booleanval boolean,
> decimalval decimal(20, 10),
> timeval time,
> dateval date,
> timestampval timestamp,
> clobval object) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.POSTTABLE', UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE PatchTable(
> intkey integer PRIMARY KEY,
> intnum integer,
> stringkey varchar(20),
> stringval varchar(20),
> booleanval boolean,
> decimalval decimal(20, 10),
> timeval time,
> dateval date,
> timestampval timestamp,
> clobval object) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.PATCHTABLE', UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE DeleteTable (
> id integer PRIMARY KEY,
> name varchar(10)) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.DELETETABLE', UPDATABLE 'TRUE');
>
>
> CREATE FOREIGN TABLE DeletePropTable (
> id integer PRIMARY KEY,
> name varchar(10)) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.DELETEPROPTABLE', UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE PutPropTable (
> id integer PRIMARY KEY,
> name varchar(10)) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.PUTPROPTABLE', UPDATABLE 'TRUE');
>
>
> CREATE FOREIGN TABLE DeleteTableUnmod (
> id integer PRIMARY KEY,
> name varchar(10)) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.DELETETABLEUNMOD', UPDATABLE 'FALSE');
> CREATE FOREIGN TABLE PutTableUnmod (
> id integer PRIMARY KEY,
> name varchar(10)) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.PUTTABLEUNMOD', UPDATABLE 'FALSE');
> CREATE FOREIGN TABLE PostTableUnmod (
> id integer PRIMARY KEY,
> name varchar(10)) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.POSTTABLEUNMOD', UPDATABLE 'FALSE');
> CREATE FOREIGN TABLE PatchTableUnmod (
> id integer PRIMARY KEY,
> name varchar(10)) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.PATCHTABLEUNMOD', UPDATABLE 'FALSE');
>
>
> CREATE FOREIGN TABLE Customers (
> idcust integer PRIMARY KEY OPTIONS (NAMEINSOURCE 'id'),
> name varchar(10)) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.CUSTOMERS');
> CREATE FOREIGN TABLE Orders (
> idords integer PRIMARY KEY OPTIONS (NAMEINSOURCE 'id'),
> customerid integer,
> place varchar(10),
> FOREIGN KEY (customerid) REFERENCES Customers(idcust)) OPTIONS (NAMEINSOURCE 'DB.PUBLIC.ORDERS');
> CREATE VIEW SimpleTableView(
> intkey integer PRIMARY KEY,
> intnum integer,
> stringkey string,
> stringval string,
> booleanval boolean,
> decimalval bigdecimal,
> timeval time,
> dateval date,
> timestampval timestamp,
> clobval object)
> AS
> SELECT
> intkey, intnum, stringkey, stringval, booleanval, decimalval, timeval, dateval, timestampval, convert(clobval, object)
> FROM
> SimpleTable;
> </metadata>
> </model>
> </vdb>
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 5 months
[JBoss JIRA] (TEIID-4317) Always init translator capabilities
by Debbie Steigner (JIRA)
Debbie Steigner created TEIID-4317:
--------------------------------------
Summary: Always init translator capabilities
Key: TEIID-4317
URL: https://issues.jboss.org/browse/TEIID-4317
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 8.12.5
Reporter: Debbie Steigner
Assignee: Steven Hawkins
there is init logic on the impala, pg, and sqlserver translators that won't get run if the database version is explicitly set on the translator. Can it be changed to always run init logic?
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 5 months
[JBoss JIRA] (TEIID-4237) INNER JOIN returns incorrect results
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4237?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4237:
------------------------------------------------
Lucie Fabrikova <lfabriko(a)redhat.com> changed the Status of [bug 1341763|https://bugzilla.redhat.com/show_bug.cgi?id=1341763] from ON_QA to VERIFIED
> INNER JOIN returns incorrect results
> ------------------------------------
>
> Key: TEIID-4237
> URL: https://issues.jboss.org/browse/TEIID-4237
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12
> Environment: teiid-8.12-Beta1 on Red Hat JBoss Enterprise Application Platform - Version 6.3.0.GA
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 9.0, 8.7.6, 8.12.5, 8.7.7.6_2, 8.13.5
>
>
> Running the following query:
> {code:sql}
> select avg(t1.a) from
> (select 3 as a, 3 as b union all
> select 1 as a, 1 as b union all
> select 3 as a, 3 as b) as t1
> join (select 1 as a, 1 as b union all
> select 1 as a, 1 as b union all
> select 2 as a, 2 as b union all
> select 2 as a, 2 as b union all
> select 3 as a, 3 as b union all
> select 3 as a, 3 as b) as t2 on t1.a=t2.a
> {code}
> on teiid causes incorrect result: 2 in comparing with mysql which returns 2.333.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 5 months