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

Re: [firebird-support] Re: Firebird 2.5 (64 bit), strange SQL result

Expand Messages
  • Thomas Steinmaurer
    ... What s the execution plan? If it s using an index on ID_DIV_LC and the following returns a result set: select * from m_int_frm a where a.id_trs_typ = 100
    Message 1 of 14 , May 7, 2012
    View Source
    • 0 Attachment
      > --- In firebird-support@yahoogroups.com, Alexandre Benson Smith<iblist@...> wrote:
      >>
      >> Em 7/5/2012 21:51, trskopo escreveu:
      >>> Hi all,
      >>>
      >>> I run sql statement via flamerobin, the statment is like this :
      >>>
      >>> SELECT * FROM M_INT_FRM a
      >>> where a.ID_TRS_TYP = 100
      >>>
      >>> It will returns :
      >>>
      >>> ID ID_DIV_LC SCT_INI ID_TRS_TYP FRM_INI
      >>> ========================================
      >>> 4 8 MP 100 PO
      >>> 5 9 MN 100 PO
      >>> 6 0 MS 100 PO
      >>>
      >>>
      >>> but if I change sql statement to this
      >>>
      >>> SELECT * FROM M_INT_FRM a
      >>> where a.ID_TRS_TYP = 100
      >>> and a.id_div_lc = 0
      >>>
      >>> no sql result returns.
      >>>
      >>> You can download the database at http://www.mediafire.com/?bx8k29kt7x2ucks
      >>>
      >>> Strange, could it be a bug?
      >>>
      >>> Thanks and regards,
      >>> Sugiarto
      >>>
      >>
      >> What the datatype of ID_DIV_LC column ?
      >>
      >> what about:
      >>
      >> SELECT * FROM M_INT_FRM a
      >> where a.ID_TRS_TYP = 100
      >> and a.id_div_lc between -0.1 and 0.1
      >>
      >> see you !
      >>
      >
      > Still no result.
      >
      > Here is the table structure
      > CREATE TABLE M_INT_FRM
      > (
      > ID Integer NOT NULL,
      > ID_DIV_LC Integer NOT NULL,
      > SCT_INI Varchar(2) NOT NULL,
      > ID_TRS_TYP Integer NOT NULL,
      > FRM_INI Varchar(2) NOT NULL,
      > PRIMARY KEY (ID)
      > );

      What's the execution plan? If it's using an index on ID_DIV_LC and the
      following returns a result set:

      select
      *
      from
      m_int_frm a
      where
      a.id_trs_typ = 100
      and a.id_div_lc + 0 = 0


      Then your index on ID_DIV_LC might be problematic/corrupted.


      --
      With regards,
      Thomas Steinmaurer (^TS^)
      Firebird Technology Evangelist

      http://www.upscene.com/

      Do you care about the future of Firebird? Join the Firebird Foundation:
      http://www.firebirdsql.org/en/firebird-foundation/
    • Jesus Garcia
      2012/5/8 trskopo ... I have tested your database and i think is an error of flamerobin, what you see 0 i see null with ibexpert. try SELECT
      Message 2 of 14 , May 8, 2012
      View Source
      • 0 Attachment
        2012/5/8 trskopo <trskopo@...>

        > **
        >
        >
        > Hi all,
        >
        > I run sql statement via flamerobin, the statment is like this :
        >
        > SELECT * FROM M_INT_FRM a
        > where a.ID_TRS_TYP = 100
        >
        > It will returns :
        >
        > ID ID_DIV_LC SCT_INI ID_TRS_TYP FRM_INI
        > ========================================
        > 4 8 MP 100 PO
        > 5 9 MN 100 PO
        > 6 0 MS 100 PO
        >
        > but if I change sql statement to this
        >
        > SELECT * FROM M_INT_FRM a
        > where a.ID_TRS_TYP = 100
        > and a.id_div_lc = 0
        >
        > no sql result returns
        >
        >
        >


        I have tested your database and i think is an error of flamerobin, what you
        see 0 i see null with ibexpert. try

        SELECT * FROM M_INT_FRM a
        where a.ID_TRS_TYP = 100
        and a.id_div_lc is null



        May be in sometime you have changed the column from null to not null.



        Regards, Jesus


        [Non-text portions of this message have been removed]
      • Jesus Garcia
        I also have seen that in ERRLOG table you have the entry DT USR MSG ERR_AT 29/04/2012 x!JKmn validation error for column ID_DIV_LC, value *** null ***
        Message 3 of 14 , May 8, 2012
        View Source
        • 0 Attachment
          I also have seen that in ERRLOG table you have the entry

          DT USR MSG ERR_AT
          29/04/2012 x!JKmn validation error for column ID_DIV_LC, value "*** null
          ***" 29/04/2012 17:00:02

          Regards, Jesus


          [Non-text portions of this message have been removed]
        • trskopo
          Thanks for your replay. If you see null with ibexpert, how come this data can be store in Firebird, since ddl for this column not allow for null value? Could
          Message 4 of 14 , May 8, 2012
          View Source
          • 0 Attachment
            Thanks for your replay.

            If you see null with ibexpert, how come this data can be store in Firebird, since ddl for this column not allow for null value? Could it be a bug in Firebird?

            Another strange results when issued this statement

            SELECT * FROM M_INT_FRM a
            where a.id_div_lc is null

            It will returns 3 result (via flamerobin).

            Thanks and regards,
            Sugiarto

            > I have tested your database and i think is an error of flamerobin, what you
            > see 0 i see null with ibexpert. try
            >
            > SELECT * FROM M_INT_FRM a
            > where a.ID_TRS_TYP = 100
            > and a.id_div_lc is null
            >
            >
            >
            > May be in sometime you have changed the column from null to not null.
            >
            >
            >
            > Regards, Jesus
            >
            >
            > [Non-text portions of this message have been removed]
            >
          • Helen Borrie
            ... It was stored at some point before the column was changed to NOT NULL. When you do that change, the engine does NOT alter existing data. Thus, you must
            Message 5 of 14 , May 8, 2012
            View Source
            • 0 Attachment
              At 02:26 PM 9/05/2012, you wrote:
              >Thanks for your replay.
              >
              >If you see null with ibexpert, how come this data can be store in Firebird, since ddl for this column not allow for null value? Could it be a bug in Firebird?

              It was stored at some point before the column was changed to NOT NULL. When you do that change, the engine does NOT alter existing data. Thus, you must always make sure that you correct the data *before* you make that change.


              >Another strange results when issued this statement
              >
              >SELECT * FROM M_INT_FRM a
              >where a.id_div_lc is null
              >
              >It will returns 3 result (via flamerobin).

              Why is it strange? You have already established that you have nulls stored in this column, from its previous life as nullable.

              ./heLen
            • trskopo
              Thanks for your reply. I think my database is in problem, because when I tried to restored it, I got error Engine Message : validation error for column
              Message 6 of 14 , May 8, 2012
              View Source
              • 0 Attachment
                Thanks for your reply.

                I think my database is in problem, because when I tried to restored it, I got error

                Engine Message :
                validation error for column ID_DIV_LC, value "*** null ***"
                warning -- record could not be restored

                I think, may be there is a leak in Firebird to trap an error when null value allowed to store in a not null value definition. I forgot how to insert this null value to the database.

                Thanks and regards,
                Sugiarto

                > What's the execution plan? If it's using an index on ID_DIV_LC and the
                > following returns a result set:
                >
                > select
                > *
                > from
                > m_int_frm a
                > where
                > a.id_trs_typ = 100
                > and a.id_div_lc + 0 = 0
                >
                >
                > Then your index on ID_DIV_LC might be problematic/corrupted.
                >
                >
                > --
                > With regards,
                > Thomas Steinmaurer (^TS^)
                > Firebird Technology Evangelist
                >
                > http://www.upscene.com/
                >
                > Do you care about the future of Firebird? Join the Firebird Foundation:
                > http://www.firebirdsql.org/en/firebird-foundation/
                >
              • Alexandre Benson Smith
                ... No, As said before, in the beginning the colunm allows null, you insert some records with null on that column, then you changed the column to be not null,
                Message 7 of 14 , May 8, 2012
                View Source
                • 0 Attachment
                  Em 9/5/2012 00:00, trskopo escreveu:
                  > Thanks for your reply.
                  >
                  > I think my database is in problem, because when I tried to restored it, I got error
                  >
                  > Engine Message :
                  > validation error for column ID_DIV_LC, value "*** null ***"
                  > warning -- record could not be restored
                  >
                  > I think, may be there is a leak in Firebird to trap an error when null value allowed to store in a not null value definition. I forgot how to insert this null value to the database.
                  >
                  > Thanks and regards,
                  > Sugiarto
                  >

                  No,

                  As said before, in the beginning the colunm allows null, you insert some
                  records with null on that column, then you changed the column to be not
                  null, since it's not null the back-up won't restore.

                  You need to provide some value for a column that allows null when you
                  change it to not null

                  see you !
                • trskopo
                  Thanks for your reply. I did another test, just to make sure. 1) Create a test table create table test (id int, nm varchar(12)); 2) insert some values insert
                  Message 8 of 14 , May 8, 2012
                  View Source
                  • 0 Attachment
                    Thanks for your reply.

                    I did another test, just to make sure.
                    1) Create a test table
                    create table test (id int, nm varchar(12));

                    2) insert some values
                    insert into test (nm) values('test1');
                    insert into test values(2, 'test2');

                    3) change id's column def
                    UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1
                    WHERE RDB$FIELD_NAME = 'ID' AND RDB$RELATION_NAME = 'TEST';

                    4) select a table
                    select * from test (via flamerobin)
                    result :

                    ID NM
                    [null] test1
                    2 test2

                    From those test, I think :
                    1) Firebird didn't raise an error when set null column into not null column (so I have to be more careful in the future)

                    2) return set shows null value when it is null. This is different from my first case when return set shows 0 for null value. So I guess what happened to my first case didn't come because I changed column definition from null to not null, it is still a mystery.

                    Thanks and regard,
                    Sugiarto


                    --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
                    >
                    > At 02:26 PM 9/05/2012, you wrote:
                    > >Thanks for your replay.
                    > >
                    > >If you see null with ibexpert, how come this data can be store in Firebird, since ddl for this column not allow for null value? Could it be a bug in Firebird?
                    >
                    > It was stored at some point before the column was changed to NOT NULL. When you do that change, the engine does NOT alter existing data. Thus, you must always make sure that you correct the data *before* you make that change.
                    >
                    >
                    > >Another strange results when issued this statement
                    > >
                    > >SELECT * FROM M_INT_FRM a
                    > >where a.id_div_lc is null
                    > >
                    > >It will returns 3 result (via flamerobin).
                    >
                    > Why is it strange? You have already established that you have nulls stored in this column, from its previous life as nullable.
                    >
                    > ./heLen
                    >
                  • Lester Caine
                    ... You have to understand that this is a hack that directly updates the systems tables rather than a managed change to the null flag. If you are using
                    Message 9 of 14 , May 8, 2012
                    View Source
                    • 0 Attachment
                      trskopo wrote:
                      > 3) change id's column def
                      > UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1
                      > WHERE RDB$FIELD_NAME = 'ID' AND RDB$RELATION_NAME = 'TEST';

                      You have to understand that this is a 'hack' that directly updates the systems
                      tables rather than a managed change to the null flag.
                      If you are using Flamerobin, then us it's ddl management which would have asked
                      'what value do you want for nulls' when the flag for the field was changed to
                      'not null'

                      --
                      Lester Caine - G8HFL
                      -----------------------------
                      Contact - http://lsces.co.uk/wiki/?page=contact
                      L.S.Caine Electronic Services - http://lsces.co.uk
                      EnquirySolve - http://enquirysolve.com/
                      Model Engineers Digital Workshop - http://medw.co.uk//
                      Firebird - http://www.firebirdsql.org/index.php
                    • Mark Rotteveel
                      ... You should *not* modify the system tables directly to achieve this, but use DDL to do this. For all you know the DDL does additional checks and table
                      Message 10 of 14 , May 9, 2012
                      View Source
                      • 0 Attachment
                        On Wed, 09 May 2012 03:40:52 -0000, "trskopo" <trskopo@...> wrote:
                        > 3) change id's column def
                        > UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1
                        > WHERE RDB$FIELD_NAME = 'ID' AND RDB$RELATION_NAME = 'TEST';

                        You should *not* modify the system tables directly to achieve this, but
                        use DDL to do this. For all you know the DDL does additional checks and
                        table changes that won't occur when you manipulate the system tables. In
                        other words: always use DDL if it is available, and only modify system
                        tables if there really is no other way.

                        > 4) select a table
                        > select * from test (via flamerobin)
                        > result :
                        >
                        > ID NM
                        > [null] test1
                        > 2 test2
                        >
                        > From those test, I think :
                        > 1) Firebird didn't raise an error when set null column into not null
                        > column (so I have to be more careful in the future)

                        That is documented behavior; behavior which might be annoying, but I think
                        it is almost impossible to do otherwise on a MVCC-type database like
                        Firebird (there might be active transaction which modified or inserted
                        records with a NULL value). Also: if Firebird would throw an error for
                        this, it can't do that if you directly manipulate the system tables.
                      • trskopo
                        Yes, I understand, this is for test only. Thanks and regrads, Sugiarto
                        Message 11 of 14 , May 9, 2012
                        View Source
                        • 0 Attachment
                          Yes, I understand, this is for test only.

                          Thanks and regrads,
                          Sugiarto

                          --- In firebird-support@yahoogroups.com, Lester Caine <lester@...> wrote:
                          >
                          > trskopo wrote:
                          > > 3) change id's column def
                          > > UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1
                          > > WHERE RDB$FIELD_NAME = 'ID' AND RDB$RELATION_NAME = 'TEST';
                          >
                          > You have to understand that this is a 'hack' that directly updates the systems
                          > tables rather than a managed change to the null flag.
                          > If you are using Flamerobin, then us it's ddl management which would have asked
                          > 'what value do you want for nulls' when the flag for the field was changed to
                          > 'not null'
                          >
                          > --
                          > Lester Caine - G8HFL
                          > -----------------------------
                          > Contact - http://lsces.co.uk/wiki/?page=contact
                          > L.S.Caine Electronic Services - http://lsces.co.uk
                          > EnquirySolve - http://enquirysolve.com/
                          > Model Engineers Digital Workshop - http://medw.co.uk//
                          > Firebird - http://www.firebirdsql.org/index.php
                          >
                        Your message has been successfully submitted and would be delivered to recipients shortly.