Issue 114189 - Base crashes when using filters / sorts on some query results with calculated boolean fields
Summary: Base crashes when using filters / sorts on some query results with calculated...
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 3.2.1
Hardware: All All
: P3 Trivial (vote)
Target Milestone: OOo 3.3
Assignee: ocke.janssen
QA Contact: issues@dba
Depends on:
Blocks: 111112
  Show dependency tree
Reported: 2010-08-30 00:31 UTC by ameliab
Modified: 2010-10-05 08:23 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

New Test Database (4.08 KB, application/vnd.sun.xml.base)
2010-08-30 19:33 UTC, ameliab
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description ameliab 2010-08-30 00:31:38 UTC
When using Base with an SQL query in which one of the column definitions uses a
correlated subquery, trying to filter the query results in the display causes
OpenOffice to crash.

Furthermore, if the query was created using the "run SQL command directly"
option, then tryint to sort the results will either cause OpenOffice to crash or
to go into an indefinite loop (and may eventually crash, although I gave up
waiting and used a force quit).  

However, if the query was run through the OpenOffice SQL parser, sorting does
not appear to cause any crashes (although see issue 114187 and issue 114188 for
other problems with sorting queries that I discovered while trying to isolate
what was causing the crash).  Filtering still causes a crash.  However, this
crash is more contained -- I get OpenOffice's "sorry but the program crashed
your file will be recovered" message, instead of Mac's "OpenOffice crashed do
you want to relaunch" message.

The test database and an explanation of it are attached to issue 114187.
Comment 1 drewjensen.inbox 2010-08-30 05:08:24 UTC
Using OOO330m5 / Ubuntu 10.04 I can not recreate the issue.

@ameliab I am going to go ahead and close this as works for me - because this is
about a crash, while the other issues mentioned have been opened. Of course if
you are still having problems do change it back.
Comment 2 ameliab 2010-08-30 19:31:30 UTC
The file I'd posted is definitely still crashing and/or hanging on Mac OSX for
me.  I re-downloaded to make sure the version saved on my computer wasn't
corrupted, and got the exact same behaviour as yesterday.  

So... maybe an operating system-unique problem?  Any other Mac users want to
give it a try?

*However*, I appear to have been wrong about what caused the crash.  I tried
reproducing it in another test database, and very similar queries work fine --
but then others crashed. 

After experimentation, I think I've narrowed it down to the boolean field that
is causing crashes, rather than the correlated subqueries.  Which is strange
because I used two completely different ways of generating the boolean field
(one using a boolean expression, the other casting from a number).

The casting method (e.g., CAST(CAST("Random"*2 AS Integer) AS Boolean) ), run
through OO's SQL parser, results in crashes when I apply a filter to the boolean
field (sorts are okay), but *only* if I also have the primary key in the query.
 (Note:  these are "neat" crashes, where OO recovers itself without having to be
relaunched from the operating system)

The same query sent directly to the embedded HSQL DB, does not cause any
crashing when I try to sort/filter the results, although sorts and filters have
no effect (as noted in issue 114187).

The alternate method, using a boolean expression as a column definition (e.g.
SELECT "Random" >= 0.5 as "MoreThanHalf"), is not acceptable to OpenOffice's SQL
parser.  When the query is sent directly to the HSQL DB, any attempt to apply a
filter or sort on the results either causes a complete crash of OpenOffice or
(in one case that I then couldn't reproduce) an indefinite hang.

I've changed the title of this issue to correspond to this new understanding,
and I'll attach my new file.  

In the file, I've again got one table with just a list of random numbers and an
auto-generated primary key for each.  All the queries are based directly on this
table.  Each query name clearly indicates: 
(1) whether it is sent directly to the embedded database ("DirectQuery") or
through the OpenOffice parser ("Query");
(2) What values it selects:
  PK -- the primary key from the original table
  Num -- a number value from the original table
  Boolean -- a boolean value created by performing arithmetic and cast()
function on the number
  BooleanExp -- a boolean value defined as a boolean expression

  (I've gotten rid of the subquery-based fields, since they didn't affect the
outcome and were just complicating matters.)

(3) the behaviour (e.g., complete or "neat" crash, or hang) that happens on my Mac.

Anyway, this has now become a very obscure problem, but at least I know which
particular conditions to avoid in my original database!
Comment 3 ameliab 2010-08-30 19:33:12 UTC
Created attachment 71486 [details]
New Test Database
Comment 4 ocke.janssen 2010-09-01 07:56:45 UTC
Worked in a.2
Comment 5 ocke.janssen 2010-09-01 07:59:12 UTC
Comment 6 ocke.janssen 2010-09-01 10:25:09 UTC
Fixed in cws dba33i
Comment 7 ocke.janssen 2010-10-05 08:23:26 UTC