Unhelpful error message: "Comparisons between (types) are not supported"
without showing expressions
----------------------------------------------------------------------------------------------------
Key: DERBY-4966
URL: https://issues.apache.org/jira/browse/DERBY-4966
Project: Derby
Issue Type: Bug
Affects Versions: 10.7.1.1
Reporter: Chris Wilson
java.sql.SQLSyntaxErrorException: Comparisons between 'INTEGER' and 'VARCHAR
(UCS_BASIC)' are not supported. Types must be comparable. String types must
also have matching collation. If collation does not match, a possible solution
is to cast operands to force them to the default collation (e.g. SELECT
tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at sun.reflect.GeneratedMethodAccessor647.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at
com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:525)
at
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: java.sql.SQLException: Comparisons between 'INTEGER' and 'VARCHAR
(UCS_BASIC)' are not supported. Types must be comparable. String types must
also have matching collation. If collation does not match, a possible solution
is to cast operands to force them to the default collation (e.g. SELECT
tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
... 18 more
Caused by: ERROR 42818: Comparisons between 'INTEGER' and 'VARCHAR (UCS_BASIC)'
are not supported. Types must be comparable. String types must also have
matching collation. If collation does not match, a possible solution is to cast
operands to force them to the default collation (e.g. SELECT tablename FROM
sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindComparisonOperator(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown
Source)
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown
Source)
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown
Source)
at org.apache.derby.impl.sql.compile.OrNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown
Source)
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
... 12 more
This is generated by the following query:
SELECT
project.name AS project_name,
project.web_address AS project_web_address,
project.email_address AS project_email_address,
producing_site.site_name AS producing_site_name,
TRIM(project_site.site_prefix)
|| '-'
|| SUBSTR('0000', 1, 4 - LENGTH(TRIM(CAST(request.seqno AS CHAR(10)))))
|| TRIM(CAST(request.seqno AS CHAR(10)))
|| suffix
AS cmr_number,
sender_org.name AS sender_org_name,
request.owners_ref AS request_owners_ref,
request.track_idnf AS request_track_idnf,
origin_site.site_name AS province,
origin.id AS origin_id,
origin.name AS origin_name,
origin.address1 AS origin_address1,
destination.id AS destination_id,
destination.name AS destination_name,
destination.address1 AS destination_address1,
line.id AS line_id,
line.seqno AS line_seqno,
product.description AS product_description,
line.ownr_item_desc AS line_ownr_item_desc,
line.ltu_qty AS line_qty,
line.total_weight / 1000 AS line_weight,
line.total_volume AS line_volume,
DATE(journey.est_dispatch_time) AS dispatch_date,
dispatch.movement_ledger_code AS movement_ledger,
SUM(CASE WHEN dispatch.movement_ledger_code = 'P' THEN -1 ELSE 1 END *
dispatch.ltu_qty) as dispatch_qty,
SUM(CASE WHEN dispatch.movement_ledger_code = 'P' THEN -1 ELSE 1 END *
dispatch.total_weight) / 1000 as dispatch_weight,
SUM(CASE WHEN dispatch.movement_ledger_code = 'P' THEN -1 ELSE 1 END *
dispatch.total_volume) as dispatch_volume,
CASE
WHEN dispatch.movement_ledger_code = 'S' AND dispatch.ltu_qty > 0 THEN
'Received'
WHEN dispatch.movement_ledger_code = 'S' AND dispatch.ltu_qty < 0 THEN
'Dispatched'
WHEN dispatch.movement_ledger_code = 'P' THEN 'Planned for Dispatch'
WHEN dispatch.movement_ledger_code = 'X' AND dispatch.ltu_qty > 0 THEN
'Planned to Collect & Deliver'
WHEN dispatch.movement_ledger_code = 'X' AND dispatch.ltu_qty < 0 THEN
'Collected & Delivered'
ELSE 'Unknown'
END AS movement_description,
ltu.description AS ltu_name
FROM
movement dispatch
INNER JOIN request_line line
ON dispatch.request_site_id = line.request_site_id
AND dispatch.request_line_id = line.id
INNER JOIN product_ltu AS ltu
ON ltu.id = line.ltu_id
INNER JOIN request request
ON line.request_site_id = request.request_site_id
AND request.id = line.request_id
INNER JOIN product product
ON line.product_id = product.id
INNER JOIN project_site project_site
ON request.project_id = project_site.project_id
AND project_site.site_id = request.request_site_id
INNER JOIN project project
ON request.project_id = project.id
INNER JOIN shipment shipment
ON dispatch.shipment_site_id = shipment.shipment_site_id
AND shipment.id = dispatch.shipment_id
INNER JOIN location origin
ON origin.location_site_id = dispatch.affected_location_site_id
AND origin.id = dispatch.affected_location_id
INNER JOIN site origin_site
ON origin.parent_site_id = origin_site.id
INNER JOIN location destination
ON destination.location_site_id = shipment.destination_location_site_id
AND destination.id = shipment.destination_location_id
INNER JOIN site destination_site
ON destination.parent_site_id = destination_site.id
INNER JOIN journey journey
ON shipment.shipment_site_id = journey.journey_site_id
AND journey.id = shipment.journey_id
-- LEFT JOIN vehicle_category ON vehicle_category.id =
journey.vehicle_category_id
-- AND journey.id = shipment.journey_id
INNER JOIN contact sender ON sender.owner_site_id = request.sender_site_id
AND sender.id = request.sender_contact_id
INNER JOIN org sender_org ON sender_org.id = sender.org_id
INNER JOIN site AS producing_site ON producing_site.id = $P{Site_ID}
WHERE movement_ledger_code IN ('C','S','P','X')
AND project.id IN ($P{Project_ID})
AND journey.is_deleted = 0
AND shipment.is_deleted = 0
AND dispatch.is_deleted = 0
AND ($P{Restrict_To_Active} = 0 OR request.status_code IN ('AC','IP'))
AND ($P{Specific_Request} IS NULL
OR $P{Specific_Request} = ''
OR
(
project_site.site_prefix = SUBSTR($P{Specific_Request}, 1, 3) AND
request.seqno = SUBSTR($P{Specific_Request}, 5, 4) AND
request.suffix = SUBSTR($P{Specific_Request}, 9)
)
)
GROUP BY
project.name,
project.web_address,
project.email_address,
producing_site.site_name,
project_site.site_prefix,
request.seqno,
request.suffix,
sender_org.name,
request.owners_ref,
request.track_idnf,
origin_site.site_name,
origin.name,
origin.address1,
destination.name,
destination.address1,
line.seqno,
product.description,
line.ownr_item_desc,
line.ltu_qty,
ltu.description,
journey.est_dispatch_time,
dispatch.movement_ledger_code,
CASE WHEN movement_ledger_code IN ('S','X') AND dispatch.ltu_qty > 0 THEN 1
ELSE 0 END
As you can imagine, it takes quite some work to figure out which expressions
it's complaining about.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
