[JBoss JIRA] Created: (TEIID-934) MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc
4:10 p.m.
MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD,
REPLACE, etc
-------------------------------------------------------------------------------------------------
Key: TEIID-934
URL: https://jira.jboss.org/jira/browse/TEIID-934
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Reporter: Warren Gibson
Assignee: Steven Hawkins
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
--
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
8:02 p.m.
New subject: [JBoss JIRA] Commented: (TEIID-934) MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc
[
https://jira.jboss.org/jira/browse/TEIID-934?page=com.atlassian.jira.plug...
]
Steven Hawkins commented on TEIID-934:
--------------------------------------
I can see the problem with the 2 argument forms of lpad/rpad, but our replace matches
exactly with mysql's. Do you have more examples of failing queries?
MySql Queries - SQL syntax error on pushdown queries when using
functions like LPAD, REPLACE, etc
-------------------------------------------------------------------------------------------------
Key: TEIID-934
URL: https://jira.jboss.org/jira/browse/TEIID-934
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Reporter: Warren Gibson
Assignee: Steven Hawkins
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
--
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
9:05 a.m.
New subject: [JBoss JIRA] Updated: (TEIID-934) MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc
[
https://jira.jboss.org/jira/browse/TEIID-934?page=com.atlassian.jira.plug...
]
Steven Hawkins updated TEIID-934:
---------------------------------
Fix Version/s: 7.0
MySql Queries - SQL syntax error on pushdown queries when using
functions like LPAD, REPLACE, etc
-------------------------------------------------------------------------------------------------
Key: TEIID-934
URL: https://jira.jboss.org/jira/browse/TEIID-934
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Reporter: Warren Gibson
Assignee: Steven Hawkins
Fix For: 7.0
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
--
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
9:05 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-934) MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc
[
https://jira.jboss.org/jira/browse/TEIID-934?page=com.atlassian.jira.plug...
]
Steven Hawkins commented on TEIID-934:
--------------------------------------
The char function on integer values greater than 255 is a problem in MySql 5.0.15 and
later, since it returns a byte sequence.
MySql Queries - SQL syntax error on pushdown queries when using
functions like LPAD, REPLACE, etc
-------------------------------------------------------------------------------------------------
Key: TEIID-934
URL: https://jira.jboss.org/jira/browse/TEIID-934
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Reporter: Warren Gibson
Assignee: Steven Hawkins
Fix For: 7.0
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
--
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
1:05 p.m.
New subject: [JBoss JIRA] Updated: (TEIID-934) MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc
[
https://jira.jboss.org/jira/browse/TEIID-934?page=com.atlassian.jira.plug...
]
Warren Gibson updated TEIID-934:
--------------------------------
Description:
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
The following fiunctions are producing "error in your SQL Syntax" errors:
LPAD
RPAD
ASCII
UCASE
LCASE
LOWER
UPPER
LEFT
RIGHT
LENGTH
LOCATE
LTRIM
RTRIM
REPEAT
REPLACE
was:
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
MySql Queries - SQL syntax error on pushdown queries when using
functions like LPAD, REPLACE, etc
-------------------------------------------------------------------------------------------------
Key: TEIID-934
URL: https://jira.jboss.org/jira/browse/TEIID-934
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Reporter: Warren Gibson
Assignee: Steven Hawkins
Fix For: 7.0
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
The following fiunctions are producing "error in your SQL Syntax" errors:
LPAD
RPAD
ASCII
UCASE
LCASE
LOWER
UPPER
LEFT
RIGHT
LENGTH
LOCATE
LTRIM
RTRIM
REPEAT
REPLACE
--
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
10:09 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-934) MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc
[
https://jira.jboss.org/jira/browse/TEIID-934?page=com.atlassian.jira.plug...
]
Steven Hawkins commented on TEIID-934:
--------------------------------------
It turns out that the other functions do not have a problem. It's our use of boolean
as the target type in cast. I've updated the numeric type mapping for boolean to be
signed instead. However our testing schema is really odd having integer and boolean
columns represented by decimal.
MySql Queries - SQL syntax error on pushdown queries when using
functions like LPAD, REPLACE, etc
-------------------------------------------------------------------------------------------------
Key: TEIID-934
URL: https://jira.jboss.org/jira/browse/TEIID-934
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Reporter: Warren Gibson
Assignee: Steven Hawkins
Fix For: 7.0
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
The following fiunctions are producing "error in your SQL Syntax" errors:
LPAD
RPAD
ASCII
UCASE
LCASE
LOWER
UPPER
LEFT
RIGHT
LENGTH
LOCATE
LTRIM
RTRIM
REPEAT
REPLACE
--
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
10:11 a.m.
New subject: [JBoss JIRA] Resolved: (TEIID-934) MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc
[
https://jira.jboss.org/jira/browse/TEIID-934?page=com.atlassian.jira.plug...
]
Steven Hawkins resolved TEIID-934.
----------------------------------
Resolution: Done
Fixed the issues with boolean conversion, pad, and the multibyte results of the 5+ char
function.
MySql Queries - SQL syntax error on pushdown queries when using
functions like LPAD, REPLACE, etc
-------------------------------------------------------------------------------------------------
Key: TEIID-934
URL: https://jira.jboss.org/jira/browse/TEIID-934
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Reporter: Warren Gibson
Assignee: Steven Hawkins
Fix For: 7.0
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
The following fiunctions are producing "error in your SQL Syntax" errors:
LPAD
RPAD
ASCII
UCASE
LCASE
LOWER
UPPER
LEFT
RIGHT
LENGTH
LOCATE
LTRIM
RTRIM
REPEAT
REPLACE
--
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
10:04 a.m.
New subject: [JBoss JIRA] Closed: (TEIID-934) MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc
[
https://jira.jboss.org/browse/TEIID-934?page=com.atlassian.jira.plugin.sy...
]
Steve Hawkins closed TEIID-934.
-------------------------------
MySql Queries - SQL syntax error on pushdown queries when using
functions like LPAD, REPLACE, etc
-------------------------------------------------------------------------------------------------
Key: TEIID-934
URL: https://jira.jboss.org/browse/TEIID-934
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.0
Reporter: Warren Gibson
Assignee: Steven Hawkins
Fix For: 7.0
Several CTC queries are failing in MySql when they are pushed down to the source. See
query example and resulting SQL error below:
SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector
MySQL_Push<5> - Error Code:1064 Message: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
') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL:
SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE,
'%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
The following fiunctions are producing "error in your SQL Syntax" errors:
LPAD
RPAD
ASCII
UCASE
LCASE
LOWER
UPPER
LEFT
RIGHT
LENGTH
LOCATE
LTRIM
RTRIM
REPEAT
REPLACE
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5294
days inactive
5451
days old
7 comments
3 participants
participants (3)
-
Steve Hawkins (JIRA) -
Steven Hawkins (JIRA)
-
Warren Gibson (JIRA)