Wrong SQL statement

[Ivan Fedotov]

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.

[hidden email]

[Taras Ledkov]

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: [hidden email]