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 "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2014/06/11 15:43:01 UTC
[jira] [Commented] (DERBY-6080) Cast to same type changes result
with IN subquery
[ https://issues.apache.org/jira/browse/DERBY-6080?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14027767#comment-14027767 ]
Knut Anders Hatlen commented on DERBY-6080:
-------------------------------------------
Maybe the difference between the two queries is how/if they are flattened. I see the same difference if I join the two tables and switch between hash join and nested-loop join:
{noformat}
ij> select * from --derby-properties joinOrder=fixed
t4, t5 --derby-properties joinStrategy=hash
where t4.b = t5.d;
B |D
---------------------------------------------
9223372036854775807 |9.223372036854776E18
1 row selected
ij> select * from --derby-properties joinOrder=fixed
t4, t5 --derby-properties joinStrategy=nestedloop
where t4.b = t5.d;
B |D
---------------------------------------------
9223372036854775806 |9.223372036854776E18
9223372036854775807 |9.223372036854776E18
2 rows selected
{noformat}
I'd expect the query to return the same number of rows regardless of join strategy.
> Cast to same type changes result with IN subquery
> -------------------------------------------------
>
> Key: DERBY-6080
> URL: https://issues.apache.org/jira/browse/DERBY-6080
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1
> Reporter: Knut Anders Hatlen
> Priority: Minor
> Labels: derby_triage10_11
>
> Given these tables
> ij> create table t4 (b bigint);
> 0 rows inserted/updated/deleted
> ij> insert into t4 values 9223372036854775806, 9223372036854775807;
> 2 rows inserted/updated/deleted
> ij> create table t5 (d double);
> 0 rows inserted/updated/deleted
> ij> insert into t5 values 9.223372036854776E18;
> 1 row inserted/updated/deleted
> the following two SELECT queries return different results:
> ij> select * from t4 where b in (select d from t5);
> B
> --------------------
> 9223372036854775807
> 1 row selected
> ij> select * from t4 where b in (select cast(d as double) from t5);
> B
> --------------------
> 9223372036854775806
> 9223372036854775807
> 2 rows selected
> Since the only difference is D vs CAST(D AS DOUBLE), and the type of D is already DOUBLE, I'd expect the two queries to be equivalent.
--
This message was sent by Atlassian JIRA
(v6.2#6252)