Re: [Hsqldb-user] SELECT gives no results

[fredt <fr...@us...>]

Thanks Ito,

I've added a fix to CVS to return correct results from your test.

Fred
----- Original Message -----
From: "Ito Kazumitsu" <ka...@ma...>
To: <hsq...@li...>
Sent: 08 December 2003 22:48
Subject: Re: [Hsqldb-user] SELECT gives no results


Thank you, Fred.

>>>>> ":" == fredt  <fr...@us...> writes:

:> Thanks Ito,
:> I've fixed bug and committed to CVS.
:>
:> More tests, especially using a couple of views in addition to the
nesting,
:> would help make sure it is completely fixed.

How about this?
I am afraid nested select within create view does not work.

bash-2.05b$ cat test02.sql
DROP TABLE YYYYY IF EXISTS
DROP VIEW VIEW01 IF EXISTS
DROP VIEW VIEW02 IF EXISTS
CREATE TEXT TABLE YYYYY (YYYYYID VARCHAR, NAME VARCHAR)
SET TABLE YYYYY SOURCE "yyyyy.csv"
CREATE VIEW VIEW01 AS
  SELECT YYYYYID AS C1, NAME AS C2 FROM (
//    SELECT * FROM (
//      SELECT * FROM (
//        SELECT * FROM (
//          SELECT * FROM (
            SELECT * FROM YYYYY WHERE YYYYYID BETWEEN 'A' AND 'B'
//          ) AS A
//        ) AS B
//      ) AS C
//    ) AS D
  ) AS E
CREATE VIEW VIEW02 AS
  SELECT YYYYYID AS C1, NAME AS C2 FROM (
    SELECT * FROM (
      SELECT * FROM (
        SELECT * FROM (
          SELECT * FROM (
            SELECT * FROM YYYYY WHERE YYYYYID BETWEEN 'A' AND 'B'
          ) AS A
        ) AS B
      ) AS C
    ) AS D
  ) AS E
go
SELECT * FROM VIEW01
go
SELECT * FROM VIEW02
go
SELECT E.C1, E.C2, E.C3, E.C4, VIEW01.C1, VIEW01.C2 FROM
  VIEW01,
  (SELECT * FROM
     (SELECT * FROM
        (SELECT * FROM
           (SELECT * FROM
              (SELECT VIEW01.C1 AS C1, VIEW01.C2 AS C2,
                      VIEW02.C1 AS C3, VIEW02.C2 AS C4
                 FROM VIEW01, VIEW02
                 WHERE VIEW01.C1 = VIEW02.C1
               ) AS A
           ) AS B
        ) AS C
     ) AS D
  ) AS E
go
bash$ java org.hsqldb.util.ScriptTool -url jdbc:hsqldb: -database
test -script test02.sql | grep -v '^Cache'
writeScript
update count 0
C1 C2
A A
B B

C1 C2

C1 C2 C3 C4 C1 C2

bash-2.05b$


-------------------------------------------------------
This SF.net email is sponsored by: IBM Linux Tutorials.
Become an expert in LINUX or just sharpen your skills.  Sign up for IBM's
Free Linux Tutorials.  Learn everything from the bash shell to sys admin.
Click now! http://ads.osdn.com/?ad_id=1278&alloc_id=3371&op=click
_______________________________________________
Hsqldb-user mailing list
Hsq...@li...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user