I know this is a bit esoteric, I have a database with a set up like this:

CREATE VIEW View1 AS
SELECT
 SUM(MyFields)
FROM MyTables
GROUP BY SomeFields!

CREATE View2 AS
SELECT
 SummaryFields
FROM View1

--

Then I write SQL:

SELECT
 SomeFields
FROM View2

The Application works well, but is randomly ceasing to return data and giving a general AV after a period of use. I can't narrow the issue down much more than this, sorry.

Are there issues with such "deep" calling of Views? I can (and have started) to rewrite the SQL with Sub-Selects, and these are working just fine, but it is a lot of work.

I am using the latest EDB (234b2 I think) and Delphi 10.4.2

Adam

Yes - you can create views using views. You can also define indexes on non-updatable views which may improve execution times.

Have you tested the views out in EDB Manager?

Have you checked the Execution Plan of the SELECT statement?

Is the SELECT statement with the GROUP BY clause a valid SQL statement? That is, "Any column specification specified in the SELECT clause must occur as a parameter of an aggregated function or in the list of columns given in the GROUP BY clause."

Richard

Thanks for the reply Richard.

The weird part is that on first call the views will usually open fine. It is only on the second or third call to activate that I get an error, and at this point it is a very unclear "error at 00000xxxx" type AV, not a defined EDB Error.

I have written and tested all the SQL, it is now that the system is in production and people are using it that I am seeing the issue occur, usually after a period of up-time when everything is fine.

Restarting the instance of the Application fixes the problem, so I don't even think it is an issue with multiple users. I don't define indexes on any of the views.

On 5/12/2021 11:05 AM, Adam Brett wrote:
> The weird part is that on first call the views will usually open fine. It is only on the second or third call to activate that I get an error, and at this point it is a very unclear "error at 00000xxxx" type AV, not a defined EDB Error.
>
> I have written and tested all the SQL, it is now that the system is in production and people are using it that I am seeing the issue occur, usually after a period of up-time when everything is fine.
>
> Restarting the instance of the Application fixes the problem, so I don't even think it is an issue with multiple users. I don't define indexes on any of the views.

Do not use these much myself so cannot help.

However, sounds like something Tim should take a deeper look into - he
will likely want copy your schema and queries and such to try to dupe it

I would suggest drop direct email to support - will be quicker than him
seeing it here.

Raul

Hello Adam

<< I don't define indexes on any of the views.>>

Does the execution plan indicate that defining indexes will be of benefit?

Richard

Adam


The second view is dependent on the first view. Is there a possibility that the first view hasn't been created when the second view is accessed and that something is stopping it being automatically created?

Roy Lambert

Dear Roy & Raul,

>>The second view is dependent on the first view. Is there a
>>possibility that the first view hasn't been created when the
>>second view is accessed and that something is stopping it
>>being automatically created?

I can only guess, I have seen the error while running an App in the IDE, but the error that comes back is just an address, nothing human-readable. My sense is that something is blocking somewhere

ie, once a view is being opened by another view and then a further user requests it ... something must be gumming up.

>>However, sounds like something Tim should take a deeper look into - he
>>will likely want copy your schema and queries and such to try to dupe it

I will email Tim.

Hello

I want to mention that I encounter quite a similar error.
I create a view and then use it to run some SQL commands.
Usually, it's run all the commands without any errors.

Occasionally the first SQL command runs fine but the second command through an AV error.

Important to mention that between the commands I don't have any code.
Sorin

Adam and Sorin,

My apologies on any delays re: a fix.

I had another similar report of an AV during tear-down of queries that reference views, and that has been fixed.  The fix was delayed while I added Linux support (Free Pascal and Delphi), and that is almost done, so the fix should be available very soon.

However, just to be sure, if one (or both) of you could send me the SQL and database catalog/table files that you're using, I can definitely confirm one way or another if this is the same issue.

Thanks !

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Adam and Sorin,
>
> My apologies on any delays re: a fix.
>
> I had another similar report of an AV during tear-down of queries
> that reference views, and that has been fixed.  The fix was delayed
> while I added Linux support (Free Pascal and Delphi), and that is
> almost done, so the fix should be available very soon.
>
> However, just to be sure, if one (or both) of you could send me the
> SQL and database catalog/table files that you're using, I can
> definitely confirm one way or another if this is the same issue.
>
> Thanks !
>
> Tim Young
> Elevate Software
> www.elevatesoft.com

Hi Tim,

you have also a catalog from our database. With latest edb-version we
had also sometimes Access violations on client side with views:

SELECT * FROM V_NRK_260_PALETTEN

or by accessing those views with tEdbTable component.

--
--
Yusuf Zorlu | MicrotronX