You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kathey Marsden (JIRA)" <ji...@apache.org> on 2011/01/13 00:06:06 UTC
[jira] Updated: (DERBY-4966) Unhelpful error message: "Comparisons
between (types) are not supported" without showing expressions" - Give
identifying detail on failing comparison
[ https://issues.apache.org/jira/browse/DERBY-4966?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-4966:
----------------------------------
Component/s: SQL
Issue Type: Improvement (was: Bug)
Summary: Unhelpful error message: "Comparisons between (types) are not supported" without showing expressions" - Give identifying detail on failing comparison (was: Unhelpful error message: "Comparisons between (types) are not supported" without showing expressions)
> Unhelpful error message: "Comparisons between (types) are not supported" without showing expressions" - Give identifying detail on failing comparison
> -----------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4966
> URL: https://issues.apache.org/jira/browse/DERBY-4966
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> 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.