Loading ...
Sorry, an error occurred while loading the content.

Re: [firebird-support] Strange behavior in stored procedure

Expand Messages
  • Martijn Tonies
    ... same ... output ... would ... Why? Can you explain that? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL
    Message 1 of 11 , Nov 1, 2006
    • 0 Attachment
      > >I have a pretty simple query that works just fine when executed via
      > >IBExpert's SQL Editor, but when I put it into a stored procedure it fails
      > >and returns no results.
      > >
      > >This is in Firebird 1.5 Super Server on Linux.
      > >
      > >The stored procedure is:
      > >
      > >CREATE PROCEDURE SOUND_STORAGE_DETAILS_PRC (
      > > a_sound_no integer)
      > >returns (
      > > r_sound_name varchar(100) character set win1251,
      > > r_sound_desc varchar(5000) character set win1251,
      > > r_sound_server varchar(100) character set win1251,
      > > r_sound_volume varchar(100) character set win1251,
      > > r_sound_filename varchar(100) character set win1251,
      > > r_sound_duration time,
      > > r_sound_redirect varchar(250) character set win1251)
      > >as
      > >begin
      > > for
      > > select first 1 skip 0
      > > SOUND.SOUND_SHORT_NAME,
      > > SOUND.SOUND_DESC,
      > > SOUND_STORAGE.SS_SERVER,
      > > SOUND_STORAGE.SS_VOLUME,
      > > SOUND_STORAGE.SS_FILENAME,
      > > SOUND_STORAGE.SS_REDIRECT,
      > > SOUND.ITUNES_DURATION
      > >FROM
      > > SOUND
      > > INNER JOIN SOUND_STORAGE ON (SOUND.SOUND_NO = SOUND_STORAGE.SOUND_NO)
      > >WHERE
      > > (SOUND.SOUND_NO = :A_SOUND_NO)
      > > into :R_SOUND_NAME, :R_SOUND_DESC, :R_SOUND_SERVER, :R_SOUND_VOLUME,
      > >:R_SOUND_FILENAME, :r_sound_redirect, :R_SOUND_DURATION
      > > do
      > > begin
      > > suspend;
      > > end
      > >End
      > >
      > >---
      > >What's really strange is that it works fine 99.9% of the time. But with
      > >certain data in the tables, it doesn't find it. But if I run the exact
      same
      > >query manually it works just fine.
      >
      > It's not the exact same query if you run it as a DSQL query. DSQL
      > isn't in a FOR loop.
      >
      > >Does anyone have any idea as to why this could happen? All input and
      output
      > >arguments match exactly to the table specs and even if they didn't I
      would
      > >expect this to fail on all cases rather than just on some cases.
      > The problem here is that there is only one row output from SELECT
      > FIRST so your FOR loop is going to return zilch whenever the output
      > has multiple rows.

      Why? Can you explain that?

      Martijn Tonies
      Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
      MS SQL Server
      Upscene Productions
      http://www.upscene.com
      My thoughts:
      http://blog.upscene.com/martijn/
      Database development questions? Check the forum!
      http://www.databasedevelopmentforum.com
    • Helen Borrie
      ... No, not really. But there s an intrinsic problem with the logic of putting that SELECT FIRST 1 inside a FOR loop. Theoretically, if the set from which
      Message 2 of 11 , Nov 1, 2006
      • 0 Attachment
        At 07:25 PM 1/11/2006, Martijn wrote:
        > It's not the exact same query if you run it as a DSQL query. DSQL
        > > isn't in a FOR loop.
        > >
        > > >Does anyone have any idea as to why this could happen? All input and
        >output
        > > >arguments match exactly to the table specs and even if they didn't I
        >would
        > > >expect this to fail on all cases rather than just on some cases.
        > > The problem here is that there is only one row output from SELECT
        > > FIRST so your FOR loop is going to return zilch whenever the output
        > > has multiple rows.
        >
        >Why? Can you explain that?

        No, not really. But there's an intrinsic problem with the logic of
        putting that SELECT FIRST 1 inside a FOR loop. Theoretically, if the
        set from which the FIRST 1 is being taken is not a singleton, there
        will be leftovers that the logic can't deal with. So, according to
        my intuition, if it's giving "weird" results in some cases, it must
        be to do with what is being output to the intermediate set.

        Really the point to make here is that SELECT FIRST is misused inside
        a loop. The same query returning the same result ad infinitum, with
        no endpoint.

        Helen
      • Martijn Tonies
        ... Could be me, but that sounds like a bug or you re wrong. SELECT [FIRST] returns a resultset, which is handled by the FOR loop. Why this should return the
        Message 3 of 11 , Nov 1, 2006
        • 0 Attachment
          > > It's not the exact same query if you run it as a DSQL query. DSQL
          > > > isn't in a FOR loop.
          > > >
          > > > >Does anyone have any idea as to why this could happen? All input and
          > >output
          > > > >arguments match exactly to the table specs and even if they didn't I
          > >would
          > > > >expect this to fail on all cases rather than just on some cases.
          > > > The problem here is that there is only one row output from SELECT
          > > > FIRST so your FOR loop is going to return zilch whenever the output
          > > > has multiple rows.
          > >
          > >Why? Can you explain that?
          >
          > No, not really. But there's an intrinsic problem with the logic of
          > putting that SELECT FIRST 1 inside a FOR loop. Theoretically, if the
          > set from which the FIRST 1 is being taken is not a singleton, there
          > will be leftovers that the logic can't deal with. So, according to
          > my intuition, if it's giving "weird" results in some cases, it must
          > be to do with what is being output to the intermediate set.
          >
          > Really the point to make here is that SELECT FIRST is misused inside
          > a loop. The same query returning the same result ad infinitum, with
          > no endpoint.

          Could be me, but that sounds like a bug or you're wrong.

          SELECT [FIRST] returns a resultset, which is handled by the FOR loop.

          Why this should "return the same result ad infinitum" is unclear to me. If
          this would be the case, how would this behave differently from a normal
          SELECT that returns a resultset? There IS an endpoint for a FIRST 1,
          namely after a single iteration of the FOR loop.

          It's not like the SELECT is executed for each FOR or anything.

          Sorry, I still fail to see why this would be wrong (despite the lack of an
          ORDER BY clause in the OPs post ;) ...

          Martijn Tonies
          Database Workbench - development tool for Firebird and more!
          Upscene Productions
          http://www.upscene.com
          My thoughts:
          http://blog.upscene.com/martijn/
          Database development questions? Check the forum!
          http://www.databasedevelopmentforum.com
        • Alan McDonald
          ... the select first statement is not IN the FOR loop - it DEFINES the LOOP. The LOOP ceases at the extent of the select, i.e. at the first record selected. So
          Message 4 of 11 , Nov 1, 2006
          • 0 Attachment
            > Could be me, but that sounds like a bug or you're wrong.
            >
            > SELECT [FIRST] returns a resultset, which is handled by the FOR loop.
            >
            > Why this should "return the same result ad infinitum" is unclear to me. If
            > this would be the case, how would this behave differently from a normal
            > SELECT that returns a resultset? There IS an endpoint for a FIRST 1,
            > namely after a single iteration of the FOR loop.
            >
            > It's not like the SELECT is executed for each FOR or anything.
            >
            > Sorry, I still fail to see why this would be wrong (despite the lack of an
            > ORDER BY clause in the OPs post ;) ...
            >
            > Martijn Tonies

            the select first statement is not IN the FOR loop - it DEFINES the LOOP. The
            LOOP ceases at the extent of the select, i.e. at the first record selected.
            So I agree with you Martijn.
            Alan
          • vladman992000
            ... Thanks for your reply, Alan. I suspect you are correct, however I found the problem in the end. It was not specifically in this stored procedure after
            Message 5 of 11 , Nov 1, 2006
            • 0 Attachment
              --- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:

              > I wonder why SKIP 0?
              > have you tried leaving this out? just use FIRST 1
              > and using the FIRST clause without an order by.. I never do it. Are you
              > looking for unreliable (unpredicable) results?

              Thanks for your reply, Alan. I suspect you are correct, however I
              found the problem in the end. It was not specifically in this stored
              procedure after all but in the calling procedure that attempted to
              create a log entry of what was going on in an audit trail. Thanks to
              IBExpert, I was able to trace through the stored procedure and find
              the error and fix it.

              All is well now. Thanks to you and everyone else for their assistance.

              Myles
            • vladman992000
              ... Thanks Helen. I ve modified the SP accordingly. Myles
              Message 6 of 11 , Nov 1, 2006
              • 0 Attachment
                --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

                > The problem here is that there is only one row output from SELECT
                > FIRST so your FOR loop is going to return zilch whenever the output
                > has multiple rows. Don't use SELECT FIRST in PSQL! Even when it
                > returns the "expected" result, it's unnecessarily noisy. PSQL can
                > get your one row (or any n rows) much more efficiently:

                Thanks Helen. I've modified the SP accordingly.

                Myles
              • Nick Upson
                could you go into more detail over this, one of my current projects has a lot of SP code written with select first ... , what magnitude of difference will it
                Message 7 of 11 , Nov 1, 2006
                • 0 Attachment
                  could you go into more detail over this, one of my current projects
                  has a lot of SP code written with "select first ... ", what magnitude
                  of difference will it make?

                  On 31/10/06, Helen Borrie <helebor@...> wrote:
                  > Don't use SELECT FIRST in PSQL! Even when it
                  > returns the "expected" result, it's unnecessarily noisy. PSQL can
                  > get your one row (or any n rows) much more efficiently:
                Your message has been successfully submitted and would be delivered to recipients shortly.