Home » SQL & PL/SQL » SQL & PL/SQL » Oracle bug with TimeStamp with Local Time Zone (10g2 (any 9g +))  () 1 Vote
Oracle bug with TimeStamp with Local Time Zone [message #443103] Fri, 12 February 2010 11:15 Go to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
I highly think Oracle has a major bug with Timestamp with local time zone.

I 'd like to know your opinion.

See the problem I open on Metalink (Oracle):

The timezone offset used as reference to calculate the timestamp value presented to the user on a field TZLTZ seems to be:
- Actual behavior : Based on the actual SessiontTimeZone offset at the actual time.
- Expected behavior : Based on the actual SessiontTimeZone offset at the data time (timestamp value in the field red from the database) .

An offset of 1 hour appear while the session change from DST to non-DST or vice-versa (DST:daylight saving time).
On DST, the sessionTimeZone offset change from +/- 1 hour and seems to affect the data on write depending when we write it/read it.

The date and time should always be the same for people in the same time zone.
For example 2009-12-25 7h15 on january should also be 2009-12-25 7h15 on july for the same person on the same PC.

================================================================================================================
Problem reproduction:
================================================================================================================

Preparation:
- Set your PC (Windows) to any timezone with DST. I used GMT-05:00 Eastern Time (US & Canada)
- In SqlPlus on any database 10g.
- create table tmp_tst_tz (ts timestamp, tstz timestamp with time zone, tsltz timestamp with local time zone);
- alter session set NLS_TIMESTAMP_TZ_FORMAT='mm/dd/yyyy hh24:mi:ssxff TZH:TZM' ;
- insert into tmp_tst_tz values (systimestamp, systimestamp, systimestamp) ;


First test:
- Set your PC to date and time in winter - DST not in function (anyone will do)
- Start SqlPlus
- select sessiontimezone from dual;
- select * from tmp_tst_tz ;

Second test:
- Set you PC to date and time in the summer - DST in function (anyone will do)
- Start SqlPlus
- select sessiontimezone from dual;
- select * from tmp_tst_tz ;


================================================================================================================
My own results of tests:
================================================================================================================

======================First test:
TS
------------------------------------
TSTZ
------------------------------------
TSLTZ
------------------------------------
12-FEB-10 09.37.11.365000 AM
12-FEB-10 09.37.11.365000 AM -05:00
12-FEB-10 09.37.11.365000 AM

======================Second test:
TS
------------------------------------
TSTZ
------------------------------------
TSLTZ
------------------------------------
12-FEB-10 09.37.11.365000 AM
12-FEB-10 09.37.11.365000 AM -05:00
12-FEB-10 10.37.11.365000 AM


================================================================================================================
================================================================================================================
================================================================================================================
What do you think ?
Re: Oracle bug with TimeStamp with Local Time Zone [message #443105 is a reply to message #443103] Fri, 12 February 2010 11:27 Go to previous messageGo to next message
John Watson
Messages: 6563
Registered: January 2010
Location: Global Village
Senior Member
This looks OK to me: the TSLTZ column is being normalized to the time zone of the client. Or have I missed the point?
Re: Oracle bug with TimeStamp with Local Time Zone [message #443107 is a reply to message #443103] Fri, 12 February 2010 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think it is coherent with its definition:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref128

Quote:
TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone offset in its value. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). This datatype is useful for displaying date information in the time zone of the client system in a two-tier application.


Regards
Michel
Re: Oracle bug with TimeStamp with Local Time Zone [message #443113 is a reply to message #443107] Fri, 12 February 2010 12:51 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
Have you read all the text ?

You both tell me it is normal to write in the database for example:
"A bug happen at:" - "2009-25-12 7h15"

in december and when you look at the record in the summer you read:

"A bug happen at:" - "2009-25-12 8h15"

By the hell, what is the logic behind that ?
You find that ok... in wich case is that usefull ?
Confused

[Updated on: Fri, 12 February 2010 12:53]

Report message to a moderator

Re: Oracle bug with TimeStamp with Local Time Zone [message #443116 is a reply to message #443103] Fri, 12 February 2010 14:08 Go to previous messageGo to next message
John Watson
Messages: 6563
Registered: January 2010
Location: Global Village
Senior Member
Hi again - yes, I think it is OK. The idea is that the data is saved as a fixed value, normalized to the database time zone. That doesn't change, check it in the database_properties view (and once you create TSLTZ columns, I think you'll find you can't change the DB time zone - I can't do the test right now). Then any session querying the data will see it normalized to their local time zone. So any one session will see data consistent with its localization.
As to whether this is useful, well, that's a different matter. When all this first appeared with release 9i, I investigated it and found that every application I worked with already had code to do the necessary conversions. I suppose Oracle is providing a facility that can remove work from application developers. But I see little value to it, because it is applicable only to a client-server environment, it is not much use in a three tier environment. But it is fun to play with, isn't it?
Re: Oracle bug with TimeStamp with Local Time Zone [message #443122 is a reply to message #443116] Fri, 12 February 2010 15:46 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
It is since 9i, you are right.

I don't undestand why you say it is only applicable in client-server app ? Why not in three tier. It's there to remove all the hassle of time conversion, anywhere!

I'm not asking if it is usefull or not that Oracle do the time conversion for us. It is usefull.

I'm asking if it is usefull to use the DST offset of the actual time instead of the DST of the data (TimeStamp data) itself ?

I think that the TSLTZ should use the time zone of the session but DST of the data.

Please read everything carefully. I'm not asking a trivial question here. I report this has as a bug to Oracle. I'm looking for solid(s) argument(s) to destroy my opinion... Or ... people who support what I'm saying.

[Updated on: Fri, 12 February 2010 15:48]

Report message to a moderator

Re: Oracle bug with TimeStamp with Local Time Zone [message #443156 is a reply to message #443122] Sat, 13 February 2010 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I think that the TSLTZ should use the time zone of the session but DST of the data.

A good argument (although DST have changed many times in the past and I hardly see Oracle keeping all the changes in its DST files).

Quote:
I report this has as a bug to Oracle.

Please post here the feedback from Oracle (or the bug number).

Regards
Michel
Re: Oracle bug with TimeStamp with Local Time Zone [message #443262 is a reply to message #443156] Sun, 14 February 2010 07:49 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
I report a service request in metalink : SR 3-1393185031

I don't know if it is public or if it can be made public ?

Re: Oracle bug with TimeStamp with Local Time Zone [message #443263 is a reply to message #443262] Sun, 14 February 2010 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, a SR can't be public but if a bug is created (and you can ask for its creation) then it will be public (but a development team may hide it, if the bug goes to development).
But you can post here Oracle reply.

Regards
Michel

[Updated on: Sun, 14 February 2010 08:17]

Report message to a moderator

Re: Oracle bug with TimeStamp with Local Time Zone [message #443264 is a reply to message #443263] Sun, 14 February 2010 08:36 Go to previous messageGo to next message
John Watson
Messages: 6563
Registered: January 2010
Location: Global Village
Senior Member
Hi - anyone who attended my 9i New Features courses for Oracle Uni will remember that I always found timezones a bit confusing! But I've done a few tests, and I think I may have the answer. Have you set your database time zone to a DST aware zone, for example -
alter database set time_zone='London/Europe';

(you have restart the instance afterwards, and you can't do this if your already have a TSLTZ column)If you haven't, I think your database will have picked up the offset from GMT as it's timezone, which will be fixed, not DST aware. At least, that's what I found when I checked:
select property_value from databasE_properties where property_name='DBTIMEZONE';

So when you change the time of both your server and client from winter to summer, you will get the effect you describe. Having set my database to a DST timezone, I get the same values returned no matter what date my client or server are operating in.
I suppose this might be why thre Globalization Guide mentions that it is a good idea to set both OS and DB timezone to GMT.

[Updated on: Sun, 14 February 2010 08:38]

Report message to a moderator

Re: Oracle bug with TimeStamp with Local Time Zone [message #443435 is a reply to message #443264] Mon, 15 February 2010 14:46 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
Thanks John,

It could have been that but I dought because:

select property_value from databasE_properties where property_name='DBTIMEZONE';

returned : 00:00

Note:
I'm not sure but I think "select dbtimezone from dual"
is equivalent and is more user friendly. Both return 00:00.

Thanks for the try. I think I would have answered the exact same thing.
Re: Oracle bug with TimeStamp with Local Time Zone [message #443436 is a reply to message #443435] Mon, 15 February 2010 14:48 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
Thanks Michel,

My last reply was made last thursday feb,11 to metalink.

I'm waiting for Oracle. When I will get the answer, I will write here the content of it.

thanks,
Eric
Re: Oracle bug with TimeStamp with Local Time Zone [message #443440 is a reply to message #443436] Mon, 15 February 2010 15:20 Go to previous messageGo to next message
John Watson
Messages: 6563
Registered: January 2010
Location: Global Village
Senior Member
Hi - that's exactly what I'm suggesting. Your client side operating system (and the server side, if you are running both on one machine?) is timezone aware, but the database isn't. When I set the database TZ to match the operating system TZ, the problem went away. Any good? It will be interesting to see what Support comes up with.
Overall, I think it comes down to what I've always said since 9i: stick with Zulu time everywhen.
Re: Oracle bug with TimeStamp with Local Time Zone [message #443658 is a reply to message #443440] Tue, 16 February 2010 20:49 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
Oracle recommend to keep dbtimezone to 00:00.
I will stick with that. Only if Oracle ask me to do otherwise that I will do it.

What is zulu time ? GMT (Offset zero) ?

It is totally silly. The date is different in winter or in summer.
Data is changing over time.
I wonder what would happen if I use TSLTZ as foreign key?
I think I will try that tomorrow... if I can find some time...
I guess a 10$ I'm ablt to setup an incoherent database !!!

Actually I got an answer from Oracle.

She said it is normal behavior, as described. Oracle will make me krazy!!!
Re: Oracle bug with TimeStamp with Local Time Zone [message #443659 is a reply to message #443658] Tue, 16 February 2010 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>What is zulu time ? GMT (Offset zero) ?
yes & yes
Re: Oracle bug with TimeStamp with Local Time Zone [message #443730 is a reply to message #443658] Wed, 17 February 2010 02:12 Go to previous messageGo to next message
John Watson
Messages: 6563
Registered: January 2010
Location: Global Village
Senior Member
Make sure your server OS is Zulu time too, then you'll be OK. You can't stop your clients from using DST, but I suppose that is one reason for using TSLTZ.
But to revert to a previous part of the discussion, "client" is important: I see this as being worthless in a 3 tier environment, because correcting times to the client (ie, middle tier) timezone is not going to anything for a user whose browser is running on a machine somewhere else.
Re: Oracle bug with TimeStamp with Local Time Zone [message #444250 is a reply to message #443103] Fri, 19 February 2010 11:18 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
Just for your readers information, this is my final comment on my SR because Oracle don't want to admit it is krazy, there implemantation (with data that change over time). I also must admit that I hate Oracle more than anybody else on earth.
Thanks to all of you who tried to help me.

>>>

The note 340512.1 is fantastic. It is very clear and I recommended as reading to 3 of my colleague. They really like it.

In the note 340512.1 mentionned. We can read:

<< Note that the SESSIONTIMEZONE (and NOT the database timezone) is the actual used timezone at both insert and select (if not specified explicit) to calculate the time for the inserted/returned result. >>

This is exactly what you told me and the behavior I had. You are totally right about it. The problem is like when you are in court... you sould say the truth...
but all the truth !!! Smile

To help other customers and save them time. Why don't you just add more information.
I undestand that you don't want to write that using an Oracle database, your data could change over time. It's not a good selling point. Althought it is exaclty what happen.

But it will probably save a lot of headache to anybody using that type if you just add :
"Be aware of the fact that you could read an offset of 1 hour due to DST while reading your data depending on your session offset (if it change in a year due to DST)."

For me, it will not change anything. I know it and I'm starting this afternoon to change all my datatype. But, I would really appreciate to knew this information before choosing this datatype.

As a final note.
I still think that Oracle had a very nice idea by implementing a type that could save UTC and adjust itself according to timezone. It could discharge the programmer from that error prone and dull task.
The problem is that it had been implemented on a totally krazy and lazy manner and you (Oracle) don't want to admit it. The behavior of the current implementation is totally useless and will just put confusion on users.
You can continue to defend your point, it's true it respect the documentation but it has been implemented wrongly.
Oracle should use the offset of the data, not the session. Yes it will be a lot more complicated, have less performance and affect many places in core code. But Oracle must either fix it or add a new subtype that will reflect what users would naturally expect from that datatype.
... And at least write documention very clearly with all the possible impacts.

Thanks!
Re: Oracle bug with TimeStamp with Local Time Zone [message #444254 is a reply to message #444250] Fri, 19 February 2010 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.
It does not surprise me Oracle does not admit it was wrong in any way.

I remember 2 cases that really show it:
- the first one was about resource manager on 9.2, after a couple of months of experimentations where we clearly showed to Oracle that it does not work like it is documented, Oracle closed the SR (which was 60 pages long) with the mention "it is implemented as designed not as documented"!
- the second one was about installing Database Vault on 10.2, it was impossible to install it in our environment. After one night spent with development team (due to 10 hours offset between France and California), we found 6 bugs in the installer (one after the other) and were not able to install it. We realized that we must have a listener named LISTENER, with port 1521, with a temporary tablespace named TEMP, with SYS password containing only alphanumeric characters... After ONE YEAR, Oracle ended the SR with the mention "these are not bugs, these are restrictions to install the product". SR closed because "Not a bug", yeah!

If timezones are really important for you I think you should use TIMESTAMP WITH TIME ZONE (not LOCAL TIME ZONE). This type is DST aware and I don't think you will have a problem with (although this is not proven).

Regards
Michel

Re: Oracle bug with TimeStamp with Local Time Zone [message #444257 is a reply to message #444254] Fri, 19 February 2010 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to my previous post, read the following:
http://blogs.zdnet.com:80/Howlett/?p=1761

Regards
Michel
Re: Oracle bug with TimeStamp with Local Time Zone [message #444561 is a reply to message #444257] Mon, 22 February 2010 10:08 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
Wow !

Thanks you for your comment and the link to the article.
I feel better. I thought I was alone on earth thinking bad things about Oracle support and their engine. I thought I would have been kick it out of this WebSite for talking bad about Oracle (you know, an Oracle FAQ web site, full of Oracle DBAs !!!).
I work in a company where our DBA's only swear for Oracle. I do not have enough experience to say that another one is better but I would really appreciate to have the chance to try something else (SQL Server, DB2 or another pro one). To me, it could hardly be harder to install, maintain, use and interface with a .net app.

Thanks,
Eric
Re: Oracle bug with TimeStamp with Local Time Zone [message #444643 is a reply to message #444561] Tue, 23 February 2010 03:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's the difference between Oracle the database and Oracle the Corporation.

The database is great, but the corporation has some issues, certainly when it comes to addressing problems in their products.
Re: Oracle bug with TimeStamp with Local Time Zone [message #444684 is a reply to message #444643] Tue, 23 February 2010 08:19 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member

Hello All,

I'm not sure at all, but I think Oracle fixed the DST problem on december 2009.

See Article :
Applying the DSTv11 update for the Oracle Database [ID 840118.1]

It sounds like fix their TZLTZ problem. The previous article described everything we should do. But I'm not a DBA and applying that patch seems not very easy. Plus, I'm not sure that patch will fix all their problem properly. It's very deep. Then I will switch all of my TSLTZ fields to TS only. I will loose some advantages but will probably save a lot of problems and mainly I will save many years of my life. Each time I have to write back to Oracle, I loose one month of my like due to all the effect of frustration because they never understand. It always takes between 12 to 36 back and forth before the support seems to understand a portion of what you are saying !!!

Suggestion 1 : Never touch TSLTZ with a 10' pole !
Suggestion 2 : Avoid, to the maximum, the Oracle Support
Suggestion 3 : Save you many years of frustration... avoid Oracle Database, take any other else (but a real one, with a real server)

I will potentially (with some help) try to test the patch mentioned at the top of this message. If I do, I will write here the results of it.

Re: Oracle bug with TimeStamp with Local Time Zone [message #444691 is a reply to message #444643] Tue, 23 February 2010 08:50 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
To JRowbottom,

You are partly right.
I think that Oracle have some great advantages.

But to me thoses disadvantages are to big to choose it (note, that it was imposed to me)

Problems / Disadvantages
- React too slowly
- Implemented SQL 92 compliant in 9g only
- Implemented binaryDouble datatype only since 9g but many of their own tool still do not support this datatype
- Implemented TS only since 9i but many of their own tool still do not support this datatype
- The ODBC driver still do not support TSLTZ datatype
- They just begin to manage the hard disk space for you (yes - - 2010 and still no direct match for Int16, Int32, Int64.
- They just begin to make some tool to analyse and help you fix your DDL or database parameters for you
Still many int weird datatype but what it is in memory and on disk... be sure it is 'bits' in multiple of 8. Oracle was made for Cobol.
you have to know what you want but some should be done automatically)
- They just begin to update their totally stupid TNS way to connect (direct IP)
- Patching system is so odd and complicated. It should be automatic, simple, with a GUI (in fact both way: batch and GUI). A patch always need 2 others patches that need 3 other patches that need 4 more patches that are not included in the initial one.
- The support is the worse on earth
- The web interface of metalink recently was corrected but was a real mess.
- They never admit they have a bug and let you in the mud
- Nobody on earth understand fully what is needed in the registry or in their environment variables (any non english people already add problems with NSL parameters too)
- How many bugs are still in SqlDeveloper and were in previous version (just for the fun... add an accent like 'é' in the path of it, we are in 2010 and they still do not support accent in path 'é' for their tool)
- They do not support 'Ms Windows' 'decimal' datatype. Windows is not everything on earth but it is still the main, althought people don't like it.
- Sometimes, session still hang forever without any reason. You kill them and they stay there.


Oracle have some advantages (performance, update live) but too many flaws.
Re: Oracle bug with TimeStamp with Local Time Zone [message #444761 is a reply to message #444691] Wed, 24 February 2010 00:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not trying in any way to defend Oracle (even though I don't agree with a number of points), but want to dig in to the last point you mention:
Quote:
- Sometimes, session still hang forever without any reason. You kill them and they stay there.

If you have a session that is performing DML for long periods of time, without committing or rollbacking, killing this session forces the DML to be rolled back. That is why it looks as though the kill takes forever.
15+ years of experience in Oracle, and I must say that I have yet to see a session truly hang. Up till now, I have often thought that sessions hung, but in retrospect, there was always a reason for the session not to respond.

Furthermore, complaining in 2010 about features that were added "only in 9i" (released almost 10 years earlier) sounds a bit odd to me.
Re: Oracle bug with TimeStamp with Local Time Zone [message #444898 is a reply to message #444761] Wed, 24 February 2010 12:45 Go to previous messageGo to next message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
I can't be sure 100% that I was not doing DML. But I'm pretty sure of it (in jobs details nothing appears as running statement).

Also, I know that doing DML on a 9i (perhaps 8i) database could even crash the database completely. You need to shutdown and restart. I never found the exact reason.

Talking about 9i features, It took 10 years to become entry level compliant to Sql92 and still... For example (only one on many), Oracle does not support the "Top" in select for the n first records. They only support the awkward "where rownum...". 'Where' is made to applied restiction on records and here it is used to applied restriction on the full select...

You could have silly things like:

select
mesure_detail.CODE_CED,
mesure_detail.sigle,
mesure_detail.no_ligne,
mesure_detail.mesure_id,
mesure_detail.adresse_electrique lclcl,
mesure_detail.status_mesure_description mes_desc,
exception_traitement.date_inscription,
message
from
exception_traitement
inner join srv_calcul_localisation on (exception_traitement.exception_traitement_id = srv_calcul_localisation.exception_traitement_id)
inner join mesure_detail on srv_calcul_localisation.mesure1_id = mesure_detail.mesure_id
where
rownum <= 20
order by
exception_traitement.exception_traitement_id desc

Here the 'where' is fully weird because it apply restriction on the entire select statement (sort included). It is the world upside-down. Inner restriction on outer data. The regular behavior is you restrict then you apply sort.

Oracle is probably one of the better performer but it's everything around it (I mean everything) that make it not a good choice for any type of development.

Also, have you ever read Isolation (database systems)on wikipedia...
http://en.wikipedia.org/wiki/Isolation_(database_systems)
You could find interresting information about how Oracle implemented isolation and are still not compliant Sql99 (in 11g2)... And isolation is the base of a database (part of ACID).

Also, do you realize, that before the patch that occur in december 2009. All the data in TSLTZ stored by ORACLE can't be trusted. The data could be an hour offset of what you see. It depends on when it was entered and if you have no other field that indicated it, then you are dead. You have incoherent data. That is major. I hope it will come out in the news very soon !!!
All this thread is about that !!! Moving data over time in an Oracle Database! A database you can't trust. 7 Years of buggy datatype denial from Oracle !!! And many customers in deep shit!

[Updated on: Wed, 24 February 2010 12:50]

Report message to a moderator

Re: Oracle bug with TimeStamp with Local Time Zone [message #444918 is a reply to message #444898] Wed, 24 February 2010 13:42 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Eric Ouellet wrote on Wed, 24 February 2010 18:45

Talking about 9i features, It took 10 years to become entry level compliant to Sql92 and still... For example (only one on many), Oracle does not support the "Top" in select for the n first records. They only support the awkward "where rownum...". 'Where' is made to applied restiction on records and here it is used to applied restriction on the full select...

You could have silly things like:
select 
  mesure_detail.CODE_CED,
  mesure_detail.sigle,
  mesure_detail.no_ligne,
  mesure_detail.mesure_id,
  mesure_detail.adresse_electrique lclcl,
  mesure_detail.status_mesure_description mes_desc,
  exception_traitement.date_inscription,
  message
from 
  exception_traitement
  inner join srv_calcul_localisation on (exception_traitement.exception_traitement_id = srv_calcul_localisation.exception_traitement_id)
  inner join mesure_detail on srv_calcul_localisation.mesure1_id = mesure_detail.mesure_id
where 
  rownum <= 20
order by
  exception_traitement.exception_traitement_id desc


Here the 'where' is fully weird because it apply restriction on the entire select statement (sort included). It is the world upside-down. Inner restriction on outer data. The regular behavior is you restrict then you apply sort.


Oracle does restrict before it applies the sort. Which is why that example is an invalid top-n. Should be:
select * from (
select 
  mesure_detail.CODE_CED,
  mesure_detail.sigle,
  mesure_detail.no_ligne,
  mesure_detail.mesure_id,
  mesure_detail.adresse_electrique lclcl,
  mesure_detail.status_mesure_description mes_desc,
  exception_traitement.date_inscription,
  message
from 
  exception_traitement
  inner join srv_calcul_localisation on (exception_traitement.exception_traitement_id = srv_calcul_localisation.exception_traitement_id)
  inner join mesure_detail on srv_calcul_localisation.mesure1_id = mesure_detail.mesure_id
order by
  exception_traitement.exception_traitement_id desc)
where 
  rownum <= 20


EDIT: fixed a tag

[Updated on: Wed, 24 February 2010 14:07]

Report message to a moderator

Re: Oracle bug with TimeStamp with Local Time Zone [message #444980 is a reply to message #444898] Thu, 25 February 2010 00:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Of course I don't know your level of knowledge of Oracle, but it looks like you dived in head-first from the SQLServer/Sybase world and expected Oracle to work like "any other database" (i.e. SQLServer/Sybase).
Mind you, I am NOT trying to belittle you or question your professionalism, it is just that I have had many discussions with people like that about the lack of features of Oracle.
(Probably will be the other way around for me as well Smile )

DML hanging the database? Simply said: not very likely.
Isolation: I for one don't really like the idea of a default setting of a database where readers can block readers or even updaters can block readers.
I think it's all a matter of taste, and what you're used to.

Re: Oracle bug with TimeStamp with Local Time Zone [message #445005 is a reply to message #444918] Thu, 25 February 2010 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

Talking about 9i features, It took 10 years to become entry level compliant to Sql92 and still...


I'm not quite sure where you're getting your information from. Oracle 7.0 was Entry Level compliant with SQL92 when it came out in 1992
Re: Oracle bug with TimeStamp with Local Time Zone [message #445009 is a reply to message #444918] Thu, 25 February 2010 03:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

Here the 'where' is fully weird because it apply restriction on the entire select statement (sort included). It is the world upside-down. Inner restriction on outer data. The regular behavior is you restrict then you apply sort.


I'm really not sure what you're trying to say here.
Oracle restricts the selected data first, in this case restricting the rows returned to 20, and then sorts these rows, which is exactly the 'regular behaviour' that you describe.
Re: Oracle bug with TimeStamp with Local Time Zone [message #445074 is a reply to message #445009] Thu, 25 February 2010 10:53 Go to previous message
Eric Ouellet
Messages: 13
Registered: February 2010
Location: Canada / Montreal
Junior Member
Thanks CookieMonster and JRowBottom,

You are right. I corrected my request (in fact it was a View created for some people I worked with to help them debug).
Thanks a lot, you saved me a great time of debugging. I realized that something went wrong but was not able to point it.
In some sense, it is more logical, that the "where" applied to restrict records and sort apply after. But...
In some sense, you prove how it is awkward... You have to do 2 selects in order to achieve a top request. Top should be only one request.

According to Oracle, only Oracle 8i was first entry level compliant to Sql92 :
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/ap_stand.htm.
The problem is that 8i did not support "join" in the "From" clause. What was far from being standard at all.
I don't know what was the entry level but it has very low standards.
To me, a "from is a from" and a "where is a "where". A database should enable people to use "join" in "from".



To Frank (and also to CookieMonster and JRowBottom)

Yes you are right. I'm quick, sometimes like now, a little bit too much.
I feel a little bit shy and sorry for it. I think my frustration about Oracle probably does not help very much !!!


Thanks to all


Previous Topic: Alternative to SubQuery
Next Topic: UTL_SMTP Encoding Issue
Goto Forum:
  


Current Time: Fri Dec 02 18:19:59 CST 2016

Total time taken to generate the page: 0.38022 seconds