I am running a 9.0.1 database on a W2K server and have come across
some strange behaviour with a SQL query.
I have a query which runs in a PL/SQL cursor which has several PL/SQL
variables used to switch on and off certain rules. One idea I had was
to have two queries UNIONed together with a simple switch selecting
which half was to operate (I know it sounds like there are probably
better ways of doing this but I have my reasons).
To cut a long story short (too late, I hear you cry?), adding "AND
0=1" to a query will only *sometimes* be included in the rules. If you
will excuse the long post, below is a simplified example which can be
copied & pasted into SQL*Plus to demonstrate the issue.
Why does it behave this way, as I am sure it did not with oracle 8.0.5
(I used to use this approach to get really slow queries short-cut when
analysing execution paths)?
If you change the "0=1" to "ID=ID+1" it works as expected, but takes
longer (normally, short-cutting a query takes no time, which is why I
was not concerned about using a UNIONed query).
----------------------------
-- Same thing happens with non-temporary tables
create global temporary table play
(
id number(12) not null,
name varchar2(30) not null,
dob date
)
on commit delete rows;
INSERT INTO play
(id, NAME, dob)
VALUES (1, 'Bob', TO_DATE ('01-01-1971','dd-mm-yyyy'));
INSERT INTO play
(id, NAME, dob)
VALUES (1, 'Ben', TO_DATE ('02-03-1974','dd-mm-yyyy'));
-- A simple example - it works as expected
SELECT id, NAME, TO_CHAR (dob, 'DD-MON-YYYY HH24:MI:SS')
FROM play
WHERE id < 10
AND ('%e%' IS NULL OR LOWER(Name) LIKE LOWER('%e%'))
AND ('' IS NULL OR dob >= TO_DATE('', 'DD-MON-YYYY HH24:MI:SS'));
-- (Remember that '' is identical to NULL to oracle)
-- As above, but with "0=1" as the first part of the restrictions.
-- This also works as expected.
SELECT id, NAME, TO_CHAR (dob, 'DD-MON-YYYY HH24:MI:SS')
FROM play
WHERE 0=1
AND id < 10
AND ('%e%' IS NULL OR LOWER(Name) LIKE LOWER('%e%'))
AND ('' IS NULL OR dob >= TO_DATE('', 'DD-MON-YYYY HH24:MI:SS'));
-- As above, but with the "0=1" moved to the end of the restrictions.
-- In this case it does NOT work as expected.
SELECT id, NAME, TO_CHAR (dob, 'DD-MON-YYYY HH24:MI:SS')
FROM play
WHERE id < 10
AND ('%e%' IS NULL OR LOWER(Name) LIKE LOWER('%e%'))
AND ('' IS NULL OR dob >= TO_DATE('', 'DD-MON-YYYY HH24:MI:SS'))
AND 0=1;
----------------------------
Is this a bug? I think we should be told!
ETA 0 2692 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Alex Mizrahi |
last post by:
Hello, All!
i admit that it's better to ask questions connected with atl/mfc classes in
special newsgroups, but seems like people there are interested more in
discussing stuff like MFC GUI than C++ 8-/, so i'll better ask here too..
CStringW is a class in atl/mfc dealing with strings 8-]. the only typecast
operator i found is to PCXSTR...
|
by: Mark Barinstein |
last post by:
Hello.
W2K, db2 v7, fp11.
Given:
create table pays (
acode integer not null,
packno smallint not null,
sum decimal(15, 2) not null
|
by: Mal |
last post by:
Greetings.
I have a perplexing problem....please help.
I am having a problem with an insert query. (SQL below)
When I run the query via code (executing the SQL string) it crashes
Access and gives me a Page Fault error in OLEAUT32.dll
When I run the query from the query window it gives the normal "You
are about to run an action...
|
by: Sebastian C. |
last post by:
Hello everybody
Since I upgraded my Office XP Professional to SP3 I got strange
behaviour.
Pieces of code which works for 3 years now are suddenly stop to work
properly.
I have Office XP Developer (SP3 for Office, SP1 for developer,
JET40SP8) on Windows XP Home Edition (SP1). The same behaviour occurs
on Windows 98 too.
|
by: bear |
last post by:
hi all,
I have a program whose speed is so strange to me. It is maily used
to
calculate a output image so from four images s0,s1,s2,s3 where
so=(s0-s2)^2+ (s1-s3)^2. I compile it with gcc (no optimization). the
codec between /***********/ is the initialization code. What supprise
me a lot
is the code with initialization(io==1) is much...
| |
by: gamehack |
last post by:
Hi all,
I've been testing out a small function and surprisingly it does not
work okay. Here's the full code listing:
#include "stdlib.h"
#include "stdio.h"
char* escaped_byte_cstr_ref(char byte);
int main (int argc, const char * argv)
|
by: Bo Yang |
last post by:
Following is my code:
include <iostream>
class Test{
public:
Test(){};
void print(){ std::cout << "OK" << std::endl ; };
};
|
by: FBM |
last post by:
Hi there,
I am puzzled with the behavior of my code.. I am working on a
networking stuff, and debugging with eclipse (GNU gdb 6.6-debian)..
The problem I am experiencing is the following:
Whenever I declare the sockaddr_in structure inside the main, the
debugger crashes at line X*, not being able to access argv parameters
(see code...
|
by: Jan Obrestad |
last post by:
Hello,
I've been using the XmlReader class to read xml files.
Lately it has had some strange quirks. It seems to ignore elements when
there is no whitespace between them.
ex
<main><textline><textelement>A</textelement>
<textelement>B</textelement<textelement>C</textelement></textline></main>
(really on one line)
|
by: Johnny Jörgensen |
last post by:
I've got a process I want to run in a thread separate from my main
application thread, so I've used a backgroundworker component, and in
frmMain.Load I invoke the code using Backgroundworker1.RunWorkerAsync().
So far so good, this works fine. But when the process is done, I want to
wait 30 minutes and then run the process again. So I added a...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |