[Info-ingres] Ingres Star and group by.

[Adrian Williamson]

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.

 

 

[Karl Schendel]

> On Oct 20, 2021, at 7:21 AM, Adrian Williamson <adrian.w...@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