Message-ID:

It's not hard to admit errors that are [only] cosmetically wrong. -- J. K. Galbraith

devel / comp.databases.ingres / Re: [Info-ingres] Ingres Star and group by.

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

<mailman.99.1634948479.1681.info-ingres@lists.planetingres.org>

https://www.rocksolidbbs.com/devel/article-flat.php?id=129&group=comp.databases.ingres#129

copy link Newsgroups: comp.databases.ingres

Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!buffer2.nntp.dca1.giganews.com!buffer1.nntp.dca1.giganews.com!news.giganews.com.POSTED!not-for-mail
NNTP-Posting-Date: Fri, 22 Oct 2021 19:22:03 -0500
From: schendel@kbcomputer.com (Karl Schendel)
Content-Type: text/plain;
charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 13.4 $3608.120.23.2.7$)
Date: Fri, 22 Oct 2021 20:21:16 -0400
References: <001301d7c5a4$adf87a50$09e96ef0$@rationalcommerce.com>
To: info-ingres@lists.planetingres.org
In-Reply-To: <001301d7c5a4$adf87a50$09e96ef0$@rationalcommerce.com>
X-Mailer: Apple Mail (2.3608.120.23.2.7)
Subject: Re: [Info-ingres] Ingres Star and group by.
X-BeenThere: info-ingres@lists.planetingres.org
X-Mailman-Version: 2.1.12
Precedence: list
List-Id: <info-ingres.lists.planetingres.org>
List-Unsubscribe: <https://lists.planetingres.org/mailman/options/info-ingres>,
<mailto:info-ingres-request@lists.planetingres.org?subject=unsubscribe>
List-Archive: <https://lists.planetingres.org/pipermail/info-ingres/>
List-Post: <mailto:info-ingres@lists.planetingres.org>
List-Help: <mailto:info-ingres-request@lists.planetingres.org?subject=help>
List-Subscribe: <https://lists.planetingres.org/mailman/listinfo/info-ingres>,
<mailto:info-ingres-request@lists.planetingres.org?subject=subscribe>
Newsgroups: comp.databases.ingres
Message-ID: <mailman.99.1634948479.1681.info-ingres@lists.planetingres.org>
Lines: 44
X-Usenet-Provider: http://www.giganews.com
X-Trace: sv3-1prKZXf5pJeVjzJhZ+emgIRlNzI9AT5qDnZ0JMHZJj994ru0/zvpm2h2dQzykggBvSxI7utS9OWcF/L!DEd+5JAKRoaVw7+7e+P/kXFFGl06JeVq1+IssfTWE5pQ6BG1fZeaZJX9kUALAYpBaXgjv05GwPcM!aCsDHZR+ebk/B6+x+Ifu3g==
X-Complaints-To: abuse@giganews.com
X-DMCA-Notifications: http://www.giganews.com/info/dmca.html
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.3.40
X-Original-Bytes: 3705

by: Karl Schendel - Sat, 23 Oct 2021 00:21 UTC

> 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

Subject	Author
Re: [Info-ingres] Ingres Star and group by.	Karl Schendel