Please run this sql-script in your h2 database.
--Test-statement
--EXPLAIN
SELECT * FROM (
--Test dataset
SELECT * FROM VALUES(1) UNION ALL SELECT * FROM VALUES(2) UNION ALL SELECT
* FROM VALUES(5)
-----------------------
) WHERE C1 > SOME (
SELECT C1 FROM (
--Test dataset
SELECT C1 FROM VALUES(1) UNION ALL SELECT C1 FROM VALUES(2) UNION ALL
SELECT C1 FROM VALUES(5)
-----------------------
) WHERE C1 > 1
)
UNION ALL
--Test-Statement after EXPLAIN
SELECT
_2.C1
FROM (
((SELECT
TABLE.C1
FROM TABLE(C1 INTEGER=(1,))
/* function */)
UNION ALL
(SELECT
TABLE.C1
FROM TABLE(C1 INTEGER=(2,))
/* function */))
UNION ALL
(SELECT
TABLE.C1
FROM TABLE(C1 INTEGER=(5,))
/* function */)
) _2
/* ((SELECT
TABLE.C1
FROM TABLE(C1 INTEGER=(1,))
/++ function: C1 IS ?1 ++/
WHERE TABLE.C1 IS ?1)
UNION ALL
(SELECT
TABLE.C1
FROM TABLE(C1 INTEGER=(2,))
/++ function: C1 IS ?1 ++/
WHERE TABLE.C1 IS ?1))
UNION ALL
(SELECT
TABLE.C1
FROM TABLE(C1 INTEGER=(5,))
/++ function: C1 IS ?1 ++/
WHERE TABLE.C1 IS ?1): C1 IN(SELECT
C1
FROM (
((SELECT
C1
FROM TABLE(C1 INTEGER=(1,))
/++ function ++/)
UNION ALL
(SELECT
C1
FROM TABLE(C1 INTEGER=(2,))
/++ function ++/))
UNION ALL
(SELECT
C1
FROM TABLE(C1 INTEGER=(5,))
/++ function ++/)
) _3
/++ ((SELECT
C1
FROM TABLE(C1 INTEGER=(1,))
/++ function: C1 >= ?1 ++/
WHERE C1 >= ?1)
UNION ALL
(SELECT
C1
FROM TABLE(C1 INTEGER=(2,))
/++ function: C1 >= ?1 ++/
WHERE C1 >= ?1))
UNION ALL
(SELECT
C1
FROM TABLE(C1 INTEGER=(5,))
/++ function: C1 >= ?1 ++/
WHERE C1 >= ?1): C1 > 1
++/
WHERE C1 > 1)
*/
WHERE C1 IN(
SELECT
C1
FROM (
((SELECT
C1
FROM TABLE(C1 INTEGER=(1,))
/* function */)
UNION ALL
(SELECT
C1
FROM TABLE(C1 INTEGER=(2,))
/* function */))
UNION ALL
(SELECT
C1
FROM TABLE(C1 INTEGER=(5,))
/* function */)
) _3
/* ((SELECT
C1
FROM TABLE(C1 INTEGER=(1,))
/++ function: C1 >= ?1 ++/
WHERE C1 >= ?1)
UNION ALL
(SELECT
C1
FROM TABLE(C1 INTEGER=(2,))
/++ function: C1 >= ?1 ++/
WHERE C1 >= ?1))
UNION ALL
(SELECT
C1
FROM TABLE(C1 INTEGER=(5,))
/++ function: C1 >= ?1 ++/
WHERE C1 >= ?1): C1 > 1
*/
WHERE C1 > 1)
Is the result-set C1(5,2,5)?
On Thursday, 9 January 2014 11:34:03 UTC+1, Thomas Mueller wrote:
>
> Hi,
>
> Sorry I can't reproduce the problem with the latest version (1.3.174).
> Maybe it is already fixed?
>
> Regards,
> Thomas
>
>
>
> On Wed, Jan 8, 2014 at 11:50 PM, <[email protected] <javascript:>> wrote:
>
>> Test-statement:
>>
>> //Library: h2-1.3.170.jar
>>
>> --EXPLAIN
>> SELECT * FROM (
>> --Test dataset
>> SELECT * FROM VALUES(1)
>> UNION ALL
>> SELECT * FROM VALUES(2)
>> UNION ALL
>> SELECT * FROM VALUES(5)
>> -----------------------
>> ) WHERE C1 > SOME (
>> SELECT C1 FROM (
>> --Test dataset
>> SELECT C1 FROM VALUES(1)
>> UNION ALL
>> SELECT C1 FROM VALUES(2)
>> UNION ALL
>> SELECT C1 FROM VALUES(5)
>> -----------------------
>> ) WHERE C1 > 1
>> )
>>
>> Resultset: C1(5). That's right, C(1,2,5) > C(2,5).
>> Resultset of statement after explain: C1(2,5). This correspond to
>> C(1,2,5) >= C(2,5).
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> To post to this group, send email to [email protected]<javascript:>
>> .
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
