Wednesday, December 26, 2007

Back to the Blog ... back to the bug (report)

Today I decide to put up a post about a common requirement which leads to an interesting SQL query (see an example here) using Firebird and Flamerobin, after a few seconds I was writing bug reports :-( see:

[ 1858394 ] Error selecting from mon$statements

and

[ 1858397 ] Error running SELECT query. (Not a Flamerobin bug)

Hope these will be fixed soon.

EDIT: the developers instantly responded to my bug reports saying that the first one is already fixed in SVN and that will be in the next release and the second is not a bug

Well actually bug 1858397 looks like a Firebird bug, unrelated to Flamerobin or IBPP :-( time to close that bug report and post to the right place, which is

[#CORE-1668] Error running SELECT query.

EDIT: looks like this is a duplicate of bug 94, a long standing one that won't be fixed anytime soon :-(

After the ordeal I ended up with

[ 1858592 ] Problem with LIST() function.
List() result is represented as a BLOB so it appears in the grid as a marker and you can't read it's content.
Even in this case the developers quickly clarified the situation:

There are plans to make textual blobs behave just like regular varchar
columns - if not sooner it will be available in version 0.9.0.

Milan.


BTW this query doesn't work:

  1. SELECT
  2. e.emp_no, e.FIRST_NAME, e.LAST_NAME,
  3. CASE
  4. WHEN ep.emp_no || ep.PROJ_ID IS NULL THEN ' didn''t take part in '
  5. ELSE ' took part in '
  6. END,
  7. p.proj_name
  8. FROM
  9. employee e,
  10. project p
  11. LEFT OUTER JOIN EMPLOYEE_PROJECT ep
  12. ON e.EMP_NO = ep.EMP_NO AND p.proj_id = ep.PROJ_ID

(If you change the above to a cross join it works fine).
While this does:

  1. SELECT
  2. a.emp_no, a.FIRST_NAME, a.LAST_NAME,
  3. CASE
  4. WHEN ep.emp_no || ep.PROJ_ID IS NULL THEN ' didn''t take part in '
  5. ELSE ' took part in '
  6. END,
  7. a.proj_name
  8. FROM
  9. (SELECT * FROM employee e, project p) a
  10. LEFT OUTER JOIN
  11. EMPLOYEE_PROJECT ep
  12. ON a.EMP_NO = ep.EMP_NO AND a.proj_id = ep.PROJ_ID;