Bugzilla Version 2.16.7
Bugzilla Bug 1788
  OQL: allow to use bind variables in combination with "IN" operators
     Query page      Enter new bug
Bug#: 1788   Platform:   Reporter: martin-fuchs at gmx dot net (Martin Fuchs)
Product:   OS:   Add CC:
Component:   Version:   CC:
Remove selected CCs
Status: CLOSED   Priority:  
Resolution: FIXED   Severity:  
Assigned To: werner dot guttmann at gmx dot net (Werner Guttmann)   Target Milestone:  
URL:
Summary:

Attachment Type Modified Status Actions
proposed patch patch 2004-11-13 03:41 none Edit
CTF test cases to test basic OQL queries and the new functionality patch 2004-11-13 09:26 none Edit
Create a New Attachment (proposed patch, testcase, etc.) View All

Bug 1788 depends on: Show dependency tree
Show dependency graph
Bug 1788 blocks: 1792
Votes: 0    Show votes for this bug    Vote for this bug

Additional Comments:


Leave as CLOSED FIXED
Reopen bug

View Bug Activity   |   Format For Printing

Description: Opened: 2004-11-11 02:43

Currently it is only possible to use OQL WHERE statements with "IN" operators 
of the following form:

"... WHERE DNAME in list(1, 2, 3)"
"... WHERE DNAME in list(\"SALES\", \"TEST\", \"ABC\")"
"... WHERE DNAME in list(\"ABC\", nil)"

To make it easier executing dynamic queries, it would be good to also allow 
using bind variables, for example:

"... WHERE DNAME in list($1, $2)"

------- Additional Comment #1 From Martin Fuchs 2004-11-13 03:41 -------
Created an attachment (id=768)
proposed patch

------- Additional Comment #2 From Martin Fuchs 2004-11-13 09:26 -------
Created an attachment (id=771)
CTF test cases to test basic OQL queries and the new functionality

------- Additional Comment #3 From Werner Guttmann 2004-11-22 02:33 -------
That's really great work, Martin. Just running the JDO test suite, but I cannot
see any problems hitting us .. ;-).

In addition, can I please ask you to have a look at the documentation, amend it
where necessary and do the same for the 'unofficial' release notes at the
CastorWiki site.

------- Additional Comment #4 From Werner Guttmann 2004-11-22 02:37 -------
Tests run fine, but a small issue with the last test, where you are apparently
expecting an exception to be thrown:

boolean exc = false;
try {
   query = _db.getOQLQuery("select x from jdo.TestObject x where value1 in    
      list(\"XXX\", \"one 21\", 999)");
   tryQuery(query, 1);
} catch(Exception e) {
   exc = true;
}
assertTrue("Exception expected", exc);

I'd consider rewriting this to ...

try {
   query = _db.getOQLQuery("select x from jdo.TestObject x where value1 in    
      list(\"XXX\", \"one 21\", 999)");
   tryQuery(query, 1);
   fail ("blah ...");
} catch(Exception e) {
   assertEquals (e.getCLass().getName().equals ("org.exolab. ... ");
   assertEquals (e.getMessage().equals ("exception text ..."));
}

Just my 0.02 (European) cents, though.




------- Additional Comment #5 From Werner Guttmann 2004-11-22 02:39 -------
Actually, above test does NOT fail on mySQL 4.1.x ... :-(. Hmm ?

------- Additional Comment #6 From Martin Fuchs 2004-11-22 12:18 -------
> Tests run fine, but a small issue with the last test, where you are apparently
expecting an exception to be thrown:

...

> I'd consider rewriting this to ...
> 
> try {
>    query = _db.getOQLQuery("select x from jdo.TestObject x where value1 in    
>       list(\"XXX\", \"one 21\", 999)");
>    tryQuery(query, 1);
>    fail ("blah ...");
> } catch(Exception e) {
>    assertEquals (e.getCLass().getName().equals ("org.exolab. ... ");
>    assertEquals (e.getMessage().equals ("exception text ..."));
> }

Yes, good idea. But there are currently some problems with it.

The following query generates an "ORA-01722: Invalid Number" exception on Oracle:

select *
from   test_table
where  value1 in ('XXX', 'one 21', 999)

This has been the exception, I was expecting for this last test case.

MySQL instead doesn't generate a error message. In fact it returns the whole
content of TEST_TABLE! IMHO this is a bug, or at least very unexpected
behaviour. There are no datasets with an value1 of "XXX", "one 21" or "999" in
this table.

The best way to resolve this would be to check for common/convertible types in
the "IN" list directly in Castor. Then it would be possible to add your
"assertEquals(e.getCLass().getName().equals("org.exolab. ... ");".

But that's a thing for a new bug report. For the moment I would like to propose
committing this patch, but commenting out the last test case. Just to let the
test suite run without problems on any of the databases.

------- Additional Comment #7 From Werner Guttmann 2004-11-22 12:48 -------
> The best way to resolve this would be to check for common/convertible types in
> the "IN" list directly in Castor. Then it would be possible to add your
>"assertEquals(e.getCLass().getName().equals("org.exolab. ... ");".
Any way of moving this to a RDBMS-specific class, similar to all the
limit/clause handling business ?

------- Additional Comment #8 From Werner Guttmann 2004-11-22 12:50 -------
> But that's a thing for a new bug report. For the moment I would like 
> to propose committing this patch, but commenting out the last test 
> case. Just to let the test suite run without problems on any of the databases.
Fine by me. Let's create a new bug report, uncomment this one particular test
again .. and let's address this issue in this new bug report.

------- Additional Comment #9 From Werner Guttmann 2004-11-22 13:27 -------
Patch applied (minus the issue mentioned above).

------- Additional Comment #10 From Martin Fuchs 2004-11-28 06:33 -------
OK, closed.

     Query page      Enter new bug
This is Bugzilla: the Mozilla bug system. For more information about what Bugzilla is and what it can do, see bugzilla.org.
Actions: New | Query | bug # | Reports   New Account | Log In