1. Forum
  2. USENET
  3. comp.databases.ingres

  • Re: [Info-ingres] Ingres Star and group by.

    From Karl Schendel@schendel@kbcomputer.com to info-ingres on Fri Oct 22 20:21:16 2021
    From Newsgroup: comp.databases.ingres


    On Oct 20, 2021, at 7:21 AM, Adrian Williamson <adrian.williamson@rationalcommerce.com> wrote:
    ...
    In the star database:

    register table gt
    as link from grouptest1
    with node = 'nodey', database = 'number2';\g

    select date_trunc('month',dt) , count(*) from gt group by 1;\g

    +-------------------------+-------------+
    |col1 |col2 | +-------------------------+-------------+
    |01-nov-2020 | 2|
    |01-nov-2020 | 2|
    |01-jul-2020 | 2|
    |01-jul-2020 | 2|
    |01-jan-2020 | 3|
    |01-jan-2020 | 3|
    |01-jan-2020 | 3| +-------------------------+-------------+

    You've (re)discovered issue II-5204 / 1083817. Your SQL is correct. Unfortunately, Star is assuming that user query constants always
    point to the original constant text. This isn't always true and one such situation is the 'month' bit in the date_trunc function. The full analysis
    is complicated, but the end result is that opa thinks that there are two different copies of date_trunc('month',dt) and generates a quel-like
    linkback. It's not immediately clear how to fix this; there are 3 or 4 possible ways forward, all involving a fair amount of work.
    This was caused somewhere in the 10.x timeframe, it seems, as a side
    effect of fixing a totally different bug where tree fragment comparisons
    were too permissive and treated two different fragments as being the same.
    Karl
    --- Synchronet 3.21b-Linux NewsLink 1.2