These is some problems with SQL subselect

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

These is some problems with SQL subselect.it produce the different results as 
other databases(MySql) when excute this SQL:

SELECT NODE.ID,NODE.NAME,NODE.URL,NODE.DESCRIPTION,
NODE.ID IN(SELECT DISTINCT MENUID FROM SYSTEM_ROLE_MENU WHERE 
SYSTEM_ROLE_MENU.ROLEID = 2) AS VISIBLE,
(COUNT(PARENT.ID) - 1) AS LEVEL,NODE.LFT,NODE.RGT,NODE.RGT=NODE.LFT+1 AS ISLEAF 
FROM SYSTEM_MENU AS NODE,SYSTEM_MENU AS PARENT 
WHERE NODE.LFT BETWEEN PARENT.LFT AND PARENT.RGT GROUP BY NODE.ID ORDER BY 
NODE.LFT;

--------------------------------------
initial database SQL:

CREATE TABLE SYSTEM_ROLE(ID  SERIAL
,NAME VARCHAR(20)
,DESCRIPTION VARCHAR(500) 
,CONSTRAINT SYSTEM_ROLE_pkey PRIMARY KEY (ID));

CREATE TABLE SYSTEM_MENU(ID  SERIAL
,NAME VARCHAR(20)
,URL VARCHAR(500)
,DESCRIPTION VARCHAR(500)
,LFT INT
,RGT INT 
,CONSTRAINT SYSTEM_MENU_pkey PRIMARY KEY (ID));

CREATE TABLE SYSTEM_ROLE_MENU(ID  SERIAL
,ROLEID INT
,MENUID INT 
,CONSTRAINT SYSTEM_ROLE_MENU_pkey PRIMARY KEY (ID));

INSERT INTO SYSTEM_ROLE(ID ,NAME ,DESCRIPTION) VALUES
(1,'系统管理员','拥有系统设置的权限'),
(2,'普通用户','拥有全部业务功能的权限'),
(3,'经理','拥有酒店设置的权限'),
(4,'前台','拥有基本业务功能的权限');

INSERT INTO SYSTEM_MENU(ID,NAME,URL,DESCRIPTION,LFT,RGT) VALUES
(1,'系统管理',NULL,NULL,1,20),
(2,'用户管理','system/user_manage.html',NULL,2,3),
(3,'角色管理','system/role_manage.html',NULL,4,5),
(4,'用户角色分配','system/role_assign.html',NULL,6,7),
(5,'菜单管理','system/menu_manage.html',NULL,8,9),
(6,'角色可见菜单分配','system/menu_assign.html',NULL,10,11),
(7,'枚举管理','system/sysenum_mange.html',NULL,12,13),
(8,'演示页面',NULL,NULL,21,40),
(9,'格式化文本框','demo1.html',NULL,22,23),
(10,'酒店设置',NULL,NULL,51,70),
(11,'房间类型设置','hotel/room_type_manage.html',NULL,52,53),
(12,'房间设置','hotel/room_manage.html',NULL,54,55),
(13,'经营管理',NULL,NULL,71,100),
(14,'可用房确认','hotel/available_room_check.html',NULL,72,73),
(15,'入住情况','hotel/room_occupancy_manage.html',NULL,74,75),
(16,'入住登记','hotel/check_in.html',NULL,76,77),
(17,'账单管理','hotel/bill_manage.html',NULL,78,79);

INSERT INTO SYSTEM_ROLE_MENU(ID ,ROLEID ,MENUID) VALUES
(1,1,1),
(2,1,2),
(3,1,3),
(4,1,4),
(5,1,5),
(6,1,6),
(7,1,7),
(8,1,8),
(9,1,9),
(10,1,10),
(11,1,11),
(12,1,12),
(13,1,13),
(14,1,14),
(15,1,15),
(16,1,16),
(17,1,17),
(18,2,10),
(19,2,11),
(20,2,12),
(21,2,13),
(22,2,14),
(23,2,15),
(24,2,16),
(25,2,17),
(26,3,10),
(27,3,11),
(28,3,12),
(29,3,13),
(30,3,14),
(31,3,15),
(32,3,16),
(33,3,17),
(34,4,13),
(35,4,14),
(36,4,15),
(37,4,16),
(38,4,17);
----------------------------------------------------------------

What is the expected output? What do you see instead?

1 系统管理 null null FALSE 0 1 20 FALSE 
2 用户管理 system/user_manage.html null FALSE 1 2 3 TRUE 
3 角色管理 system/role_manage.html null FALSE 1 4 5 TRUE 
4 用户角色分配 system/role_assign.html null FALSE 1 6 7 TRUE 
5 菜单管理 system/menu_manage.html null FALSE 1 8 9 TRUE 
6 角色可见菜单分配 system/menu_assign.html null FALSE 1 10 11 TRUE 
7 枚举管理 system/sysenum_mange.html null FALSE 1 12 13 TRUE 
8 演示页面 null null FALSE 0 21 40 FALSE 
9 格式化文本框 demo1.html null TRUE 1 22 23 TRUE 
10 酒店设置 null null TRUE 0 51 70 FALSE 
11 房间类型设置 hotel/room_type_manage.html null TRUE 1 52 53 TRUE 
12 房间设置 hotel/room_manage.html null TRUE 1 54 55 TRUE 
13 经营管理 null null TRUE 0 71 100 FALSE 
14 可用房确认 hotel/available_room_check.html null TRUE 1 72 73 TRUE 
15 入住情况 hotel/room_occupancy_manage.html null TRUE 1 74 75 TRUE 
16 入住登记 hotel/check_in.html null TRUE 1 76 77 TRUE 
17 账单管理 hotel/bill_manage.html null TRUE 1 78 79 TRUE 

I think It should be like this:

ID   NAME   URL   DESCRIPTION   VISIBLE   LEVEL   LFT   RGT   ISLEAF   
1 系统管理 null null FALSE 0 1 20 FALSE 
2 用户管理 system/user_manage.html null FALSE 1 2 3 TRUE 
3 角色管理 system/role_manage.html null FALSE 1 4 5 TRUE 
4 用户角色分配 system/role_assign.html null FALSE 1 6 7 TRUE 
5 菜单管理 system/menu_manage.html null FALSE 1 8 9 TRUE 
6 角色可见菜单分配 system/menu_assign.html null FALSE 1 10 11 TRUE 
7 枚举管理 system/sysenum_mange.html null FALSE 1 12 13 TRUE 
8 演示页面 null null FALSE 0 21 40 FALSE 
9 格式化文本框 demo1.html null FALSE 1 22 23 TRUE 
10 酒店设置 null null TRUE 0 51 70 FALSE 
11 房间类型设置 hotel/room_type_manage.html null TRUE 1 52 53 TRUE 
12 房间设置 hotel/room_manage.html null TRUE 1 54 55 TRUE 
13 经营管理 null null TRUE 0 71 100 FALSE 
14 可用房确认 hotel/available_room_check.html null TRUE 1 72 73 TRUE 
15 入住情况 hotel/room_occupancy_manage.html null TRUE 1 74 75 TRUE 
16 入住登记 hotel/check_in.html null TRUE 1 76 77 TRUE 
17 账单管理 hotel/bill_manage.html null TRUE 1 78 79 TRUE 

----------------------------------------------------------------
What version of the product are you using? On what operating system, file
system, and virtual machine?

h2-1.2.147.jar
WinXP 32X
NTFS
JDK6

----------------------------------------------------------------
In your view, is this a defect or a feature request?
a defect!

Original issue reported on code.google.com by conanca2...@gmail.com on 17 Dec 2010 at 2:38

[GoogleCodeExporter]

PostgreSQL throws the exception:
ERROR: column "node.name" must appear in the GROUP BY clause or be used in an 
aggregate function

Original comment by thomas.t...@gmail.com on 21 Dec 2010 at 7:23

[GoogleCodeExporter]

I can reproduce the problem, and I have a simpler test case now:

drop table test;
create table test(id int);
insert into test values(1), (2), (4);
select a.id, a.id in(select 4) x 
from test a, test b where a.id in (b.id, b.id - 1);
select a.id, a.id in(select 4) x 
from test a, test b where a.id in (b.id, b.id - 1) 
group by a.id;

I don't know what the problem is so far, but I hope I can solve it in the next 
release.

Thanks a lot for your help!

Original comment by thomas.t...@gmail.com on 21 Dec 2010 at 8:25

• Changed state: Accepted

[GoogleCodeExporter]

Another test case (more like your test case):

drop table role;
drop table menu;

create table menu(id int primary key, lft int, rgt int);
insert into menu values(8, 21, 40), (9, 22, 23), (10, 51, 70);

select a.id, b.id, a.id in(select 10) as visible
from menu as a, menu as b
where a.lft between b.lft and b.rgt;

select a.id, a.id = 10 as visible
from menu as a, menu as b
where a.lft between b.lft and b.rgt group by a.id order by a.id;

Original comment by thomas.t...@gmail.com on 21 Dec 2010 at 8:28

[GoogleCodeExporter]

Fixed in version 1.3.149

Original comment by thomas.t...@gmail.com on 10 Jan 2011 at 7:22

• Changed state: Fixed