Thread: Why is this allowed?
<div class="Section1"><p class="MsoNormal">Why don’t we have some kind of error check for people entering things like INTERVAL‘1’ DAY in their query, since we don’t handle it.<p class="MsoNormal"> <p class="MsoNormal">select now() =now() +interval '1' day;<p class="MsoNormal"> ?column?<p class="MsoNormal">----------<p class="MsoNormal"> T<p class="MsoNormal"> <pclass="MsoNormal">This seems scary… We allow something through and then ignore it?<p class="MsoNormal"> <pclass="MsoNormal">“Interval ‘1 day’” or “interval ‘1 day’ day “ both work. </div>
"Chuck McDevitt" <cmcdevitt@greenplum.com> writes: > Why don't we have some kind of error check for people entering things > like INTERVAL '1' DAY in their query, since we don't handle it. Because it's not an error. It just doesn't mean what you think it means. You've requested an interval measured in days and supplied '1' as the value which is read as a single second. Hm, the documentation doesn't describe this syntax but the comments in the source imply that this postfix precision notation is required and there's quite a bit of code in gram.y to support it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
"Gregory Stark" <stark@enterprisedb.com> writes: > "Chuck McDevitt" <cmcdevitt@greenplum.com> writes: > >> Why don't we have some kind of error check for people entering things >> like INTERVAL '1' DAY in their query, since we don't handle it. > > Because it's not an error. It just doesn't mean what you think it means. > > You've requested an interval measured in days and supplied '1' as the value > which is read as a single second. Actually sorry, that's true but it seems it's a bug. The comments even use the example INTERVAL '1' YEAR which one imagines the author didn't intend to be parsed as a 1 second interval measured in years. Looking more at it now. There are a couple different grammar productions that look like they might be relevant, I'm not sure which is getting used here. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.com> writes:
> "Chuck McDevitt" <cmcdevitt@greenplum.com> writes:
>> Why don't we have some kind of error check for people entering things
>> like INTERVAL '1' DAY in their query, since we don't handle it.
> Because it's not an error. It just doesn't mean what you think it means.
What it is is an incompletely implemented feature. That syntax is
required by the SQL spec, and Tom Lockhart had been making progress
towards supporting it when he got bored and left the project. Where
he left it is that the grammar accepts it but the interval input routine
isn't paying any attention to the qualifier.
I'm not eager to rip out what's there, but I don't personally feel
like making it work either...
regards, tom lane
Ok...
Just to be clear, the ISO SQL spec says that INTERVAL '1' DAY is the
correct way to specify a one-day interval.
That's why it is surprising that PostgreSQL treats it differently, with
no error or warning.
The PostgreSQL syntax INTERVAL '1 DAY' is non-standard.
Is fixing this on the TODO list?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, March 10, 2007 5:11 PM
To: Gregory Stark
Cc: Chuck McDevitt; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Why is this allowed?
Gregory Stark <stark@enterprisedb.com> writes:
> "Chuck McDevitt" <cmcdevitt@greenplum.com> writes:
>> Why don't we have some kind of error check for people entering things
>> like INTERVAL '1' DAY in their query, since we don't handle it.
> Because it's not an error. It just doesn't mean what you think it
means.
What it is is an incompletely implemented feature. That syntax is
required by the SQL spec, and Tom Lockhart had been making progress
towards supporting it when he got bored and left the project. Where
he left it is that the grammar accepts it but the interval input routine
isn't paying any attention to the qualifier.
I'm not eager to rip out what's there, but I don't personally feel
like making it work either...
regards, tom lane
"Chuck McDevitt" <cmcdevitt@greenplum.com> writes:
> Is fixing this on the TODO list?
See the 'Add ISO INTERVAL handling' entry.
regards, tom lane
On Sat, 10 Mar 2007, Chuck McDevitt wrote: > Ok... > > Just to be clear, the ISO SQL spec says that INTERVAL '1' DAY is the > correct way to specify a one-day interval. > That's why it is surprising that PostgreSQL treats it differently, with > no error or warning. > > The PostgreSQL syntax INTERVAL '1 DAY' is non-standard. > > Is fixing this on the TODO list? Yes: Add ISO INTERVAL handling There's detail there about the nature of support required. Personally, I think the syntax is awful. I don't understand why intervals are handled so differently in the spec as compared to other data types. This syntax is well supported by Oracle and DB2 though. I guess it should be completed in postgres. Thanks, Gavin