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

    From Adrian Williamson@21:1/5 to All on Wed Oct 20 12:21:50 2021
    This is a multipart message in MIME format.

    Hi,



    Using



    II 11.0.0 (su9.us5/100)

    15419



    I recently found the need to use star to do daily checks across two databases.



    I tried to write an SQL to group some data in one database with the where clause referencing two other tables in the other database.



    I got an ungrouped data set – kind of



    I thought this might be really difficult to summarise but while waiting for some queries to finish I found this did the trick:



    In database number2:



    create table grouptest1 ( dt date not null);\g

    insert into grouptest1 values ('1-jan-2020');\g

    insert into grouptest1 values ('2-jan-2020');\g

    insert into grouptest1 values ('3-jan-2020');\g

    insert into grouptest1 values ('1-jul-2020');\g

    insert into grouptest1 values ('2-jul-2020');\g

    insert into grouptest1 values ('20-nov-2020');\g

    insert into grouptest1 values ('21-nov-2020');\g



    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|

    +-------------------------+-------------+



    So I would expect three rows back as if I had done it directly on the database number2:



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

    Executing . . .





    +-------------------------+-------------+

    |col1 |col2 |

    +-------------------------+-------------+

    |01-nov-2020 | 2|

    |01-jul-2020 | 2|

    |01-jan-2020 | 3|

    +-------------------------+-------------+



    Anyone else using star and noticed this?



    I think I am using legitimate syntax.






    <html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta
    http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
    /* Font Definitions */
    @font-face
    {font-family:"Cambria Math";
    panose-1:2 4 5 3 5 4 6 3 2 4;}
    @font-face
    {font-family:Calibri;
    panose-1:2 15 5 2 2 2 4 3 2 4;}
    @font-face
    {font-family:"Lucida Console";
    panose-1:2 11 6 9 4 5 4 2 2 4;}
    @font-face
    {font-family:Consolas;
    panose-1:2 11 6 9 2 2 4 3 2 4;}
    /* Style Definitions */
    p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
    {mso-style-priority:99;
    mso-style-link:"Plain Text Char";
    margin:0cm;
    margin-bottom:.0001pt;
    font-size:11.0pt;
    font-family:"Calibri",sans-serif;
    mso-fareast-language:EN-US;}
    span.PlainTextChar
    {mso-style-name:"Plain Text Char";
    mso-style-priority:99;
    mso-style-link:"Plain Text";
    font-family:"Calibri",sans-serif;}
    .MsoChpDefault
    {mso-style-type:export-only;
    font-family:"Calibri",sans-serif;
    mso-fareast-language:EN-US;}
    @page WordSection1
    {size:612.0pt 792.0pt;
    margin:72.0pt 72.0pt 72.0pt 72.0pt;}
    div.WordSection1
    {page:WordSection1;}
    </style><!--[if gte mso 9]><xml>
    <o:shapedefaults v:ext="edit" spidmax="1026" />
    </xml><![endif]--><!--[if gte mso 9]><xml>
    <o:shapelayout v:ext="edit">
    <o:idmap v:ext="edit" data="1" /> </o:shapelayout></xml><![endif]--></head><body lang=EN-GB link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoPlainText>Hi,<o:p></o:p></p><p class=MsoPlainText><o:p>&nbsp;</o:p></p><p class=MsoPlainText>Using<o:p></o:p></p><p class=
    MsoPlainText><o:p>&nbsp;</o:p></p><p class=MsoPlainText>II 11.0.0 (su9.us5/100)<o:p></o:p></p><p class=MsoPlainText>15419<o:p></o:p></p><p class=MsoPlainText><o:p>&nbsp;</o:p></p><p class=MsoPlainText>I recently found the need to use star to do daily
    checks across two databases.<o:p></o:p></p><p class=MsoPlainText><o:p>&nbsp;</o:p></p><p class=MsoPlainText>I tried to write an SQL to group some data in one database with the where clause referencing two other tables in the other database.<o:p></o:p></p>
    <p class=MsoPlainText><o:p>&nbsp;</o:p></p><p class=MsoPlainText>I got an ungrouped data set – kind of<o:p></o:p></p><p class=MsoPlainText><o:p>&nbsp;</o:p></p><p class=MsoPlainText>I thought this might be really difficult to summarise but while
    waiting for some queries to finish I found this did the trick:<o:p></o:p></p><p class=MsoPlainText><o:p>&nbsp;</o:p></p><p class=MsoPlainText><span style='color:black'>In database number2:<o:p></o:p></span></p><p class=MsoPlainText><span style='color:
    black'><o:p>&nbsp;</o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>create table grouptest1 ( dt date not null);\g<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:
    black'>insert into grouptest1 values ('1-jan-2020');\g<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>insert into grouptest1 values ('2-jan-2020');\g<o:p></o:p></span></p><p class=MsoPlainText><span
    style='font-family:"Lucida Console";color:black'>insert into grouptest1 values ('3-jan-2020');\g<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>insert into grouptest1 values ('1-jul-2020');\g<o:p></o:p></
    span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>insert into grouptest1 values ('2-jul-2020');\g<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>insert into grouptest1
    values ('20-nov-2020');\g<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>insert into grouptest1 values ('21-nov-2020');\g<o:p></o:p></span></p><p class=MsoPlainText><span style='color:black'><o:p>&nbsp;</
    </span></p><p class=MsoPlainText><span style='color:black'>In the star database:<o:p></o:p></span></p><p class=MsoPlainText><span style='color:black'><o:p>&nbsp;</o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:
    black'>register table  gt<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>as link from grouptest1<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>with
    node = 'nodey', database = 'number2';\g<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'><o:p>&nbsp;</o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>select date_
    trunc('month',dt) , count(*) from gt group by 1;\g<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'><o:p>&nbsp;</o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>+
    -------------------------+-------------+<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>|col1                     |col2         |<o:p></o:p></span></p><p class=MsoPlainText><
    span style='font-family:"Lucida Console";color:black'>+-------------------------+-------------+<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>|01-nov-2020              |          
    2|<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>|01-nov-2020              |            2|<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";
    color:black'>|01-jul-2020              |            2|<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>|01-jul-2020              |            2|<o:p></o:p></
    span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>|01-jan-2020              |            3|<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>|01-
    jan-2020              |            3|<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>|01-jan-2020              |            3|<o:p></o:p></span></p><p
    class=MsoPlainText><span style='font-family:"Lucida Console";color:black'>+-------------------------+-------------+<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'><o:p>&nbsp;</o:p></span></p><p class=
    MsoPlainText>So I would expect three rows back as if I had done it directly on the database number2:<o:p></o:p></p><p class=MsoPlainText><o:p>&nbsp;</o:p></p><p class=MsoPlainText><span style='font-family:"Lucida Console"'>* select date_trunc('month',dt)
    , count(*) from grouptest1  group by 1;\g<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console"'>Executing . . .<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console"'><o:p>&nbsp;</o:p></span>
    </p><p class=MsoPlainText><span style='font-family:"Lucida Console"'><o:p>&nbsp;</o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console"'>+-------------------------+-------------+<o:p></o:p></span></p><p class=MsoPlainText><span
    style='font-family:"Lucida Console"'>|col1                     |col2         |<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console"'>+-------------------------+-------------+<o:p></o:p></span></
    <p class=MsoPlainText><span style='font-family:"Lucida Console"'>|01-nov-2020              |            2|<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console"'>|01-jul-2020            
      |            2|<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console"'>|01-jan-2020              |            3|<o:p></o:p></span></p><p class=MsoPlainText><span style='font-family:"
    Lucida Console"'>+-------------------------+-------------+<o:p></o:p></span></p><p class=MsoPlainText><o:p>&nbsp;</o:p></p><p class=MsoPlainText>Anyone else using star and noticed this?<o:p></o:p></p><p class=MsoPlainText><o:p>&nbsp;</o:p></p><p class=
    MsoPlainText>I think I am using legitimate syntax.<o:p></o:p></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'><o:p>&nbsp;</o:p></span></p><p class=MsoPlainText><span style='font-family:"Lucida Console";color:black'><o:p>&
    nbsp;</o:p></span></p></div></body></html>

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Karl Schendel@21:1/5 to All on Fri Oct 22 20:21:16 2021
    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

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