[JBoss JIRA] (TEIID-5397) RETURN_GENERATED_KEYS not working
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5397?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5397.
-----------------------------------
Resolution: Explained
Marking as explained based upon Ramesh's comment.
There is no concept of a session level last_insert_id in Teiid. Generated keys functionality follows the JDBC paradigm and are captured for a given statement. In the case of a view without a key, no generated keys will be captured.
> RETURN_GENERATED_KEYS not working
> ---------------------------------
>
> Key: TEIID-5397
> URL: https://issues.jboss.org/browse/TEIID-5397
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.2.1
> Reporter: Lukáš Svačina
> Assignee: Steven Hawkins
>
> *+VDB:+*
> {code}
> <vdb name="_GENERATED_form_shoes" version="2">
> <model name="_INTERNAL_internalModel" type="PHYSICAL">
> <source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/>
> </model>
> <model name="GEN_view" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true')
> AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model",
> "public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]>
> </metadata>
> </model>
> </vdb>
> {code}
> *+TABLE:+*
> name: form_shoes_2
> columns: id (SERIAL) | size (INTEGER *NULLABLE*) | model (VARCHAR *NULLABLE*)
> *+PROBLEM:+*
> Connected into VDB using JDBC like:
> {code:java}
> final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS);
> statement.executeUpdate();
> final ResultSet generatedKeys = statement.getGeneratedKeys();
> {code}
> *generatedKeys is empty if:*
> # INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK!
> # INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here?
> *+QUESTIONS:+*
> # How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
> # How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?
> Thanks for fixing/adding this functionality.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5403) Support the use of variables in client SQL query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5403?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5403.
-----------------------------------
Resolution: Explained
If you want to issue this as a single statement, it can be enclosed as a anonymous procedure block which can also be directly sent from the client:
{code}
BEGIN
DECLARE string PARAM1 = cast(? as string);
SELECT ...
WHERE COLUMN1 = PARAM1
) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
WHERE TABLEA.COLUMN4 = PARAM1
...;
{code}
Note the cast of the bind variable, which is due to a small issue with the resolver that isn't inferring the type from the variable declaration.
You may also use the postgresql like feature (which is undocumented for our jdbc interface) of using $n bindings - where is the parameter number:
{code}
...
WHERE COLUMN1 = $1
) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
WHERE TABLEA.COLUMN4 = $1
...
{code}
> Support the use of variables in client SQL query
> ------------------------------------------------
>
> Key: TEIID-5403
> URL: https://issues.jboss.org/browse/TEIID-5403
> Project: Teiid
> Issue Type: Enhancement
> Reporter: SHI HONG CHIN
> Assignee: Steven Hawkins
>
> In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
> Example:
> {code:java}
> DECLARE @PARAM1 VARCHAR(100);
> SET @PARAM1 = ?;
> SELECT
> <SOME COLUMNS>
> FROM TABLEA LEFT OUTER JOIN (
> SELECT
> <SOME COLUMNS>
> FROM TABLEB
> WHERE COLUMN1 = @PARAM1
> ) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
> WHERE TABLEA.COLUMN4 = @PARAM1
> UNION ALL
> SELECT
> <SOME COLUMNS>
> FROM TABLEC
> WHERE TABLEC.COLUMN5 = @PARAM1;
> {code}
> In above example, "@PARAM1" is the local variable that been used multiple times in the query.
> The benefits:
> 1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
> 2. I no need to create a procedure.
> However, in Teiid, I cannot find anyway to achieve this.
> In Teiid, please implement the support of local variables in client SQL query.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5403) Support the use of variables in client SQL query
by SHI HONG CHIN (JIRA)
[ https://issues.jboss.org/browse/TEIID-5403?page=com.atlassian.jira.plugin... ]
SHI HONG CHIN updated TEIID-5403:
---------------------------------
Summary: Support the use of variables in client SQL query (was: Support the use of local variables in client SQL query)
> Support the use of variables in client SQL query
> ------------------------------------------------
>
> Key: TEIID-5403
> URL: https://issues.jboss.org/browse/TEIID-5403
> Project: Teiid
> Issue Type: Enhancement
> Reporter: SHI HONG CHIN
> Assignee: Steven Hawkins
>
> In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
> Example:
> {code:java}
> DECLARE @PARAM1 VARCHAR(100);
> SET @PARAM1 = ?;
> SELECT
> <SOME COLUMNS>
> FROM TABLEA LEFT OUTER JOIN (
> SELECT
> <SOME COLUMNS>
> FROM TABLEB
> WHERE COLUMN1 = @PARAM1
> ) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
> WHERE TABLEA.COLUMN4 = @PARAM1
> UNION ALL
> SELECT
> <SOME COLUMNS>
> FROM TABLEC
> WHERE TABLEC.COLUMN5 = @PARAM1;
> {code}
> In above example, "@PARAM1" is the local variable that been used multiple times in the query.
> The benefits:
> 1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
> 2. I no need to create a procedure.
> However, in Teiid, I cannot find anyway to achieve this.
> In Teiid, please implement the support of local variables in client SQL query.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5403) Support the use of local variables in client SQL query
by SHI HONG CHIN (JIRA)
[ https://issues.jboss.org/browse/TEIID-5403?page=com.atlassian.jira.plugin... ]
SHI HONG CHIN updated TEIID-5403:
---------------------------------
Description:
In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
Example:
{code:java}
DECLARE @PARAM1 VARCHAR(100);
SET @PARAM1 = ?;
SELECT
<SOME COLUMNS>
FROM TABLEA LEFT OUTER JOIN (
SELECT
<SOME COLUMNS>
FROM TABLEB
WHERE COLUMN1 = @PARAM1
) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
WHERE TABLEA.COLUMN4 = @PARAM1
UNION ALL
SELECT
<SOME COLUMNS>
FROM TABLEC
WHERE TABLEC.COLUMN5 = @PARAM1;
{code}
In above example, "@PARAM1" is the local variable that been used multiple times in the query.
The benefits:
1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
2. I no need to create a procedure.
However, in Teiid, I cannot find anyway to achieve this.
In Teiid, please implement the support of local variables in client SQL query.
was:
In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
Example:
{code:java}
DECLARE @PARAM1 VARCHAR(100);
SET @PARAM1 = ?;
SELECT
<SOME COLUMNS>
FROM TABLEA LEFT OUTER JOIN (
SELECT
<SOME COLUMNS>
FROM TABLEB
WHERE COLUMN1 = @PARAM1
) T1
WHERE TABLEA.COLUMN2 = @PARAM1
UNION ALL
SELECT
<SOME COLUMNS>
FROM TABLEC
WHERE TABLEC.COLUMN3 = @PARAM1;
{code}
In above example, "@PARAM1" is the local variable that been used multiple times in the query.
The benefits:
1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
2. I no need to create a procedure.
However, in Teiid, I cannot find anyway to achieve this.
In Teiid, please implement the support of local variables in client SQL query.
> Support the use of local variables in client SQL query
> ------------------------------------------------------
>
> Key: TEIID-5403
> URL: https://issues.jboss.org/browse/TEIID-5403
> Project: Teiid
> Issue Type: Enhancement
> Reporter: SHI HONG CHIN
> Assignee: Steven Hawkins
>
> In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
> Example:
> {code:java}
> DECLARE @PARAM1 VARCHAR(100);
> SET @PARAM1 = ?;
> SELECT
> <SOME COLUMNS>
> FROM TABLEA LEFT OUTER JOIN (
> SELECT
> <SOME COLUMNS>
> FROM TABLEB
> WHERE COLUMN1 = @PARAM1
> ) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
> WHERE TABLEA.COLUMN4 = @PARAM1
> UNION ALL
> SELECT
> <SOME COLUMNS>
> FROM TABLEC
> WHERE TABLEC.COLUMN5 = @PARAM1;
> {code}
> In above example, "@PARAM1" is the local variable that been used multiple times in the query.
> The benefits:
> 1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
> 2. I no need to create a procedure.
> However, in Teiid, I cannot find anyway to achieve this.
> In Teiid, please implement the support of local variables in client SQL query.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5403) Support the use of local variables in client SQL query
by SHI HONG CHIN (JIRA)
SHI HONG CHIN created TEIID-5403:
------------------------------------
Summary: Support the use of local variables in client SQL query
Key: TEIID-5403
URL: https://issues.jboss.org/browse/TEIID-5403
Project: Teiid
Issue Type: Enhancement
Reporter: SHI HONG CHIN
Assignee: Steven Hawkins
In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
Example:
{code:java}
DECLARE @PARAM1 VARCHAR(100);
SET @PARAM1 = ?;
SELECT
<SOME COLUMNS>
FROM TABLEA LEFT OUTER JOIN (
SELECT
<SOME COLUMNS>
FROM TABLEB
WHERE COLUMN1 = @PARAM1
) T1
WHERE TABLEA.COLUMN2 = @PARAM1
UNION ALL
SELECT
<SOME COLUMNS>
FROM TABLEC
WHERE TABLEC.COLUMN3 = @PARAM1;
{code}
In above example, "@PARAM1" is the local variable that been used multiple times in the query.
The benefits:
1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
2. I no need to create a procedure.
However, in Teiid, I cannot find anyway to achieve this.
In Teiid, please implement the support of local variables in client SQL query.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5395) Improvement of JPA translator join behavior
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5395?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5395:
----------------------------------
Issue Type: Bug (was: Enhancement)
Priority: Major (was: Minor)
Looks like there are some pretty big bugs that were addressed here. I'll evaluate the change for inclusion into 10.x as well.
> Improvement of JPA translator join behavior
> -------------------------------------------
>
> Key: TEIID-5395
> URL: https://issues.jboss.org/browse/TEIID-5395
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 10.2.1
> Reporter: Harold Campbell
> Assignee: Steven Hawkins
> Fix For: 11.0
>
>
> The behavior of the JPA connector has a number of issues regarding entity relationships.
> * Implicit joins (those needed to get child entity id's) are made INNER JOINS, preventing getting any rows where the value would be null.
> * Something odd is done with *ToMany relationships which blows up the size of resultsets.
> * If a parent entity and a child entity use the same name for their id property, only one gets mapped.
> * It's not possible to join the same entity more than once.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5402) UPSERT in batch mode.
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5402?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5402.
-----------------------------------
Fix Version/s: 10.3.2
10.2.3
Resolution: Done
Updated the logic to not assume the command is fully pushed down when batching is supported.
> UPSERT in batch mode.
> ---------------------
>
> Key: TEIID-5402
> URL: https://issues.jboss.org/browse/TEIID-5402
> Project: Teiid
> Issue Type: Bug
> Reporter: Kulbhushan Chaskar
> Assignee: Steven Hawkins
> Labels: Teiid
> Fix For: 11.0, 10.3.2, 10.2.3
>
>
> Unable to execute UPSERT in batch. If batch size is 1, then it executes well. If batch size greater than 1 then getting logs.
> I checked with MySQL database and SQLServer database.
> *MySQL logs:*
> py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
> , `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `aakash_test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
> ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
> ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
> ore detailed logging to see the entire stacktrace.
> 9:15:50,309 WARN [org.teiid.CONNECTOR] (Worker17_QueryProcessorQueue55) ws3B9oHdsaA9 Connector worker process failed for atomic-request=ws3B9oHdsaA9.0.6.11: org.teiid.
> ranslator.TranslatorBatchException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`acco
> nt` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`,
> pri`) VALUES (?, ?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> aused by: org.teiid.translator.jdbc.JDBCExecutionException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT
> NTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCO
> NT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
> ... 20 more
> aused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us
> near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1160)
> at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773)
> at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257)
> at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
> ... 21 more
> aused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio
> for the right syntax to use near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
> at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
> at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751)
> ... 25 more
> 9:15:50,338 WARN [org.teiid.PROCESSOR] (Worker16_QueryProcessorQueue56) ws3B9oHdsaA9 TEIID30020 Processing exception for request ws3B9oHdsaA9.0 'TEIID30504 MySqlConn_c
> py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
> , `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
> ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
> ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
> ore detailed logging to see the entire stacktrace.
> *SQLServer Logs:*
> count 44.
> 13:05:13,738 INFO [org.teiid.PROCESSOR.MATVIEWS] (Worker0_QueryProcessorQueue4) o8FeDIzU1i/F TEIID30014 Loaded materialized view table #MAT_PG_CATALOG.MATPG_DATATYPE wi
> th row count 52.
> 13:08:38,311 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue14) L+yP/GL+aiOm Connector worker process failed for atomic-request=L+yP/GL+aiOm.0.6.3: org.teiid.tr
> anslator.TranslatorBatchException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("cus
> tid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya']
> SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO
> "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prep
> ared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
> ... 20 more
> Caused by: java.sql.BatchUpdateException: Incorrect syntax near the keyword 'INTO'.
> at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
> ... 21 more
> 13:08:38,397 WARN [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue15) L+yP/GL+aiOm TEIID30020 Processing exception for request L+yP/GL+aiOm.0 'TEIID30504 sql_server:
> 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Pr
> epared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_ta
> b" ("custid", "custname") VALUES (?, ?)]'. Originally TeiidProcessingException 'Incorrect syntax near the keyword 'INTO'.' JtdsStatement.java:1069. Enable more detailed
> logging to see the entire stacktrace.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5402) UPSERT in batch mode.
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5402?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5402:
---------------------------------------
The issue manifests itself when a translator does not have support for upsert, but does have support for bulk update. The plan produced will still push an upsert which is not expected.
> UPSERT in batch mode.
> ---------------------
>
> Key: TEIID-5402
> URL: https://issues.jboss.org/browse/TEIID-5402
> Project: Teiid
> Issue Type: Bug
> Reporter: Kulbhushan Chaskar
> Assignee: Steven Hawkins
> Labels: Teiid
> Fix For: 11.0
>
>
> Unable to execute UPSERT in batch. If batch size is 1, then it executes well. If batch size greater than 1 then getting logs.
> I checked with MySQL database and SQLServer database.
> *MySQL logs:*
> py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
> , `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `aakash_test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
> ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
> ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
> ore detailed logging to see the entire stacktrace.
> 9:15:50,309 WARN [org.teiid.CONNECTOR] (Worker17_QueryProcessorQueue55) ws3B9oHdsaA9 Connector worker process failed for atomic-request=ws3B9oHdsaA9.0.6.11: org.teiid.
> ranslator.TranslatorBatchException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`acco
> nt` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`,
> pri`) VALUES (?, ?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> aused by: org.teiid.translator.jdbc.JDBCExecutionException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT
> NTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCO
> NT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
> ... 20 more
> aused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us
> near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1160)
> at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773)
> at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257)
> at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
> ... 21 more
> aused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio
> for the right syntax to use near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
> at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
> at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751)
> ... 25 more
> 9:15:50,338 WARN [org.teiid.PROCESSOR] (Worker16_QueryProcessorQueue56) ws3B9oHdsaA9 TEIID30020 Processing exception for request ws3B9oHdsaA9.0 'TEIID30504 MySqlConn_c
> py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
> , `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
> ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
> ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
> ore detailed logging to see the entire stacktrace.
> *SQLServer Logs:*
> count 44.
> 13:05:13,738 INFO [org.teiid.PROCESSOR.MATVIEWS] (Worker0_QueryProcessorQueue4) o8FeDIzU1i/F TEIID30014 Loaded materialized view table #MAT_PG_CATALOG.MATPG_DATATYPE wi
> th row count 52.
> 13:08:38,311 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue14) L+yP/GL+aiOm Connector worker process failed for atomic-request=L+yP/GL+aiOm.0.6.3: org.teiid.tr
> anslator.TranslatorBatchException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("cus
> tid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya']
> SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO
> "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prep
> ared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
> ... 20 more
> Caused by: java.sql.BatchUpdateException: Incorrect syntax near the keyword 'INTO'.
> at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
> ... 21 more
> 13:08:38,397 WARN [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue15) L+yP/GL+aiOm TEIID30020 Processing exception for request L+yP/GL+aiOm.0 'TEIID30504 sql_server:
> 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Pr
> epared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_ta
> b" ("custid", "custname") VALUES (?, ?)]'. Originally TeiidProcessingException 'Incorrect syntax near the keyword 'INTO'.' JtdsStatement.java:1069. Enable more detailed
> logging to see the entire stacktrace.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5401) MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5401?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5401.
-----------------------------------
Fix Version/s: 10.3.2
10.2.3
Resolution: Done
In RulePushLargeIn was too restrictive as it allowed only all constant sets, and not those for example containing references.
> MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
> ----------------------------------------------------------------------------
>
> Key: TEIID-5401
> URL: https://issues.jboss.org/browse/TEIID-5401
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 11.0, 10.3.2, 10.2.3
>
>
> When user sets translator override property MaxDependentInPredicates, then IN criteria is not pushed into underlying datasource, but only when using Prepared Statement.
> The pushdown is not dependent on the limit vs IN criteria size. Simply, if the property is set, then the pushdown is not performed.
> {code:xml|title=vdb}
> <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
> <vdb name="vdb-name" version="1">
> <model name="pm1" type="PHYSICAL">
> <source name="pm1" connection-jndi-name="java:/ds"
> translator-name="mysql5-override" />
> <metadata type="DDL">
> <![CDATA[
> CREATE FOREIGN TABLE g1(e1 integer, e2 string);
> ]]>
> </metadata>
> </model>
> <translator name="mysql5-override" type="mysql5">
> <property name="MaxInCriteriaSize" value="2" />
> <property name="MaxDependentInPredicates" value="2" />
> </translator>
> </vdb>
> {code}
> {code:sql|title=query}
> SELECT e2 FROM g1 WHERE e2 IN ('a', 'b', 'c', 'd', 'e', 'f') AND e1<5 ORDER BY e2
> {code}
> pushed as 2 src commands:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND (g_0.e2 IN ('a', 'b') OR g_0.e2 IN ('c', 'd'));
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND g_0.e2 IN ('e', 'f');
> {code}
> {code:java|title=preparedstatement}
> PreparedStatement ps = con
> .prepareStatement("SELECT e2 FROM g1 WHERE e2 IN (?, ?, ?, ?, ?, ?) AND e1<? ORDER BY e2");
> ps.setString(1, "a");
> ps.setString(2, "b");
> ps.setString(3, "c");
> ps.setString(4, "d");
> ps.setString(5, "e");
> ps.setString(6, "f");
> ps.setInt(7, 5);
> ResultSet rs2 = ps.executeQuery();
> {code}
> pushed as single SRC Command:
> {code:sql}
> SELECT g_0.e2 FROM g1 AS g_0 WHERE g_0.e1 < ?
> {code}
> when I remove the 'MaxDependentInPredicates' property, the ps gets pushed as:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE (g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?)) AND g_0.e1 < ? ORDER BY c_0
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5401) MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5401?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5401:
----------------------------------
Fix Version/s: 11.0
Upstream I cannot replicate the behavior for non-binding values, but can for binding values.
> MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
> ----------------------------------------------------------------------------
>
> Key: TEIID-5401
> URL: https://issues.jboss.org/browse/TEIID-5401
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 11.0
>
>
> When user sets translator override property MaxDependentInPredicates, then IN criteria is not pushed into underlying datasource, but only when using Prepared Statement.
> The pushdown is not dependent on the limit vs IN criteria size. Simply, if the property is set, then the pushdown is not performed.
> {code:xml|title=vdb}
> <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
> <vdb name="vdb-name" version="1">
> <model name="pm1" type="PHYSICAL">
> <source name="pm1" connection-jndi-name="java:/ds"
> translator-name="mysql5-override" />
> <metadata type="DDL">
> <![CDATA[
> CREATE FOREIGN TABLE g1(e1 integer, e2 string);
> ]]>
> </metadata>
> </model>
> <translator name="mysql5-override" type="mysql5">
> <property name="MaxInCriteriaSize" value="2" />
> <property name="MaxDependentInPredicates" value="2" />
> </translator>
> </vdb>
> {code}
> {code:sql|title=query}
> SELECT e2 FROM g1 WHERE e2 IN ('a', 'b', 'c', 'd', 'e', 'f') AND e1<5 ORDER BY e2
> {code}
> pushed as 2 src commands:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND (g_0.e2 IN ('a', 'b') OR g_0.e2 IN ('c', 'd'));
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND g_0.e2 IN ('e', 'f');
> {code}
> {code:java|title=preparedstatement}
> PreparedStatement ps = con
> .prepareStatement("SELECT e2 FROM g1 WHERE e2 IN (?, ?, ?, ?, ?, ?) AND e1<? ORDER BY e2");
> ps.setString(1, "a");
> ps.setString(2, "b");
> ps.setString(3, "c");
> ps.setString(4, "d");
> ps.setString(5, "e");
> ps.setString(6, "f");
> ps.setInt(7, 5);
> ResultSet rs2 = ps.executeQuery();
> {code}
> pushed as single SRC Command:
> {code:sql}
> SELECT g_0.e2 FROM g1 AS g_0 WHERE g_0.e1 < ?
> {code}
> when I remove the 'MaxDependentInPredicates' property, the ps gets pushed as:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE (g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?)) AND g_0.e1 < ? ORDER BY c_0
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months