query with EXTRACT (.Year / .Month ...) works in where condition but not in select

[cil22]

Description

The extraction of year, month etc. by adding .Year / .Month to the field does not seem to be reflected in the "select" part of the query. It works for the "where" part.

Repro steps

1. query { for r in ctx.schema.mytable do where (r.myDate.Year > 2015) select (r.myDate.Year) distinct

2. Add event for SqlQuery to inspect SQL statement generated.

Expected behavior

Only the "year" of the date field should be returned.

Actual behavior

Statement is generated in a way that the whole date is returned.

Known workarounds

No workaround known.

Related information

• Used database: Oracle Database 12c
• Operating system: Windows 10
• SqlProvider 1.1.77
• .NET Runtime / FSI

[Thorium]

Hmm, this might be an Oracle specific problem... Are you using SelectOperations.DatabaseSide?

[cil22]

Good point - I wasn't using SelectOperations.DatabaseSide so far. I tried it in FSI and it works! Shouldn't SelectOperations.DotNetSide work as well? Thanks for your help!

Am 10.11.2019 19:55 schrieb Tuomas Hietanen:

Hmm, this might be an Oracle specific problem... Are you using SelectOperations.DatabaseSide [1]?

-- You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub [2], or unsubscribe [3].

Links:

[1] https://fsprojects.github.io/SQLProvider/core/querying.html#SelectOperations-DatabaseSide [2] https://github.com/fsprojects/SQLProvider/issues/651?email_source=notifications&email_token=AMYLXI57RBG4NWAXF6OIYG3QTBKKXA5CNFSM4JLNBRF2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDVD7DQ#issuecomment-552222606 [3] https://github.com/notifications/unsubscribe-auth/AMYLXI2R2TMTIZBLL2677YDQTBKKXANCNFSM4JLNBRFQ

[Thorium]

It should... I don't have Oracle, so hard to debug what goes wrong. If you have spare time you can try to see...

[Thorium]

Ok, just debugged this, the reason it will not work in DotNetSide operations is that DotNetSide will select the columns and do the operations in .NET. What happens is SQL generated looks like:

SELECT DISTINCT myDate 
FROM schema.mytable
...

...and because of myDate is a date-column, it actually fetches all the distinct dates and then takes the date.Year in .NET.