NexusDB Newsgroups

NexusDB Newsgroups (http://www.nexusdb.com/forums/index.php)
-   nexusdb.public.support.sql (http://www.nexusdb.com/forums/forumdisplay.php?f=25)
-   -   Strange Behavior on Indices choice using current_date alias (http://www.nexusdb.com/forums/showthread.php?t=20909)

yannou 15th January 2020 09:30 PM

Strange Behavior on Indices choice using current_date alias
 
Hello guys I found something strange in the index choice for some query that are using "current_date".

I did not test other alias.

For example we have this test table with 2 indices :

- 1 using int + date
- 1 using date only

/* TEST */
CREATE TABLE "TEST"
(
"Id" AUTOINC,
"Int_" SMALLINT,
"Date_" DATE
);
CREATE INDEX "Index_INT_DATE" ON "TEST"("Int_", "Date_");
CREATE INDEX "Index_DATE" ON "TEST"("Date_");


Let see what index are used when I query those :

SELECT * FROM "TEST" where int_ = 1 and date_ >= current_date ; // use Index_DATE => WRONG
SELECT * FROM "TEST" where int_ = 1 and date_ = current_date ; // use Index_INT_DATE => GOOD
SELECT * FROM "TEST" where int_ = 1 and date_ > DATE '2020-01-15'; // use Index_INT_DATE => GOOD

It seems using "Date_ > current_date" (or "Date_ < current_date") makes nexus use a "bad index" (not the best)

Querying "Date_> DATE '2020-01-15'" is working fine.

Hopes you understand ;)

Regards

yannou 31st January 2020 12:09 AM

Did you guy tested this ?

Should i post it in mantis ?

Regards

Eivind Bakkestuen [NDD] 31st January 2020 08:39 AM

Re: Strange Behavior on Indices choice using current_date alias
 
> Should i post it in mantis ?

Please do.

--
Eivind Bakkestuen [NDD]

yannou 26th May 2020 05:15 PM

Hello,

Can you give a quick look please ?

https://www.nexusdb.com/mantis/view.php?id=2340

Thorsten Engler [NDA] 26th May 2020 06:37 PM

Re: Strange Behavior on Indices choice using current_date alias
 
yannou wrote:

>
> Hello,
>
> Can you give a quick look please ?
>
> https://www.nexusdb.com/mantis/view.php?id=2340


I've looked at it before.

Yes, the index choice is sub-optimal.

The difference in behaviour is because one is a literal and the other a
function.

No, it's not a trivial change because touching this logic degrades performance
in 17 of the queries that are part of our automated test suite.

I'm contemplating adding a way to specify a suggested index for the SQL engine
to use as part of a SELECT statement.

yannou 26th May 2020 07:11 PM

Quote:

Originally Posted by Thorsten Engler [NDA] (Post 113313)
yannou wrote:

>
> Hello,
>
> Can you give a quick look please ?
>
> https://www.nexusdb.com/mantis/view.php?id=2340


I've looked at it before.

Yes, the index choice is sub-optimal.

The difference in behaviour is because one is a literal and the other a
function.

No, it's not a trivial change because touching this logic degrades performance
in 17 of the queries that are part of our automated test suite.

I'm contemplating adding a way to specify a suggested index for the SQL engine
to use as part of a SELECT statement.



Thanks !


All times are GMT +11. The time now is 10:16 PM.

Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.