Complex Nested SELECT statement broken since 1.0.75

[GoogleCodeExporter]

This bug involves a nested SELECT with the ALL comparison operator returning an 
unexpected number of results.

Version 1.0.75 works fine, Version 1.0.101-1.3.159 return the wrong results

What steps will reproduce the problem?
1.  Create the authors table from the book SQL: Visual QuickStart Guide, Third 
Edition (http://www.fehily.com/downloads/sql_vqs3_files.zip):

DROP TABLE authors;
CREATE TABLE authors   (
  au_id    CHAR(3)     NOT NULL,
  au_fname VARCHAR(15) NOT NULL,
  au_lname VARCHAR(15) NOT NULL,
  phone    VARCHAR(12)         ,
  address  VARCHAR(20)         ,
  city     VARCHAR(15)         ,
  state    CHAR(2)             ,
  zip      CHAR(5)             ,
  CONSTRAINT pk_authors PRIMARY KEY (au_id)
  );
INSERT INTO authors VALUES('A01','Sarah','Buchman','718-496-7223','75 West 205 
St','Bronx','NY','10468');
INSERT INTO authors VALUES('A02','Wendy','Heydemark','303-986-7020','2922 
Baseline Rd','Boulder','CO','80303');
INSERT INTO authors VALUES('A03','Hallie','Hull','415-549-4278','3800 Waldo 
Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A04','Klee','Hull','415-549-4278','3800 Waldo Ave, 
#14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A05','Christian','Kells','212-771-4680','114 
Horatio St','New York','NY','10014');
INSERT INTO authors VALUES('A06','','Kellsey','650-836-7128','390 Serra 
Mall','Palo Alto','CA','94305');
INSERT INTO authors VALUES('A07','Paddy','O''Furniture','941-925-0752','1442 
Main St','Sarasota','FL','34236');

2.  Perform a SELECT query using the >ALL comparison:
SELECT au_fname, au_lname, city, state, zip FROM AUTHORS WHERE zip > ALL 
(select zip FROM authors WHERE zip < 50000) ;

This returns 4 results.

3.   Perform a query like: SELECT * FROM ( above query )
Example:
SELECT COUNT(*) FROM (SELECT au_fname, au_lname, city, state, zip FROM AUTHORS 
WHERE zip > ALL (select zip FROM authors WHERE zip < 50000) );

The result is 3 (it should be 4)

What is the expected output? What do you see instead?
The expected output is a count of 4.  See attached screenshot.

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

Version 1.0.101 to 1.3.159 exhibit the problem in Windows XP.
The problem was also found in v1.3.159 on MacOS (never tested other versions).

Do you know a workaround?
Performing an equivalent SELECT max(...) query seems to work:
Example:
SELECT au_fname, au_lname, city, state, zip FROM AUTHORS WHERE zip > (select 
MAX(zip) FROM authors WHERE zip < 50000);

What is your use case, meaning why do you need this feature?
Bug not a feature

How important/urgent is the problem for you?
Not sure

Please provide any additional information below.

Original issue reported on code.google.com by robert.b...@gmail.com on 1 Sep 2011 at 5:55

[GoogleCodeExporter]

Hi,

I tested this with MySQL, PostgreSQL, HSQLDB, and Apache Derby.

MySQL and HSQLDB return 4 rows / count(*) = 4.
PostgreSQL and Derby throw exceptions:
Comparisons between 'CHAR (UCS_BASIC)' and 'INTEGER' are not supported. Types 
must be comparable.
ERROR: operator does not exist: character < integer 42883/0

But it's still a bug, either H2 should say count(*)=4.

Simpler test case:

drop table test;
create table test(name varchar(255));
insert into test values('a');
insert into test values('b');
insert into test values('c');
select name from test where name > all(select name from test where name<'b');
select count(*) from (select name from test where name > all(select name from 
test where name<'b')) x;

Original comment by thomas.t...@gmail.com on 8 Sep 2011 at 7:29

• Changed state: Accepted

[GoogleCodeExporter]

Now fixed in the trunk.

Original comment by thomas.t...@gmail.com on 10 Sep 2011 at 9:37

[GoogleCodeExporter]

Thanks Thomas.

Original comment by robert.b...@gmail.com on 12 Sep 2011 at 2:03

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 22 Sep 2011 at 4:32

• Changed state: Fixed