1. Forum
  2. Usenet
  3. COMP.SYS.IBM.AS400.MISC

  • SELECT COUNT(*) oddity

    From Jonathan Ball@21:1/5 to All on Fri Sep 28 14:36:08 2018
    I'm working with a large physical file. The file has rows in it. There
    are about 365.8 million active rows, and some 123.8 million deleted rows.

    If I run

    select * from the_lib.the_table

    I get a result set of rows.

    If I run

    select count(*) from the_lib.the_table

    I get back null.

    The file has one member. The columns are all DEC and CHAR. The largest decimal column is 9 digits, and longest character column is 60 bytes.
    None of the columns is nullable.

    I've never seen SELECT COUNT(*) return a null value. If there are rows,
    I get the number, and if the table or file is empty, I get zero.

    Thoughts?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jonathan Ball@21:1/5 to Jonathan Ball on Fri Sep 28 16:35:05 2018
    On 9/28/2018 2:36 PM, Jonathan Ball wrote:
    I'm working with a large physical file.  The file has rows in it.  There are about 365.8 million active rows, and some 123.8 million deleted rows.

    I was off by an order of magnitude on the number of active rows. There
    are 3.658 *billion* rows.

    I still wouldn't think that would make a difference. If I look at
    SYSTABLESTAT for the table, it shows the correct number of rows in the NUMBER_ROWS column.


    If I run

       select * from the_lib.the_table

    I get a result set of rows.

    If I run

       select count(*) from the_lib.the_table

    I get back null.

    The file has one member.  The columns are all DEC and CHAR.  The largest decimal column is 9 digits, and longest character column is 60 bytes.
    None of the columns is nullable.

    I've never seen SELECT COUNT(*) return a null value.  If there are rows,
    I get the number, and if the table or file is empty, I get zero.

    Thoughts?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Buck@21:1/5 to Jonathan Ball on Sat Sep 29 05:46:00 2018
    On Friday, September 28, 2018 at 7:35:06 PM UTC-4, Jonathan Ball wrote:

    I was off by an order of magnitude on the number of active rows. There
    are 3.658 *billion* rows.

    I still wouldn't think that would make a difference. If I look at SYSTABLESTAT for the table, it shows the correct number of rows in the NUMBER_ROWS column.

    I suspect that the NULL is really overflow.
    What happens if you CAST the count to a large int, or float?
    --buck

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Dr.UgoGagliardelli@21:1/5 to All on Sat Sep 29 17:52:38 2018
    Il 29.09.2018 14.46, Buck ha scritto:
    On Friday, September 28, 2018 at 7:35:06 PM UTC-4, Jonathan Ball wrote:

    I was off by an order of magnitude on the number of active rows. There
    are 3.658 *billion* rows.

    I still wouldn't think that would make a difference. If I look at
    SYSTABLESTAT for the table, it shows the correct number of rows in the
    NUMBER_ROWS column.

    I suspect that the NULL is really overflow.
    Yes, for sure.

    What happens if you CAST the count to a large int, or float?
    I bet nothing happens. He should use count_big instead of count.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Dr.UgoGagliardelli@21:1/5 to All on Sat Sep 29 17:50:51 2018
    Il 29.09.2018 01.35, Jonathan Ball ha scritto:
    On 9/28/2018 2:36 PM, Jonathan Ball wrote:
    I'm working with a large physical file.  The file has rows in it.
    There are about 365.8 million active rows, and some 123.8 million
    deleted rows.

    I was off by an order of magnitude on the number of active rows.  There
    are 3.658 *billion* rows.

    I still wouldn't think that would make a difference.  If I look at SYSTABLESTAT for the table, it shows the correct number of rows in the NUMBER_ROWS column.

    The result cannot mup to an integer, integer max value is about 2
    billion, use COUNT_BIG instead, that can return a bigger number.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jonathan Ball@21:1/5 to Rudy Canoza on Tue Oct 2 17:50:16 2018
    On 10/2/2018 5:48 PM, Rudy Canoza wrote:

    Whoops! I posted from an alternate Usenet account.


    On 9/29/2018 8:50 AM, Dr.UgoGagliardelli wrote:
    Il 29.09.2018 01.35, Jonathan Ball ha scritto:
    On 9/28/2018 2:36 PM, Jonathan Ball wrote:
    I'm working with a large physical file.  The file has rows in it.
    There are about 365.8 million active rows, and some 123.8 million
    deleted rows.

    I was off by an order of magnitude on the number of active rows.
    There are 3.658 *billion* rows.

    I still wouldn't think that would make a difference.  If I look at
    SYSTABLESTAT for the table, it shows the correct number of rows in
    the NUMBER_ROWS column.

    The result cannot mup to an integer, integer max value is about 2
    billion, use COUNT_BIG instead, that can return a bigger number.

    That was it!  I hadn't noticed in my SQL script message area that I was getting a SQLSTATE of 01519:  "The null value has been assigned to a variable, because a numeric value is out of range."

    Also, while I like to think I know a lot of the built-in functions, I
    hadn't encountered COUNT_BIG before.  That fixed it.

    grazie mille

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jonathan Ball@21:1/5 to Dr.UgoGagliardelli on Tue Oct 2 17:53:32 2018
    On 9/29/2018 8:52 AM, Dr.UgoGagliardelli wrote:
    Il 29.09.2018 14.46, Buck ha scritto:
    On Friday, September 28, 2018 at 7:35:06 PM UTC-4, Jonathan Ball wrote:

    I was off by an order of magnitude on the number of active rows.  There >>> are 3.658 *billion* rows.

    I still wouldn't think that would make a difference.  If I look at
    SYSTABLESTAT for the table, it shows the correct number of rows in the
    NUMBER_ROWS column.

    I suspect that the NULL is really overflow.
    Yes, for sure.

    What happens if you CAST the count to a large int, or float?
    I bet nothing happens. He should use count_big instead of count.


    Correct. I tried both BIGINT(COUNT(*)) and FLOAT(COUNT(*)), and neither worked. It's because COUNT(*) is INT, and the number exceeds the
    capacity of an INT value.

    What *did* work, but took a long time to run (because it's doing a table
    scan), was BIGINT(SUM(1)).

    COUNT_BIG is instantaneous and gives the correct result.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Rudy Canoza@21:1/5 to Dr.UgoGagliardelli on Tue Oct 2 17:48:56 2018
    On 9/29/2018 8:50 AM, Dr.UgoGagliardelli wrote:
    Il 29.09.2018 01.35, Jonathan Ball ha scritto:
    On 9/28/2018 2:36 PM, Jonathan Ball wrote:
    I'm working with a large physical file.  The file has rows in it.
    There are about 365.8 million active rows, and some 123.8 million
    deleted rows.

    I was off by an order of magnitude on the number of active rows.
    There are 3.658 *billion* rows.

    I still wouldn't think that would make a difference.  If I look at
    SYSTABLESTAT for the table, it shows the correct number of rows in the
    NUMBER_ROWS column.

    The result cannot mup to an integer, integer max value is about 2
    billion, use COUNT_BIG instead, that can return a bigger number.

    That was it! I hadn't noticed in my SQL script message area that I was
    getting a SQLSTATE of 01519: "The null value has been assigned to a
    variable, because a numeric value is out of range."

    Also, while I like to think I know a lot of the built-in functions, I
    hadn't encountered COUNT_BIG before. That fixed it.

    grazie mille

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Dr.UgoGagliardelli@21:1/5 to All on Wed Oct 3 09:56:00 2018
    Il 29.09.2018 01.35, Jonathan Ball ha scritto:
    [...]
    I hadn't encountered COUNT_BIG before.  That fixed it.
    It's there since V4R5, about 18 years ago!
    :-)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)