6 Replies Latest reply: Aug 28, 2012 5:28 AM by Gennady Sigalaev RSS

    TimesTen SQL with group by returning multiple rows

    957775
      I have a Active-Standby TimesTen nodes.

      Using group by with or without having clause:
      Whenever I do a group by query on table1 table with or without having clause, SQL returns multiple rows. This looks very strange to me. Each time it gives different count

      Command> select count(*) from table1 group by pname having pname='pool';
      < 390400 >
      1 row found.
      Command> select count(*) from table1 group by pname having pname='pool';
      < 390608 >
      < 32639 >
      2 rows found.
      Command> select count(*) from table1 group by pname having pname='pool';
      < 2394 >
      < 351057 >
      2 rows found.
      Command> select count(*) from table1 group by pname having pname='pool';
      < 305732 >
      1 row found.
      Command> select count(*) from table1 group by pname having pname='pool';
      < 420783 >
      1 row found.


      Command> select count(*),pool_name from root.rms_address_pools group by pool_name order by pool_name;
      < *435473, pool* >
      < *32313, pool* >
      < 453, smvG3 >
      < *28980, pool* >
      < 3786, smvG4 >
      < *26025, pool* >
      < 236120, smvG6 >
      < 131455, smcG3 >
      < *65150, pool* >
      < 23, snt1G1 >
      < 510, snt2G1 >
      < 510, snt2G2 >

      Using where clause:
      Command> select count(*) from table1 where pname='pool';
      < *442354* >
      1 row found.
      Command> select count(*) from table1 where pname='pool';
      < 442354 >
      1 row found.

      Table description:
      Command> desc table1;

      Table table1:
      Columns:
      *IP_ADDRESS                      BIGINT NOT NULL
      PNAME CHAR (32) NOT NULL
      SITEID TINYINT NOT NULL

      1 table found.

      ttVersion:
      bash-3.00# ./ttVersion
      TimesTen Release *7.0.3.1.0 (64 bit Solaris)* (tt70:17001) 2007-10-30T22:17:07Z
      Instance admin: root
      Instance home directory: /TimesTen/tt70
      Daemon home directory: /var/TimesTen/tt70
      bash-3.00#

      Could any one suggest what is wrong with my SQL? or is it a bug with TimesTen?

      Many thanks in advance.

      Br,
      Brij
        • 1. Re: TimesTen SQL with group by returning multiple rows
          Gennady Sigalaev
          Hi Brij,
          Whenever I do a group by query on table1 table with or without having clause, SQL returns multiple rows. This looks very strange to me. Each time it gives different count
          select count(*) from table1 group by pname having pname='pool';
          What do you expect? It rely depends on data. I see that your data are changing (each time you get a new result) and you are grouping data by "pname", so if someone insert a new pname you will see one more row in the result.
          Could any one suggest what is wrong with my SQL? or is it a bug with TimesTen?
          Everything is fine. It works fine (TimesTen and SQL).

          Best regards,
          Gena
          • 2. Re: TimesTen SQL with group by returning multiple rows
            Gennady Sigalaev
            Hi Brij,
            Whenever I do a group by query on table1 table with or without having clause, SQL returns multiple rows. This looks very strange to me. Each time it gives different count
            select count(*) from table1 group by pname having pname='pool';
            What do you expect? It rely depends on data. I see that your data are changing (each time you get a new result) and you are grouping data by "pname", so if someone insert a new pname you will see one more row in the result.
            Could any one suggest what is wrong with my SQL? or is it a bug with TimesTen?
            Everything is fine. It works fine (TimesTen and SQL).

            Best regards,
            Gena
            • 3. Re: TimesTen SQL with group by returning multiple rows
              957775
              Thanks Gena,

              But why i am getting multiple entries for pool by group by as below , yet where clause giving only one output.

              Command> select count(*),pool_name from root.rms_address_pools group by pool_name order by pool_name;
              < 435473, pool >
              < 32313, pool >
              < 453, smvG3 >
              < 28980, pool >
              < 3786, smvG4 >
              < 26025, pool >
              < 236120, smvG6 >
              < 131455, smcG3 >
              < 65150, pool >
              < 23, snt1G1 >
              < 510, snt2G1 >
              < 510, snt2G2 >

              Using where clause:
              Command> select count(*) from table1 where pname='pool';
              < 442354 >
              1 row found.
              Command> select count(*) from table1 where pname='pool';
              < 442354 >
              1 row found.

              Do you mean that someone inserted the pool having different columns.

              Regards, Brij
              • 4. Re: TimesTen SQL with group by returning multiple rows
                Gennady Sigalaev
                H Brij,

                I am sorry, I was wrong :( now I understand what do yo mean.
                Command> select count(*) from table1 group by pname having pname='pool';
                < 390608 >
                < 32639 >
                Yes, it is weird. It looks like a bug for me.

                try - select count(*) from table1 where pname='pool' group by pname;

                Regards,
                Gena
                • 5. Re: TimesTen SQL with group by returning multiple rows
                  957775
                  Hi Gena,

                  When i execute the query with where clause, it gives me the output with more than one pool:

                  Command> select pname, count (*) from table1 where pname='pool' group by pname ;
                  < smcG3 , 18836 >
                  < pool , 423527 >
                  2 rows found.

                  Command> select pname, count (*) from table1 where pname='pool' group by pname ;
                  < intG302 , 17202 >
                  < pool , 425159 >
                  2 rows found.

                  While if give use the having clause it gives me multiple rows for one pool only ( sometimes) :

                  select pname, count (*) from table1 group by pname having pname='pool';
                  < pool , 32686 >
                  < pool , 420445 >
                  2 rows found.

                  select pname, count (*) from table1 group by pname having pname='pool';
                  < pool , 393574 >
                  < pool , 5838 >
                  < pool , 110943 >
                  3 rows found.

                  Command> select pname, count (*) from table1 group by pname having pname='pool';
                  < pool , 414590 >
                  < pool , 8395 >
                  2 rows found.

                  Please suggest what can be done in this case, need i open a case with Oracle for this.

                  Regards, Brij
                  • 6. Re: TimesTen SQL with group by returning multiple rows
                    Gennady Sigalaev
                    Hi Brij,
                    Command> select pname, count (*) from table1 where pname='pool' group by pname ;
                    < smcG3 , 18836 >
                    < pool , 423527 >
                    2 rows found.
                    Oww. It is completely wrong. You should definitely open a SR in support.oracle.com.
                    Additionally, you should upgrade your TimesTen version (7.0.3.1.0 is too old version). I am sure that 7.0.5 works fine.

                    Best regards,
                    Gena