Latest topics for the forum "GridSQL - Developers"

how to build and run the gridsql source code

cyj10727 — GMT

I just read the readme.txt . In the text it just say that " The simplest way to do this is to copy apps/gridsql/src/config/server/gridsql.config to another directory like the root project directory, modify the properties of the file appropriately depending on your environment, and set the project properties in your IDE to use the directory where this file is as the execution directory. " what does this mean "depending on your environment " ? Is there anyone give me some examples .I am a novice about gridsql and there is so little document about this

Form application

OUOBA_MARTIAL — GMT

If postgres have a tools like "Oracle form builder" to developpe application :?:

SQL_TRACE in EDB

abuabdullah — GMT

Hi,

What is equvilent of Oracle's SQL_TRACE in EDB

Postgres can also work as a multi instance database like Oracle RAC

mostalika — GMT

Hi Folks,

I would like to know can Postgres supports migration of Oracle RAC mutli instance database to Postgres.
Is there any multi instance database feature supported by Postgres similar to Oracle RAC database, it is
different from Single instance database.

Thanks
-Ravi

Vacuum db in gridsql

Pavani_Mallampati_2 — GMT

Hi,

I am using

postgres=# PostgreSQL 8.3.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)
GridSQL -> 1.1.0.9

Could you please tell me that we have any chance to run the vacuumdb on gridsql. If possible please tell me.Our database is having 14 nodes with near 1800 GB of data init.
Vacuumdb in one node is taking more than 3 hours. And could u please tell me that which procedure of vacuum is good for db either node to node vacuum or directly at grid.

Kindly reply me..

Regards
Pavani

CVS Check-In: Fixed bug with aliases in subquery handling

Andrei_M — GMT

Bug report:
http://forums.enterprisedb.com/posts/list/2525.page#9496

Files:
Checking in src/com/edb/gridsql/queryproc/QueryProcessor.java;
/cvsroot/gridsql/apps/gridsql/src/com/edb/gridsql/queryproc/QueryProcessor.java,v <-- QueryProcessor.java
new revision: 1.23; previous revision: 1.22
done

Add new nodes(from new server) to the Gridsql database

Pavani_Mallampati_2 — GMT

Hi,

We are using postgresplus standard server with default grid version available in it as the details are shown below.

[b]Postgres version[/b]

postgres=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)
(1 row)

[b]Grid version[/b]

GridSQL -> select version();
+---------+
| version |
+---------+
| 1.1.0.9 |
+---------+
1 row(s).

Now, in grid I have the database running on 14 nodes with 1 coordinator and 2 agents as shown below.

coordinator nodes: 1,2,3,4,5
agent1 nodes: 6,7,8,9,10
agent2 nodes: 11,12,13,14

GridSQL -> show databases;
+-----------------------------------------------------------+
| DATABASE | STATUS | NODES |
+-----------------------------------------------------------+
| testdb | Started | 1,2,3,4,5,6,7,8,9,10,11,12,13,14 |
+-----------------------------------------------------------+
3 row(s).

[quote]Now, i need to add new nodes from the different server to the above currently existing database(testdb) , the database have many tables with huge amount of data.
Please help me to add the nodes.
[/quote]

Thanks in advance.

Pavani

can not run more than one gs-loader.sh in one mathine?

harry_zhang — GMT

can not run more than one gs-loader.sh in one mathine?

hello, :)

i want to run muti gs-loader.sh on one mathine?
but when i run the second gs-load.sh ，there some error message。。。

[root@KVMHost-00237d54fa0a data]# gs-loader.sh
Error occurred during initialization of VM
Could not reserve enough space for the card marking array
[root@KVMHost-00237d54fa0a data]#

how can i do?

thank you ! :lol: :lol:

gs-createdb.sh problem

amir_hoshangi — GMT

hi
ive configured my gridSql according to its documentations. i have two nodes , node one(IP = 192.168.1.3) and node two(192.168.1.2) , according to the documentation i checked following command from nodes one postgresql :
createdb -h 192.168.1.2 -p 5433 -U postgres -O postgres xxxx
and it worked successfully , so my connection and other things are ok, but while running this command from gridSql
gs-createdb.sh -d xxxx -u postgres -p mypass -n 1,2
i got exception . it created xxxx db in my localhost db but when it wants to create db in node two (192.168.1.2) it fails due to :
ERROR: Failed to execute command "createdb -h 192.168.1.2 -p 5433 -U postgres -O postgres hahaN2": exit code was 1

and in my console.log i got :

[code]2010-11-30 19:37:09,334 - ERROR Catching throwable:
java.io.IOException: Failed to execute command "createdb -h 192.168.1.2 -p 5433 -U postgres -O postgres xxxx": exit code was 1
at com.edb.gridsql.util.DbGateway.executeList(Unknown Source)
at com.edb.gridsql.util.DbGateway.executeTemplateOnNodes(Unknown Source)
at com.edb.gridsql.util.DbGateway.createDbOnNodes(Unknown Source)
at com.edb.gridsql.parser.SqlCreateDatabase.execute(Unknown Source)
at com.edb.gridsql.engine.ExecutableRequest.execute(Unknown Source)
at com.edb.gridsql.engine.XDBSessionContext.executeRequest(Unknown Source)
at com.edb.gridsql.engine.ServerStatement.execute(Unknown Source)
at com.edb.gridsql.engine.ServerStatement.describe(Unknown Source)
at com.edb.gridsql.engine.XDBSessionContext.describeStatement(Unknown Source)
at com.edb.gridsql.protocol.PgProtocolSession.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
2010-11-30 19:37:09,366 - ERROR Catching throwable:
java.io.IOException: Connection is broken
at com.edb.gridsql.protocol.PgProtocolSession.readRequest(Unknown Source)
at com.edb.gridsql.protocol.PgProtocolSession.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)

where to report GridSql documentation Bugs ?

amir_hoshangi — GMT

hi
there is GridSQLAdminGuide.PDF file in gridSql doc directory.
at the end of the document at:
9.1 Appendix A – Metadata Database Schema
there is problem on one the sql statements and thats :

create table xsystables
...
[b][color=red]parented[/color][/b] int,
...

as you see its wrong and it must be parentid
its very critical especially when someone new wants to start the server and ...
regards

the Join methods for partitioned tables???

harry_zhang — GMT

the Join methods for partitioned tables???

Several join methods are available for DB2 partitioned database environments,
including: collocated joins, broadcast outer-table joins, directed outer-table joins,
directed inner-table and outer-table joins, broadcast inner-table joins, and directed
inner-table joins.

could anybody can tell me what Join methods for partitioned table in gridsql ??

thank you ! :D :D :D

How to get the detail log information of GridSQL?

Cao_Junliang — GMT

I get the source of gridsql from website. And it runs correctly. I want to get the detail log, can anyone help me? thx.

such as execute step info, query plan, execute plan.

could i create a functional index in gridsql ?

harry_zhang — GMT

could i create a functional index in gridsql ?

GridSQL -> create table a1 (id int,uname varchar(10));
OK

GridSQL -> create index uname_a1 on a1(uname);
OK

GridSQL -> drop index uname_a1;
OK

GridSQL -> create index uname_a1 on a1(substring(uname,1,10));
SQLException: ERROR: Encountered "(" at line 1, column 38.
Was expecting one of:
"BINARY" ...
"PASSWORD" ...
"CLUSTER" ...
"ASC" ...
"DESC" ...
"AVG" ...
"SQRT" ...
"COUNT" ...
"EXTRACT" ...
"MAX" ...
"CURRENT_DATE" ...
"CURRENT_TIME" ...
"CURRENT_TIMESTAMP" ...
"TRUNC" ...
"TRUNCATE" ...
"CURRENT_USER" ...
"USER" ...
"YEAR" ...
"LENGTH" ...
"LOWER" ...
"MONTH" ...
"HOUR" ...
"MINUTE" ...
"SECOND" ...
"SUBSTRING" ...
"NOW...

GridSQL -> create index uname_a1 on a1 using (substring(uname,1,10));
SQLException: ERROR: Encountered "(" at line 1, column 35.
Was expecting one of:
"BINARY" ...
"PASSWORD" ...
"CLUSTER" ...
"AVG" ...
"SQRT" ...
"COUNT" ...
"EXTRACT" ...
"MAX" ...
"CURRENT_DATE" ...
"CURRENT_TIME" ...
"CURRENT_TIMESTAMP" ...
"TRUNC" ...
"TRUNCATE" ...
"CURRENT_USER" ...
"USER" ...
"YEAR" ...
"LENGTH" ...
"LOWER" ...
"MONTH" ...
"HOUR" ...
"MINUTE" ...
"SECOND" ...
"SUBSTRING" ...
"NOW" ...
"ADDDATE" ...
"...

GridSQL ->

^_^

All aggregate function are not working

pavani_mallampati — GMT

Hi,

We have 1 coordinator and 2 agents previously now we are trying to configure one more agent to the same coordinator and we have configured successfully

with the agents 15 16 17 18 and 19
and then

we have created one database on all the nodes(19) it is successfully created.

we have created some tables on that database on all nodes (19) they are successfully created.

we have inserted the records into the tables they to successfully inserted.

But while selecting the data from table then

execute direct on all 'select count(*) from emp'; is working fine.

But when i fire the [b]select count(*) from emp [/b] then it was throwing an error on newly created agent's nodes like

SQLException: ERROR: Failed To Get Results For ( SQL , NodeURL) : ( SELECT
count(*) AS "XCOL1" FROM "captor_tdr_tapin" ) eQS Node 15 has aborted execution,
cause is: com.edb.gridsql.exception.XDBServerException : Can not send data to
Nodes

If next time i find the same query then it is throwing error on another node on same server.. like

SQLException: ERROR: Failed To Get Results For ( SQL , NodeURL) : ( SELECT
count(*) AS "XCOL1" FROM "captor_tdr_tapin" ) eQS Node 19 has aborted execution,
cause is: com.edb.gridsql.exception.XDBServerException : Can not send data to
Nodes

If next time i find the same query then it is throwing error on another node on same server.. like

SQLException: ERROR: Failed To Get Results For ( SQL , NodeURL) : ( SELECT
count(*) AS "XCOL1" FROM "captor_tdr_tapin" ) eQS Node 18 has aborted execution,
cause is: com.edb.gridsql.exception.XDBServerException : Can not send data to
Nodes

etc.

Please provide the necessary solution ..

Thanks in advance..

Regards
Pavani

Selecting the data from the base table having inheritance with the child tables..

pavani_mallampati — GMT

Hi,

We have a [b]base table[/b] [b][i]test[/i] [/b]having [b]child tables[/b] for each and every date of this month like [b][i]test_011010 ,test_021010[/i][/b]...... and [b]having data[/b] in it..

When i am selecting the data from the child table of a particular date then it is showing the record count but while i was selecting the data from the base table then it was throwing an error message..

For the details please go through the below statements...

GridSQL -> select count(*) from test_011010;
+----------+
| count(*) |
+----------+
| 4459939 |
+----------+
1 row(s).
Response time: 0m 4s 557ms Total time: 0m 4s 557ms

GridSQL -> select count(*) from test_011010 where start_date='2010-10-01';
+----------+
| count(*) |
+----------+
| 4459939 |
+----------+
1 row(s).

[b]GridSQL -> select count(*) from test where start_date='2010-10-01';[/b]

[quote]SQLException: ERROR: Failed To Get Results For ( SQL , NodeURL) : ( SELECT count(*) AS "XCOL1" FROM "test" WHERE ("test"."start_date" = '2010-10-01')) eQS Node 9 has aborted execution, cause is: java.sql.SQLException : ERROR: could not open relation 3586782/3586779/3588234: No such file or directory
[/quote]

Please provide the solution.......

Thanks in advance

Regards
Pavani

Create more than one index in single call

Ted_Yu_2 — GMT

We routinely create 7500+ indexes.

In order to speed up index creation, it is desirable to create more than one index using a single call.

Here is a sample for the extended syntax:
create index idxName on tabA(col1), tabA(col2), tabB(col3)

make code compile using Java 1.6 compiler

Ted_Yu_2 — GMT

tyumac:gridsql tyu$ java -version
java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02-279-10M3065)
Java HotSpot(TM) 64-Bit Server VM (build 16.3-b01-279, mixed mode)
tyumac:gridsql tyu$ uname -a
Darwin tyumac.local 10.4.0 Darwin Kernel Version 10.4.0: Fri Apr 23 18:28:53 PDT 2010; root:xnu-1504.7.4~1/RELEASE_I386 i386 i386

cp -R ../../apps/gridsql/obj/cpy/* ../../apps/gridsql/obj/src
javac -g:none -d ../../apps/gridsql/obj/bin -classpath ../../apps/gridsql/obj/bin:../../apps/gridsql/lib/log4j.jar:../../apps/gridsql/lib/jline-0_9_5.jar:../../apps/gridsql/lib/edb-jdbc14.jar:../../apps/gridsql/lib/postgresql.jar -sourcepath ../../apps/gridsql/obj/src \
../../apps/gridsql/obj/src/com/edb/gridsql/communication/ConnectorFactory.java \
../../apps/gridsql/obj/src/com/edb/gridsql/communication/BroadcastConnector.java \
../../apps/gridsql/obj/src/com/edb/gridsql/communication/ClusteredBroadcastConnector.java \
../../apps/gridsql/obj/src/com/edb/gridsql/communication/ClusteredConnector.java \
../../apps/gridsql/obj/src/com/edb/gridsql/communication/SocketConnector.java \
../../apps/gridsql/obj/src/com/edb/gridsql/engine/io/RequestMessage.java \
../../apps/gridsql/obj/src/com/edb/gridsql/parser/SqlAddNodesToDB.java \
../../apps/gridsql/obj/src/com/edb/gridsql/parser/SqlDropNodesFromDB.java \
../../apps/gridsql/obj/src/com/edb/gridsql/common/ErrorMessages.java \
../../apps/gridsql/obj/src/com/edb/gridsql/protocol/ProtocolManager.java \
../../apps/gridsql/obj/src/com/edb/gridsql/protocol/PgProtocolHandler.java
../../apps/gridsql/obj/src/com/edb/gridsql/common/ResultSetImpl.java:44: com.edb.gridsql.common.ResultSetImpl is not abstract and does not override abstract method updateSQLXML(java.lang.String,java.sql.SQLXML) in java.sql.ResultSet
public class ResultSetImpl implements java.sql.ResultSet {
^
../../apps/gridsql/obj/src/com/edb/gridsql/misc/combinedresultset/ServerResultSetImpl.java:35: com.edb.gridsql.misc.combinedresultset.ServerResultSetImpl is not abstract and does not override abstract method updateSQLXML(java.lang.String,java.sql.SQLXML) in java.sql.ResultSet
public class ServerResultSetImpl implements java.sql.ResultSet, ServerResultSet {
^
../../apps/gridsql/obj/src/com/edb/gridsql/parser/core/CSQLParser.java:10013: cannot find symbol
symbol : constructor dropTable(com.edb.gridsql.parser.core.syntaxtree.NodeToken,com.edb.gridsql.parser.core.syntaxtree.NodeToken,com.edb.gridsql.parser.core.syntaxtree.TableName)
location: class com.edb.gridsql.parser.core.syntaxtree.dropTable
{if (true) return new dropTable(n0,n2,n4);}
^
Note: ../../apps/gridsql/obj/src/com/edb/gridsql/misc/combinedresultset/ServerResultSetImpl.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
Note: Some input files use unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.

drop more than one table in one call

Ted_Yu_2 — GMT

According to http://www.postgresql.org/docs/7.4/interactive/sql-droptable.html, multiple tables can be dropped in one call.
Is this supported by GridSQL ?

Provide more meaningful error message in XdbDbStop and ShutdownXDB

Ted_Yu_2 — GMT

Only e.getMessage() is shown when error happens.

-bash-3.2$ gs-shutdown.sh -u admi -p sec
ERROR: com/edb/gridsql/parser/core/syntaxtree/ShutdownXDB
Parameters: [-f]
where is -j jdbc:edb://:/xdbadmin?user=&password=
or [-h ] [-s ] -u [-p ]
-h : Host name or IP address where XDBServer is running. Default is localhost
-s : XDBServer's port. Default is 6453
-u , -p : Login to the server
-f : Force mode. Try and shutdown server even if databases are online.

We have been using the same command.
The stack trace should be displayed and possibly omitting usage text when error happens.

Provide configuration for minSize of JDBCPool in LoaderConnectionPool

Ted_Yu_2 — GMT

When client uses gs-loader.sh in multiple processes, we observed high number of connections (over 200) to Postgres DB.
The connections didn't close after 10 minutes (xdb.jdbc.pool.idle)

In ObjectPool.java:
protected synchronized void packBuffer() {
while (buffer.size() > 0 && buffer.size() + out.size() > minSize) {
minSize by default is 1.
This means there would always be at least 1 PoolEntry in the pool which can explain why connection to Postgres isn't closed.

I think we can introduce a new configuration parameter to control minSize for JDBCPool. E.g.
Property.getInt("xdb.jdbc.pool.minsize", 0),

Bug while loading the data into a table in grisql

pavani_mallampati — GMT

Hi,
Please find the bug while loading the data into the table in gridsql,

NOT WORKING
The “postgres” version I am using was mentioned below.
Postgres version:
postgres=# select version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)
(1 row)
The “gridsql” version I am using was mentioned below.
GRID version:
GridSQL -> select version();
+-------------+
| EXPRESSION5 |
+-------------+
| 1.0.0.6 |
+-------------+
1 row(s).
Creating “emp” table in grid with the syntax.
GridSQL -> create table employee_details(emp_id integer,
GridSQL -> emp_name varchar,
GridSQL -> emp_address varchar) with xrowid;
OK
And the table was successfully created.
Loading the data into the table using copy:
I have a “csv” file like
[postgres@localhost ~]$ cat emp.csv
1,postgres1,newyork
2,postgres1,newyork
3,postgres1,newyork
4,postgres1,newyork
5,postgres1,newyork
6,postgres1,newyork
7,postgres1,newyork
8,postgres1,newyork
9,postgres1,newyork
10,postgres1,newyork
While loading the data into the table using the copy command then I am getting the error as shown below.
GridSQL -> copy employee_details from '/home/postgres/emp.csv' delimiter ',' CSV;
SQLException: ERROR: java.lang.NumberFormatException: For input string: "newyork"
If I drop the XROWID column then the data is loaded
GridSQL -> alter table employee_details drop column xrowid;
OK
GridSQL -> copy employee_details from '/home/postgres/emp.csv' delimiter ',' CSV;
OK
WHY…………………………………………..
Please reply……………………………………..

WORKING
While working with another version it was working
Postgres version:
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)
(1 row)
GRID version:
GridSQL -> select version();
+---------+
| version |
+---------+
| 1.1.0.9 |
+---------+
1 row(s).
Creating “emp” table in grid with the same syntax.
GridSQL -> create table employee_details(emp_id integer,
GridSQL -> emp_name varchar,
GridSQL -> emp_address varchar) with xrowid;
OK
And the table was successfully created.
Loading the data into the table using copy:
I have a “csv” file like as same as above
[postgres@localhost ~]$ cat emp.csv
1,postgres1,newyork
2,postgres1,newyork
3,postgres1,newyork
4,postgres1,newyork
5,postgres1,newyork
6,postgres1,newyork
7,postgres1,newyork
8,postgres1,newyork
9,postgres1,newyork
10,postgres1,newyork
Data loaded successfully:
GridSQL -> copy employee_details from '/home/postgres/emp.csv' delimiter ',' CSV;
OK

Bug while loading the data into a table in grisql

pavani_mallampati — GMT

Hi

I am using the

Operating system [b]Centos[/b]
postgres version is [b]PostgreSQL 8.3.3[/b]
grid version is [b]1.0.0.6 [/b]

While loading the data into the table using the copy command then I am getting the error as shown below.

GridSQL -> copy employee_details from '/home/postgres/emp.csv' delimiter ',' CSV;
SQLException: ERROR: java.lang.NumberFormatException: For input string: "newyork"

If I drop the XROWID column then the data is loaded

WHY…………………………………………..
Please reply……………………………………..

Two parts - Union + Order by query issue - Why can't I use EXEC DIRECT all the time?

ranga.gopalan@sorrisotech.com — GMT

Hi,

I'm using a version of GridSQL updated from the CVS source tree around May 26th.

I am running into an issue with a query executing successfully in Postgres and not on GridSQL - it works fine with "exec direct on all" - I had a question - Why can't I just run the query using "EXEC DIRECT on ALL '....'" via JDBC from my program - all it seems to do is to add an extra "Node" column to each row in the result set. This works fine in my tests with simple queries.

Is there any inherent problem with this approach? If the query cannot be supported by GridSQL, I would like to use 'exec direct on all' for a particular scenario within my application where I know the data is distributed across all the nodes (and the required Dimension join tables are replicated on all nodes) - Any reason why I shouldn't do this? The query will be limited using the LIMIT clause so there is no reason for huge numbers of rows to be returned to GridSQL by each node.

The "EXEC DIRECT on all" option would allow me to escape having to worry about syntax dependencies on GridSQL - However, probably I will need to send each query as a String literal (without using parameters) - I don't seem to be able to specify JDBC parameters to a prepared statement when using EXEC DIRECT.

It would ideal of course if the query worked with GridSQL and I didn't have to worry about the Exec Direct part.

Incidentally the query in question is:

[code]
SELECT * FROM (
SELECT *
FROM t2
WHERE f1 < 10
ORDER BY f2 DESC
LIMIT 2) t
UNION ALL
SELECT * FROM (
SELECT *
FROM t3
WHERE f1 < 10
ORDER BY f2 DESC
LIMIT 2) t
ORDER BY f2 DESC
LIMIT 2
[/code]

I am trying to use this query to work around a Postgres bug with handling order by + limit clauses when dealing with partitioned tables - (In this scenario both t2 and t3 are child tables of a parent table) - Similar issue to that faced by the person here: http://stackoverflow.com/questions/2236776/efficient-querying-of-multi-partition-postgres-table

Here is the exception shown while executing the above query in the GridSQL console log:

[code]
2010-07-07 16:06:17,600 - ERROR Catching throwable:
com.edb.gridsql.parser.core.ParseException: Encountered "ORDER BY" at line 1, column 56.
Was expecting one of:
"UNION" ...
...
"AND" ...
"OR" ...
"!" ...
"HAVING" ...
")" ...
"::" ...
"+" ...
"-" ...
"||" ...
"*" ...
"/" ...
"MOD" ...
"DIV" ...
"%" ...
"&" ...
"|" ...
"#" ...
"<<" ...
">>" ...
"<<=" ...
">>=" ...
"(+)" ...

at com.edb.gridsql.parser.core.CSQLParser.generateParseException(CSQLParser.java:38089)
at com.edb.gridsql.parser.core.CSQLParser.jj_consume_token(CSQLParser.java:37927)
at com.edb.gridsql.parser.core.CSQLParser.TableSpec(CSQLParser.java:22468)
at com.edb.gridsql.parser.core.CSQLParser.FromTableSpec(CSQLParser.java:22603)
at com.edb.gridsql.parser.core.CSQLParser.TableList(CSQLParser.java:22517)
at com.edb.gridsql.parser.core.CSQLParser.FromClause(CSQLParser.java:22183)
at com.edb.gridsql.parser.core.CSQLParser.SelectWithoutOrderAndSet(CSQLParser.java:12678)
at com.edb.gridsql.parser.core.CSQLParser.SelectWithoutOrderAndSetWithParenthesis(CSQLParser.java:12566)
at com.edb.gridsql.parser.core.CSQLParser.SelectWithoutOrder(CSQLParser.java:12503)
at com.edb.gridsql.parser.core.CSQLParser.SelectWithoutOrderWithParenthesis(CSQLParser.java:12483)
at com.edb.gridsql.parser.core.CSQLParser.Select(CSQLParser.java:12446)
at com.edb.gridsql.parser.core.CSQLParser.SelectWithParenthesis(CSQLParser.java:12430)
at com.edb.gridsql.parser.core.CSQLParser.process(CSQLParser.java:2353)
at com.edb.gridsql.parser.Parser.parse(Parser.java:611)
at com.edb.gridsql.parser.Parser.parseStatement(Parser.java:85)
at com.edb.gridsql.protocol.RequestAnalyzer.getExecutableRequest(RequestAnalyzer.java:149)
at com.edb.gridsql.protocol.PgProtocolSession.run(PgProtocolSession.java:613)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:637)
[/code]

My table structures:
[code]
GridSQL -> describe t2;
+-------------------------------------------------------------------------+
| COLUMN_NAME | SQL_DATA_TYPE | TYPE_NAME | IS_NULLABLE | KEY | DEFAULT |
+-------------------------------------------------------------------------+
| f1 | 4 | INTEGER | YES | NO | |
| f2 | 12 | VARCHAR(10) | YES | NO | |
+-------------------------------------------------------------------------+
2 row(s).

GridSQL -> describe t3;
+-------------------------------------------------------------------------+
| COLUMN_NAME | SQL_DATA_TYPE | TYPE_NAME | IS_NULLABLE | KEY | DEFAULT |
+-------------------------------------------------------------------------+
| f1 | 4 | INTEGER | YES | NO | |
| f2 | 12 | VARCHAR(10) | YES | NO | |
+-------------------------------------------------------------------------+
2 row(s).
[/code]

Seem to be running into 10 parameter limit with JDBC

ranga.gopalan@sorrisotech.com — GMT

Hi,

I updated to the latest source and seem to be hitting a limit of 10 JDBC parameters - The query itself is simple (say select f1,f2 from tableName where f3 in (?,?,?,?,?,?,?,?,?,?) -

Looks like the issue referred to here: http://forums.enterprisedb.com/posts/list/1375.page

Here is the exception on the client side:
[code]
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
QUERY: SELECT "mydim"."dimval" as "dimval" FROM "mydim" WHERE "mydim"."dimval" IN ( 'dim2' , 'dim3' , 'dim1' , 'dim2' , 'dim3' , 'dim4' , 'dim3' , 'dim3' , 'dim3' , 'dim2'0 ) LIMIT 4
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
[/code]

Exception in the Coordinator console.log:

[code]
2010-05-20 21:51:14,008 - ERROR Catching throwable:
com.edb.gridsql.exception.XDBServerException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
QUERY: SELECT "mydim"."dimval" as "dimval" FROM "mydim" WHERE "mydim"."dimval" IN ( 'dim2' , 'dim3' , 'dim1' , 'dim2' , 'dim3' , 'dim4' , 'dim3' , 'dim3' , 'dim3' , 'dim2'0 ) LIMIT 4
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:538)
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryExecPlan(QueryProcessor.java:190)
at com.edb.gridsql.queryproc.QueryProcessor.execute(QueryProcessor.java:1071)
at com.edb.gridsql.parser.SqlSelect.execute(SqlSelect.java:438)
at com.edb.gridsql.engine.ExecutableRequest.execute(ExecutableRequest.java:244)
at com.edb.gridsql.engine.XDBSessionContext.executeRequest(XDBSessionContext.java:1037)
at com.edb.gridsql.engine.ServerStatement.execute(ServerStatement.java:201)
at com.edb.gridsql.engine.ServerStatement.describe(ServerStatement.java:145)
at com.edb.gridsql.engine.XDBSessionContext.describeStatement(XDBSessionContext.java:914)
at com.edb.gridsql.protocol.PgProtocolSession.run(PgProtocolSession.java:659)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:637)
Caused by: com.edb.gridsql.exception.XDBMessageMonitorException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
at com.edb.gridsql.engine.MessageMonitor.checkMessages(MessageMonitor.java:245)
at com.edb.gridsql.engine.MultinodeExecutor.executeStep(MultinodeExecutor.java:931)
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:533)
... 12 more
Caused by: com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
... 1 more
2010-05-20 21:51:14,035 - ERROR Throwing throwable:
com.edb.gridsql.exception.XDBServerException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near "0"
com.edb.gridsql.exception.XDBServerException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
QUERY: SELECT "mydim"."dimval" as "dimval" FROM "mydim" WHERE "mydim"."dimval" IN ( 'dim2' , 'dim3' , 'dim1' , 'dim2' , 'dim3' , 'dim4' , 'dim3' , 'dim3' , 'dim3' , 'dim2'0 ) LIMIT 4
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:538)
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryExecPlan(QueryProcessor.java:190)
at com.edb.gridsql.queryproc.QueryProcessor.execute(QueryProcessor.java:1071)
at com.edb.gridsql.parser.SqlSelect.execute(SqlSelect.java:438)
at com.edb.gridsql.engine.ExecutableRequest.execute(ExecutableRequest.java:244)
at com.edb.gridsql.engine.XDBSessionContext.executeRequest(XDBSessionContext.java:1037)
at com.edb.gridsql.engine.ServerStatement.execute(ServerStatement.java:201)
at com.edb.gridsql.engine.ServerStatement.describe(ServerStatement.java:145)
at com.edb.gridsql.engine.XDBSessionContext.describeStatement(XDBSessionContext.java:914)
at com.edb.gridsql.protocol.PgProtocolSession.run(PgProtocolSession.java:659)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:637)
Caused by: com.edb.gridsql.exception.XDBMessageMonitorException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
at com.edb.gridsql.engine.MessageMonitor.checkMessages(MessageMonitor.java:245)
at com.edb.gridsql.engine.MultinodeExecutor.executeStep(MultinodeExecutor.java:931)
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:533)
... 12 more
Caused by: com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
... 1 more
2010-05-20 21:51:14,036 - ERROR Catching throwable:
com.edb.gridsql.exception.XDBServerException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
QUERY: SELECT "mydim"."dimval" as "dimval" FROM "mydim" WHERE "mydim"."dimval" IN ( 'dim2' , 'dim3' , 'dim1' , 'dim2' , 'dim3' , 'dim4' , 'dim3' , 'dim3' , 'dim3' , 'dim2'0 ) LIMIT 4
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:538)
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryExecPlan(QueryProcessor.java:190)
at com.edb.gridsql.queryproc.QueryProcessor.execute(QueryProcessor.java:1071)
at com.edb.gridsql.parser.SqlSelect.execute(SqlSelect.java:438)
at com.edb.gridsql.engine.ExecutableRequest.execute(ExecutableRequest.java:244)
at com.edb.gridsql.engine.XDBSessionContext.executeRequest(XDBSessionContext.java:1037)
at com.edb.gridsql.engine.ServerStatement.execute(ServerStatement.java:201)
at com.edb.gridsql.engine.ServerStatement.describe(ServerStatement.java:145)
at com.edb.gridsql.engine.XDBSessionContext.describeStatement(XDBSessionContext.java:914)
at com.edb.gridsql.protocol.PgProtocolSession.run(PgProtocolSession.java:659)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:637)
Caused by: com.edb.gridsql.exception.XDBMessageMonitorException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
at com.edb.gridsql.engine.MessageMonitor.checkMessages(MessageMonitor.java:245)
at com.edb.gridsql.engine.MultinodeExecutor.executeStep(MultinodeExecutor.java:931)
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:533)
... 12 more
Caused by: com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: syntax error at or near "0"
Position: 175
at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
... 1 more
[/code]

Table Script:
[code]
create table mydim(dimkey int, dimval varchar(10));

GridSQL -> select * from mydim;
+-----------------+
| dimkey | dimval |
+-----------------+
| 1 | dim1 |
| 2 | dim2 |
| 3 | dim3 |
+-----------------+
[/code]

Jdbc Code:
[code]
String queryWithParam = "select mydim.dimval as dimval from mydim where mydim.dimval in (?,?,?,?,?,?,?,?,?,?) LIMIT 4";

PreparedStatement pStmt = conn.prepareStatement(queryWithParam);

// pStmt.setObject(1, new Integer(1));
// pStmt.setObject(2, new Integer(100));

pStmt.setObject(1, "dim2");
pStmt.setObject(2, "dim3");

pStmt.setObject(3, "dim1");
pStmt.setObject(4, "dim2");
pStmt.setObject(5, "dim3");
pStmt.setObject(6, "dim4");
pStmt.setObject(7, "dim3");
pStmt.setObject(8, "dim3");
pStmt.setObject(9, "dim3");
pStmt.setObject(10, "dim3");

ResultSet rs = pStmt.executeQuery();

[/code]

CVS Check-in: Couple bugs were fixed in subquery handling

Andrei_M — GMT

1. Prepared statements were failing if parameters were in subquery
2. In some cases subquery with UNION were not handled properly

Files:

Checking in src/com/edb/gridsql/parser/handler/WhereClauseHandler.java;
/cvsroot/gridsql/apps/gridsql/src/com/edb/gridsql/parser/handler/WhereClauseHandler.java,v <-- WhereClauseHandler.java
new revision: 1.5; previous revision: 1.4
done
Checking in src/com/edb/gridsql/optimizer/AttributeColumn.java;
/cvsroot/gridsql/apps/gridsql/src/com/edb/gridsql/optimizer/AttributeColumn.java,v <-- AttributeColumn.java
new revision: 1.3; previous revision: 1.2
done
Checking in src/com/edb/gridsql/planner/QueryPlan.java;
/cvsroot/gridsql/apps/gridsql/src/com/edb/gridsql/planner/QueryPlan.java,v <-- QueryPlan.java
new revision: 1.16; previous revision: 1.15
done
Checking in src/com/edb/gridsql/planner/QueryPlan.java;
/cvsroot/gridsql/apps/gridsql/src/com/edb/gridsql/planner/QueryPlan.java,v <-- QueryPlan.java
new revision: 1.17; previous revision: 1.16
done
Checking in src/com/edb/gridsql/planner/Leaf.java;
/cvsroot/gridsql/apps/gridsql/src/com/edb/gridsql/planner/Leaf.java,v <-- Leaf.java
new revision: 1.7; previous revision: 1.6
done