You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Иван Федотов <iv...@gmail.com> on 2017/10/17 08:16:39 UTC
Wrong SQL statement
Hello, Igniters!
Currently, if one try to execute `INSERT INTO t1 VALUES(...);` it will be
“IgniteSQLException: Failed to parse query: INSERT INTO Person VALUES(?,?)
…
Caused by: class
org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to
parse query: INSERT INTO Person VALUES(?,?)
…
Caused by: org.h2.jdbc.JdbcSQLException: Неверное количество столбцов”
It looks like a bug, because:
1.H2 supports format “Insert into t1 values()” [1]
2.SQL-92 tells us it is a correct query. Paragraph 13.8 - insert
statement, syntax rules[2].
So, I want to create ticket to fix it, what do you think?
Reproducer:
public class IgniteSqlAllColumnsInsertTest extends GridCommonAbstractTest {
public void testSqlInsert() throws Exception {
try (Ignite ignite = startGrid(0)) {
CacheConfiguration<Integer, Integer> cacheCfg = new
CacheConfiguration<Integer, Integer>("CachePerson").setSqlSchema("PUBLIC");
IgniteCache<Integer, Integer> cache =
ignite.getOrCreateCache(cacheCfg);
cache.query(new SqlFieldsQuery("CREATE TABLE Person (Name
varchar, Age int, primary key (Name))"));
// Good query
QueryCursor<List<?>> cursor = cache.query(
new SqlFieldsQuery("INSERT INTO Person (Name, Age) VALUES
(?,?)")
.setArgs("Alice", 23)
);
assertEquals(1L, cursor.getAll().get(0).<Long>get(0));
// Bad query
cursor = cache.query(
new SqlFieldsQuery("INSERT INTO Person VALUES(?,?)")
.setArgs("Bob", 25)
);
assertEquals(1L, cursor.getAll().get(0).<Long>get(0));
}
}
}
[1] http://www.h2database.com/html/history.html
[2]http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
--
Ivan Fedotov.
ivanan639@gmail.com
Re: Wrong SQL statement
Posted by Taras Ledkov <tl...@gridgain.com>.
Hi,
There is a ticket to track it:
https://issues.apache.org/jira/browse/IGNITE-6111.
On 17.10.2017 11:16, Иван Федотов wrote:
> Hello, Igniters!
>
>
> Currently, if one try to execute `INSERT INTO t1 VALUES(...);` it will be
> “IgniteSQLException: Failed to parse query: INSERT INTO Person VALUES(?,?)
>
> …
>
> Caused by: class
> org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to
> parse query: INSERT INTO Person VALUES(?,?)
>
> …
>
> Caused by: org.h2.jdbc.JdbcSQLException: Неверное количество столбцов”
>
> It looks like a bug, because:
>
>
> 1.H2 supports format “Insert into t1 values()” [1]
>
> 2.SQL-92 tells us it is a correct query. Paragraph 13.8 - insert
> statement, syntax rules[2].
>
>
> So, I want to create ticket to fix it, what do you think?
>
>
> Reproducer:
>
>
> public class IgniteSqlAllColumnsInsertTest extends GridCommonAbstractTest {
>
> public void testSqlInsert() throws Exception {
>
> try (Ignite ignite = startGrid(0)) {
>
> CacheConfiguration<Integer, Integer> cacheCfg = new
> CacheConfiguration<Integer, Integer>("CachePerson").setSqlSchema("PUBLIC");
>
>
> IgniteCache<Integer, Integer> cache =
> ignite.getOrCreateCache(cacheCfg);
>
>
> cache.query(new SqlFieldsQuery("CREATE TABLE Person (Name
> varchar, Age int, primary key (Name))"));
>
>
> // Good query
>
> QueryCursor<List<?>> cursor = cache.query(
>
> new SqlFieldsQuery("INSERT INTO Person (Name, Age) VALUES
> (?,?)")
>
> .setArgs("Alice", 23)
>
> );
>
>
> assertEquals(1L, cursor.getAll().get(0).<Long>get(0));
>
>
> // Bad query
>
> cursor = cache.query(
>
> new SqlFieldsQuery("INSERT INTO Person VALUES(?,?)")
>
> .setArgs("Bob", 25)
>
> );
>
>
> assertEquals(1L, cursor.getAll().get(0).<Long>get(0));
>
> }
>
> }
>
> }
>
>
>
>
> [1] http://www.h2database.com/html/history.html
>
>
> [2]http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
>
>
--
Taras Ledkov
Mail-To: tledkov@gridgain.com