[Info-Ingres] PARTITION - LIST

#1

Hi all,

Where I'am wrong when I cust and paste this example from the SQL doc :
CREATE TABLE table1 (
i INTEGER NOT NULL WITH DEFAULT,
str VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (LIST ON i,str
PARTITION p1 VALUES (1,'one')
WITH LOCATION=(ii_database),
PARTITION p2 VALUES ((2,'two'),(3,'three'))
WITH LOCATION=(loc1, loc2),
PARTITION p3 VALUES DEFAULT);

it give me the following error :

[CREATE - 0 row(s), 0.125 secs] [Error Code: 6457, SQL State: 42000]
CREATE TABLE: Wrong number of value components in partitioning constant.
Expected 2 components, saw 1; error near '1'.
.... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.125/0.000
sec [0 successful, 0 warnings, 1 errors]

Version is : II 9.2.0 (int.w32/109)

Thx
--
Jean-Pierre Zuate
La Fage Conseil
+33(0)6 11 40 11 09
jean-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/

#2

Jean-Pierre,

Are you sure that you pasted this from the SQL manual? The 9.2 SQL
Reference Guide I am looking at has some extra parentheses. Try this:

CREATE TABLE table1 (
i INTEGER NOT NULL WITH DEFAULT,
str VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (LIST ON i,str
PARTITION p1 VALUES ((1,'one'))
WITH LOCATION=(ii_database),
PARTITION p2 VALUES ((2,'two'),(3,'three'))
WITH LOCATION=(loc1, loc2),
PARTITION p3 VALUES (DEFAULT));

Note there is an extra set of parentheses around the (1,'one'). You are
also missing the closing parenthesis after the (DEFAULT),

John

________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Jean-Pierre Zuate, La Fage Conseil
Sent: Friday, 3 April 2009 11:27 AM
To: Ingres and related product discussion forum
Subject: [Info-Ingres] PARTITION - LIST

Hi all,

Where I'am wrong when I cust and paste this example from the SQL doc :
CREATE TABLE table1 (
i INTEGER NOT NULL WITH DEFAULT,
str VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (LIST ON i,str
PARTITION p1 VALUES (1,'one')
WITH LOCATION=(ii_database),
PARTITION p2 VALUES ((2,'two'),(3,'three'))
WITH LOCATION=(loc1, loc2),
PARTITION p3 VALUES DEFAULT);

it give me the following error :

[CREATE - 0 row(s), 0.125 secs] [Error Code: 6457, SQL State: 42000]
CREATE TABLE: Wrong number of value components in partitioning constant.

Expected 2 components, saw 1; error near '1'.
.... 1 statement(s) executed, 0 row(s) affected, exec/fetch time:
0.125/0.000 sec [0 successful, 0 warnings, 1 errors]

Version is : II 9.2.0 (int.w32/109)

Thx
--
Jean-Pierre Zuate
La Fage Conseil
+33(0)6 11 40 11 09
jean-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/

#3

Jean-Pierre Zuate, La Fage Conseil wrote:

Quote:

Hi all,

Where I'am wrong when I cust and paste this example from the SQL doc :
CREATE TABLE table1 (
i INTEGER NOT NULL WITH DEFAULT,
str VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (LIST ON i,str
PARTITION p1 VALUES (1,'one')
WITH LOCATION=(ii_database),
PARTITION p2 VALUES ((2,'two'),(3,'three'))
WITH LOCATION=(loc1, loc2),
PARTITION p3 VALUES DEFAULT);

it give me the following error :

[CREATE - 0 row(s), 0.125 secs] [Error Code: 6457, SQL State: 42000]
CREATE TABLE: Wrong number of value components in partitioning constant.
Expected 2 components, saw 1; error near '1'.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.125/0.000
sec [0 successful, 0 warnings, 1 errors]

John Dennis already pointed out the problem here, but what you've
probably noticed is that the syntax for describing a partitioning
scheme is very elborate which makes it easy to make that kind of
mistake.

You will find a diagram of the Ingres partitioning syntax at
http://www.rationalcommerce.com/uplo...tition-ref.pdf which may
help.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

#4

Wow ! big headache in perspective

)

Thx Roy !

2009/4/3 Roy Hann <specially (AT) processed (DOT) almost.meat>

Quote:

Jean-Pierre Zuate, La Fage Conseil wrote:

Hi all,

Where I'am wrong when I cust and paste this example from the SQL doc :
CREATE TABLE table1 (
i INTEGER NOT NULL WITH DEFAULT,
str VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (LIST ON i,str
PARTITION p1 VALUES (1,'one')
WITH LOCATION=(ii_database),
PARTITION p2 VALUES ((2,'two'),(3,'three'))
WITH LOCATION=(loc1, loc2),
PARTITION p3 VALUES DEFAULT);

it give me the following error :

[CREATE - 0 row(s), 0.125 secs] [Error Code: 6457, SQL State: 42000]
CREATE TABLE: Wrong number of value components in partitioning constant.
Expected 2 components, saw 1; error near '1'.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time:
0.125/0.000
sec [0 successful, 0 warnings, 1 errors]

John Dennis already pointed out the problem here, but what you've
probably noticed is that the syntax for describing a partitioning
scheme is very elborate which makes it easy to make that kind of
mistake.

You will find a diagram of the Ingres partitioning syntax at
http://www.rationalcommerce.com/uplo...tition-ref.pdf which may
help.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

--
Jean-Pierre Zuate
La Fage Conseil
+33(0)6 11 40 11 09
jean-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/

#5

I have checked this syntax error against the 9.2 documentation and the
SQL is correct

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Jean-Pierre Zuate, La Fage Conseil
Sent: 03 April 2009 07:40
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] PARTITION - LIST

Wow ! big headache in perspective

)

Thx Roy !

2009/4/3 Roy Hann <specially (AT) processed (DOT) almost.meat>

Jean-Pierre Zuate, La Fage Conseil wrote:

Quote:

Hi all,

Where I'am wrong when I cust and paste this example from the SQL doc :
CREATE TABLE table1 (
i INTEGER NOT NULL WITH DEFAULT,
str VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (LIST ON i,str
PARTITION p1 VALUES (1,'one')
WITH LOCATION=(ii_database),
PARTITION p2 VALUES ((2,'two'),(3,'three'))
WITH LOCATION=(loc1, loc2),
PARTITION p3 VALUES DEFAULT);

it give me the following error :

[CREATE - 0 row(s), 0.125 secs] [Error Code: 6457, SQL State: 42000]
CREATE TABLE: Wrong number of value components in partitioning
constant.
Expected 2 components, saw 1; error near '1'.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time:
0.125/0.000
sec [0 successful, 0 warnings, 1 errors]

John Dennis already pointed out the problem here, but what you've
probably noticed is that the syntax for describing a partitioning
scheme is very elborate which makes it easy to make that kind of
mistake.

You will find a diagram of the Ingres partitioning syntax at
http://www.rationalcommerce.com/uplo...tition-ref.pdf which may
help.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9,
2009
Go to http://www.iua.org.uk/join to get on the mailing list.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

--
Jean-Pierre Zuate
La Fage Conseil
+33(0)6 11 40 11 09
jean-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/

#6

John Smedley wrote:

Quote:

I have checked this syntax error against the 9.2 documentation and the
SQL is correct

The rejected SQL may be consistent with the documentation but the server
doesn't like it. I dimly recall this was exactly the reason I created
my diagram. I also dimly recall mentioning to Karl at the time that the
documentation was at least ambiguous if not incorrect. (Karl expressed
deep concern I seem to remember, or something he clearly intended to be
mistaken for concern anyway.)

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

#7

Quote:

John Smedley wrote:

I have checked this syntax error against the 9.2 documentation and the
SQL is correct

The rejected SQL may be consistent with the documentation but the server
doesn't like it. I dimly recall this was exactly the reason I created
my diagram. I also dimly recall mentioning to Karl at the time that the
documentation was at least ambiguous if not incorrect. (Karl expressed
deep concern I seem to remember, or something he clearly intended to be
mistaken for concern anyway.)

I do recall Roy grumbling about this at one point. I'm away from my
email archives, but I do believe that I intended real concern. :-)

The syntax needs the outer parens to delimit the list, and the inner
parens to group each value. It's been 5 years since I did the parser
syntax, so I might be wrong, but I'm pretty sure that yacc would piss
and moan about any attempt to produce syntax rules which would parse
the composite-value form without the extra parens. (I'm sure you
could do it with a different parser generator, or a hand coded parser,
but yacc-generated parsers are not real good with relaxed syntax.)

I wouldn't want to be definitive until I get back home, but I think
it's the doc that is wrong this time.

Karl

#8

Sorry my English is not so good - what I mean is that I have been to the
Ingres 9.2 SQL manual and "CREATE TABLE example 16" reads:

CREATE TABLE table1 (
i INTEGER NOT NULL WITH DEFAULT,
str VARCHAR(10) NOT NULL WITH DEFAULT
) WITH PARTITION = (LIST ON i, str
PARTITION p1 VALUES ((1,'one'))
WITH LOCATION=(ii_database),
PARTITION p2 VALUES ((2,'two'),(3,'three'))
WITH LOCATION=(loc1, loc2),
PARTITION p3 VALUES (DEFAULT));

And this SQL works against Ingres Microsoft Windows Version II 9.2.0
(int.w32/143).

John

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Betty and Karl Schendel
Sent: 03 April 2009 15:34
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] PARTITION - LIST

Quote:

John Smedley wrote:

I have checked this syntax error against the 9.2 documentation and
the
SQL is correct

The rejected SQL may be consistent with the documentation but the
server
doesn't like it. I dimly recall this was exactly the reason I created
my diagram. I also dimly recall mentioning to Karl at the time that
the
documentation was at least ambiguous if not incorrect. (Karl
expressed
deep concern I seem to remember, or something he clearly intended to
be
mistaken for concern anyway.)

I do recall Roy grumbling about this at one point. I'm away from my
email archives, but I do believe that I intended real concern. :-)

The syntax needs the outer parens to delimit the list, and the inner
parens to group each value. It's been 5 years since I did the parser
syntax, so I might be wrong, but I'm pretty sure that yacc would piss
and moan about any attempt to produce syntax rules which would parse
the composite-value form without the extra parens. (I'm sure you
could do it with a different parser generator, or a hand coded parser,
but yacc-generated parsers are not real good with relaxed syntax.)

I wouldn't want to be definitive until I get back home, but I think
it's the doc that is wrong this time.

Karl

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

#9

Hello Jean-Pierre,

The page you have attached is for "Ingres(r) 2006 Release 2", which is
Ingres 9.1.2. I suspect this was fixed as documentation bug between
9.1.2 and 9.2, as in the current 9.2 version of that manual the example
SQL has been corrected.

John

________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Jean-Pierre Zuate, La Fage Conseil
Sent: Saturday, 4 April 2009 3:50 AM
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] PARTITION - LIST

Mr Smedley,

My english is worth than your

And the example I have in the doc I install with the product have the
bad syntax (see extract of first page and the example page of CREATE
TABLE).

Regards,
John-Peter

)

2009/4/3 John Smedley <John.Smedley (AT) ingres (DOT) com>

Sorry my English is not so good - what I mean is that I have
been to the
Ingres 9.2 SQL manual and "CREATE TABLE example 16" reads:

CREATE TABLE table1 (
i INTEGER NOT NULL WITH DEFAULT,
str VARCHAR(10) NOT NULL WITH DEFAULT,

) WITH PARTITION = (LIST ON i, str
PARTITION p1 VALUES ((1,'one'))
WITH LOCATION=(ii_database),
PARTITION p2 VALUES ((2,'two'),(3,'three'))
WITH LOCATION=(loc1, loc2),

PARTITION p3 VALUES (DEFAULT));

And this SQL works against Ingres Microsoft Windows Version II
9.2.0
(int.w32/143).

John

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf
Of

Betty and Karl Schendel
Sent: 03 April 2009 15:34
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] PARTITION - LIST

Quote:

John Smedley wrote:

I have checked this syntax error against the 9.2
documentation and

the

Quote:

SQL is correct

The rejected SQL may be consistent with the documentation but
the

server

Quote:

doesn't like it. I dimly recall this was exactly the reason I
created
my diagram. I also dimly recall mentioning to Karl at the
time that

the

Quote:

documentation was at least ambiguous if not incorrect. (Karl
expressed
deep concern I seem to remember, or something he clearly
intended to

be

Quote:

mistaken for concern anyway.)

I do recall Roy grumbling about this at one point. I'm away
from my
email archives, but I do believe that I intended real concern.
:-)

The syntax needs the outer parens to delimit the list, and the
inner
parens to group each value. It's been 5 years since I did the
parser
syntax, so I might be wrong, but I'm pretty sure that yacc would
piss
and moan about any attempt to produce syntax rules which would
parse
the composite-value form without the extra parens. (I'm sure
you
could do it with a different parser generator, or a hand coded
parser,
but yacc-generated parsers are not real good with relaxed
syntax.)

I wouldn't want to be definitive until I get back home, but I
think
it's the doc that is wrong this time.

Karl

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com

http://www.kettleriverconsulting.com...fo/info-ingres

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com

http://www.kettleriverconsulting.com...fo/info-ingres

--
Jean-Pierre Zuate
La Fage Conseil
+33(0)6 11 40 11 09
jean-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[Info-Ingres] PARTITION - LIST

comp.databases.ingres comp.databases.ingres

[Info-Ingres] PARTITION - LIST - 04-02-2009 , 07:26 PM

Re: [Info-Ingres] PARTITION - LIST - 04-02-2009 , 07:46 PM

Re: [Info-Ingres] PARTITION - LIST - 04-03-2009 , 01:31 AM

Re: [Info-Ingres] PARTITION - LIST - 04-03-2009 , 01:40 AM

Re: [Info-Ingres] PARTITION - LIST - 04-03-2009 , 02:37 AM

Re: [Info-Ingres] PARTITION - LIST - 04-03-2009 , 03:16 AM

Re: [Info-Ingres] PARTITION - LIST - 04-03-2009 , 09:33 AM

Re: [Info-Ingres] PARTITION - LIST - 04-03-2009 , 11:17 AM

Re: [Info-Ingres] PARTITION - LIST - 04-05-2009 , 05:12 PM