Hi,
This is not a bug in H2. The problem is that you used "WHERE
<condition>" instead of "ON <joinCondition>". H2 supports the omission
of an "ON" condition, and uses "ON 1=1" in this case.
Please note that using randomly generated UUIDs as the primary key
will result on poor performance once there are millions of rows in a
table. The reason is that the cache behavior is very bad with randomly
distributed data. This is a problem for any database system.
Regards,
Thomas
P.S. my test case gives the same results in all databases:
drop table container;
CREATE TABLE container (
containerID int PRIMARY KEY,
parentContainerId int,
description VARCHAR(50) NOT NULL
);
alter table container add constraint x FOREIGN KEY(parentContainerID)
REFERENCES container (containerID);
INSERT INTO container (containerID, description) VALUES
(1, 'vriezer');
INSERT INTO container (
containerID,
parentContainerID,
description
) VALUES (
2,
(SELECT containerID FROM container WHERE description = 'vriezer'),
'bovenste lade'
);
INSERT INTO container (
containerID,
parentContainerID,
description
) VALUES (
3,
(SELECT containerID FROM container WHERE description = 'vriezer'),
'middelste lade'
);
INSERT INTO container (
containerID,
parentContainerID,
description
) VALUES (
4,
(SELECT containerID FROM container WHERE description = 'vriezer'),
'onderste lade'
);
SELECT container.description AS container,
parent.description AS parentContainer
FROM container
LEFT JOIN container AS parent
ON 1=1
WHERE container.parentContainerID = parent.containerID;
SELECT container.description AS container,
parent.description AS parentContainer
FROM container
LEFT JOIN container AS parent
ON container.parentContainerID = parent.containerID
On Mon, Mar 5, 2012 at 1:42 AM, Cecil Westerhof <cldwester...@gmail.com> wrote:
> 2012/3/5 Cecil Westerhof <cldwester...@gmail.com>:
>> I am using the following SQL code:
>> CREATE TABLE container (
>> containerID UUID PRIMARY KEY,
>> parentContainerId UUID,
>> description VARCHAR(50) NOT NULL,
>>
>> FOREIGN KEY(parentContainerID) REFERENCES (containerID)
>> );
>> INSERT INTO container (containerID, description) VALUES
>> (random_UUID(), 'vriezer');
>> INSERT INTO container (
>> containerID,
>> parentContainerID,
>> description
>> ) VALUES (
>> random_UUID(),
>> SELECT containerID FROM container WHERE description = 'vriezer',
>> 'bovenste lade'
>> );
>> INSERT INTO container (
>> containerID,
>> parentContainerID,
>> description
>> ) VALUES (
>> random_UUID(),
>> SELECT containerID FROM container WHERE description = 'vriezer',
>> 'middelste lade'
>> );
>> INSERT INTO container (
>> containerID,
>> parentContainerID,
>> description
>> ) VALUES (
>> random_UUID(),
>> SELECT containerID FROM container WHERE description = 'vriezer',
>> 'onderste lade'
>> );
>> SELECT container.description AS container,
>> parent.description AS parentContainer
>> FROM container
>> LEFT JOIN container AS parent
>> WHERE container.parentContainerID = parent.containerID
>>
>> I would expect that all four containers would show up, with vriezer
>> 'having' parent NULL. But it does not. Only the four that do have a
>
> I mend three instead of four.
>
>
>> parent show up. Is this a bug, or am I doing something wrong?
>>
>> Also: is there a better way to insert the containers that have as
>> parent 'vriezer'?
>
> --
> Cecil Westerhof
>
> --
> You received this message because you are subscribed to the Google Groups "H2
> Database" group.
> To post to this group, send email to h2-database@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
