#1  
Old 15th January 2020, 09:30 PM
yannou yannou is offline
Member
 
Join Date: Nov 2013
Posts: 69
Default 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
  #2  
Old 31st January 2020, 12:09 AM
yannou yannou is offline
Member
 
Join Date: Nov 2013
Posts: 69
Default

Did you guy tested this ?

Should i post it in mantis ?

Regards
  #3  
Old 31st January 2020, 08:39 AM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: Strange Behavior on Indices choice using current_date alias

> Should i post it in mantis ?

Please do.

--
Eivind Bakkestuen [NDD]
  #4  
Old 26th May 2020, 05:15 PM
yannou yannou is offline
Member
 
Join Date: Nov 2013
Posts: 69
Default

Hello,

Can you give a quick look please ?

https://www.nexusdb.com/mantis/view.php?id=2340
  #5  
Old 26th May 2020, 06:37 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default 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.
  #6  
Old 26th May 2020, 07:11 PM
yannou yannou is offline
Member
 
Join Date: Nov 2013
Posts: 69
Default

Quote:
Originally Posted by Thorsten Engler [NDA] View Post
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 !


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Strange behavior in a Japanese environment Shinji Tomoyuki Binaries 1 27th October 2015 11:05 AM
Strange behavior at a client Arne De Herdt nexusdb.public.support 1 27th April 2010 05:20 PM
Closest to current_date (Tricking the optimizer) =?ISO-8859-1?Q?Cl=E9ment_Doss?= nexusdb.public.support.sql 4 7th March 2010 04:41 AM
Strange behavior in string field Alvaro GP nexusdb.public.support 5 15th May 2005 11:10 AM
strange server behavior Stéphane Thiers nexusdb.public.support 14 2nd April 2004 11:40 PM


All times are GMT +11. The time now is 08:51 AM.


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