You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Wilhelm <wi...@anaplan.com> on 2018/03/20 23:51:51 UTC
Error: Failed to parse query: SELECT * FROM ...
Hello,
I'm playing with ignite to evaluate it, basically I want to test how class
inheritance/polymorphism works with the cache (and SQL search-able).
To test well this I'm trying to look at the result it produce in DBeaver
and/or sqlline.sh
Note: I'm using version 2.3.0 (same result on version 2.4.0)
If I create a tables and add data from dbeaver everything is fine, I can see
the tables and data.
If I create the tables and data from java code then I can see the tables
from dbeaver or sqlline but if I add some data I can see it from java but I
do not from dbeaver or sqlline, any idea?
Here is an example:
Note: I put a breakpoint to the println so I can test before it clears the
cache.
public interface IA {
double get_fa_1();
void set(double d);
}
public class A implements IA {
@QuerySqlField(index = true)
public double fa_1 =3;
@QuerySqlField
public B0 _b = null;
@QuerySqlField
public String name = "A";
public A(double d){ fa_1 = d;}
public double get_fa_1(){return fa_1; }
public void set(double d){fa_1 = d;}
}
public class B0{
@QuerySqlField
public double fb0_1 =5;
@QuerySqlField
public double fb0_2 =10;
@QuerySqlField
public String name = "B0";
public B0(double b1, double b2){ fb0_1=b1; fb0_2=b2;}
}
public class B10 extends B0 {
@QuerySqlField
public double fb10_1 = 6;
@QuerySqlField
public String name = "B10";
public B10(double b1, double b2) {super(b1, b2);}
}
public class IgniteATest {
private static final Logger LOG =
LoggerFactory.getLogger(IgniteAna.class);
private static String PREFIXTABLE = "";//IgniteSQL.class.getSimpleName()
+ "Cache_";
private static Ignite ignite;
public static void main(String[] args) {
try {
//Ignition.setClientMode(false);
ignite = Ignition.start("ignite-docker.xml");
//ignite.active(true);
CacheConfiguration<Long, A> a_cacheCfg = new
CacheConfiguration<>(PREFIXTABLE+"A");
a_cacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default.
a_cacheCfg.setIndexedTypes(Long.class, A.class);
CacheConfiguration<AffinityKey<Long>, B0> b_cacheCfg = new
CacheConfiguration<>(PREFIXTABLE+"B");
b_cacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default.
b_cacheCfg.setIndexedTypes(AffinityKey.class, B0.class);
IgniteCache<Long, A> cache_a =
ignite.getOrCreateCache(a_cacheCfg);
IgniteCache<AffinityKey<Long>, B0> cache_b =
ignite.getOrCreateCache(b_cacheCfg);
A a = new A(0);
a._b = new B0(0.2, 0.3);
cache_a.put(1L, a);
cache_b.put(new AffinityKey<Long>(1L), a._b);
a = new A(1);
a._b = new B10(1.2, 1.3);
cache_a.put(2L, a);
cache_b.put(new AffinityKey<Long>(2L), a._b);
/* a = new A(2);
a._b = new B11(2.2, 2.3);
cache_a.put(3L, a);
cache_b.put(new AffinityKey<Long>(3L), a._b);
a = new A(3);
a._b = new B2(3.2, 3.3);
cache_a.put(4L, a);
cache_b.put(new AffinityKey<Long>(4L), a._b);
*/
A a0 = cache_a.get(1L);
System.out.println(a0);
} catch (Exception ex) {
LOG.error("Got exception: {}", ex.getMessage(), ex);
} finally {
ignite.destroyCache(PREFIXTABLE+"A");
ignite.destroyCache(PREFIXTABLE+"B");
LOG.info("Caches dropped...");
ignite.close();
}
}
Here is what I get from sqlline.sh
sqlline version 1.3.0
0: jdbc:ignite:thin://127.0.0.1/> !table
+--------------------------------+--------------------------------+--------------------------------+---------------------------------+
| TABLE_CAT | TABLE_SCHEM |
TABLE_NAME | TABLE_TYPE |
+--------------------------------+--------------------------------+--------------------------------+---------------------------------+
| | A | A
| TABLE |
| | B | B0
| TABLE |
+--------------------------------+--------------------------------+--------------------------------+---------------------------------+
0: jdbc:ignite:thin://127.0.0.1/> SELECT * FROM B;
Error: Failed to parse query: SELECT * FROM B (state=42000,code=0)
java.sql.SQLException: Failed to parse query: SELECT * FROM B
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:648)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1/> SELECT * FROM B0;
Error: Failed to parse query: SELECT * FROM B0 (state=42000,code=0)
java.sql.SQLException: Failed to parse query: SELECT * FROM B0
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:648)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1/> select * from A;
Error: Failed to parse query: select * from A (state=42000,code=0)
java.sql.SQLException: Failed to parse query: select * from A
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:648)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1/>
and from dbeaver I get the table, but if I ask for the data I see "SQL Error
[50000]: javax.cache.CacheException:Failed to run map query remotely.
thanks for your help
w
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Error: Failed to parse query: SELECT * FROM ...
Posted by Wilhelm <wi...@anaplan.com>.
Working!
Thanks!
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Error: Failed to parse query: SELECT * FROM ...
Posted by Alex Plehanov <pl...@gmail.com>.
In case of Cache_A and Cache_B you need to use quote symbols, since table
schema is case-sensitive, but parser convert schemas in your query to upper
case.
Try these statements:
SELECT * FROM "Cache_B".B0;
SELECT * FROM "Cache_A".A;
2018-03-21 20:27 GMT+03:00 Wilhelm <wi...@anaplan.com>:
> Super - Thanks a lot Alex.
>
> So the first one works well (sqlline, dbeaver and java)
> SELECT * FROM B.B0;
>
> But the second one failed (dbeaver and sqlline),
> SELECT * FROM A.A;
>
> So I thought may be if the cache name is different from object type the it
> might work
> so instead of "Cache/Schema.ObjectClass" like A.A or B.B0 I use Cache_A.A
> and Cache_B.B0 but that is failing
> The only thing I changed in the code is
> private static String PREFIXTABLE =
> "Cache_";//IgniteSQL.class.getSimpleName() + "Cache_";
>
> +--------------------------------+--------------------------
> ------+--------------------------------+---------------------------------+
> | TABLE_CAT | TABLE_SCHEM |
> TABLE_NAME | TABLE_TYPE |
> +--------------------------------+--------------------------
> ------+--------------------------------+---------------------------------+
> | | Cache_B | B0
> | TABLE |
> | | Cache_A | A
> | TABLE |
> +--------------------------------+--------------------------
> ------+--------------------------------+---------------------------------+
> 0: jdbc:ignite:thin://127.0.0.1/> SELECT * FROM Cache_A.A;
> Error: Failed to parse query. Schema "CACHE_A" not found; SQL statement:
> SELECT * FROM Cache_A.A [90079-195] (state=42000,code=0)
> java.sql.SQLException: Failed to parse query. Schema "CACHE_A" not found;
> SQL statement:
> SELECT * FROM Cache_A.A [90079-195]
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(
> JdbcThinConnection.java:648)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute0(JdbcThinStatement.java:130)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute(JdbcThinStatement.java:299)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> 0: jdbc:ignite:thin://127.0.0.1/> SELECT * FROM Cache_B.B0;
> Error: Failed to parse query. Schema "CACHE_B" not found; SQL statement:
> SELECT * FROM Cache_B.B0 [90079-195] (state=42000,code=0)
> java.sql.SQLException: Failed to parse query. Schema "CACHE_B" not found;
> SQL statement:
> SELECT * FROM Cache_B.B0 [90079-195]
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(
> JdbcThinConnection.java:648)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute0(JdbcThinStatement.java:130)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute(JdbcThinStatement.java:299)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> 0: jdbc:ignite:thin://127.0.0.1/>
>
>
> Thanks
> w
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Error: Failed to parse query: SELECT * FROM ...
Posted by Wilhelm <wi...@anaplan.com>.
Super - Thanks a lot Alex.
So the first one works well (sqlline, dbeaver and java)
SELECT * FROM B.B0;
But the second one failed (dbeaver and sqlline),
SELECT * FROM A.A;
So I thought may be if the cache name is different from object type the it
might work
so instead of "Cache/Schema.ObjectClass" like A.A or B.B0 I use Cache_A.A
and Cache_B.B0 but that is failing
The only thing I changed in the code is
private static String PREFIXTABLE =
"Cache_";//IgniteSQL.class.getSimpleName() + "Cache_";
+--------------------------------+--------------------------------+--------------------------------+---------------------------------+
| TABLE_CAT | TABLE_SCHEM |
TABLE_NAME | TABLE_TYPE |
+--------------------------------+--------------------------------+--------------------------------+---------------------------------+
| | Cache_B | B0
| TABLE |
| | Cache_A | A
| TABLE |
+--------------------------------+--------------------------------+--------------------------------+---------------------------------+
0: jdbc:ignite:thin://127.0.0.1/> SELECT * FROM Cache_A.A;
Error: Failed to parse query. Schema "CACHE_A" not found; SQL statement:
SELECT * FROM Cache_A.A [90079-195] (state=42000,code=0)
java.sql.SQLException: Failed to parse query. Schema "CACHE_A" not found;
SQL statement:
SELECT * FROM Cache_A.A [90079-195]
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:648)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1/> SELECT * FROM Cache_B.B0;
Error: Failed to parse query. Schema "CACHE_B" not found; SQL statement:
SELECT * FROM Cache_B.B0 [90079-195] (state=42000,code=0)
java.sql.SQLException: Failed to parse query. Schema "CACHE_B" not found;
SQL statement:
SELECT * FROM Cache_B.B0 [90079-195]
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:648)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1/>
Thanks
w
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Error: Failed to parse query: SELECT * FROM ...
Posted by Alex Plehanov <pl...@gmail.com>.
Hello, Wilhelm!
Default SQL schema is PUBLIC, when you create tables via SQL or query
tables without specifying schema - default schema is used. When you create
tables via java - cache name is used as schema. Your sqlline output shows
that there are two tables A and B0 were created in schemas A and
B respectively.
Try to use this SQL to query your data:
SELECT * FROM B.B0;
SELECT * FROM A.A;
2018-03-21 2:51 GMT+03:00 Wilhelm <wi...@anaplan.com>:
> Hello,
>
> I'm playing with ignite to evaluate it, basically I want to test how class
> inheritance/polymorphism works with the cache (and SQL search-able).
> To test well this I'm trying to look at the result it produce in DBeaver
> and/or sqlline.sh
>
> Note: I'm using version 2.3.0 (same result on version 2.4.0)
>
> If I create a tables and add data from dbeaver everything is fine, I can
> see
> the tables and data.
> If I create the tables and data from java code then I can see the tables
> from dbeaver or sqlline but if I add some data I can see it from java but I
> do not from dbeaver or sqlline, any idea?
>
> Here is an example:
> Note: I put a breakpoint to the println so I can test before it clears the
> cache.
>
> public interface IA {
> double get_fa_1();
> void set(double d);
> }
>
> public class A implements IA {
> @QuerySqlField(index = true)
> public double fa_1 =3;
> @QuerySqlField
> public B0 _b = null;
> @QuerySqlField
> public String name = "A";
>
> public A(double d){ fa_1 = d;}
> public double get_fa_1(){return fa_1; }
> public void set(double d){fa_1 = d;}
>
> }
>
> public class B0{
> @QuerySqlField
> public double fb0_1 =5;
> @QuerySqlField
> public double fb0_2 =10;
> @QuerySqlField
> public String name = "B0";
>
> public B0(double b1, double b2){ fb0_1=b1; fb0_2=b2;}
> }
>
> public class B10 extends B0 {
> @QuerySqlField
> public double fb10_1 = 6;
> @QuerySqlField
> public String name = "B10";
>
> public B10(double b1, double b2) {super(b1, b2);}
> }
>
> public class IgniteATest {
>
> private static final Logger LOG =
> LoggerFactory.getLogger(IgniteAna.class);
> private static String PREFIXTABLE = "";//IgniteSQL.class.
> getSimpleName()
> + "Cache_";
>
> private static Ignite ignite;
>
> public static void main(String[] args) {
> try {
>
> //Ignition.setClientMode(false);
> ignite = Ignition.start("ignite-docker.xml");
> //ignite.active(true);
>
>
> CacheConfiguration<Long, A> a_cacheCfg = new
> CacheConfiguration<>(PREFIXTABLE+"A");
>
> a_cacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default.
> a_cacheCfg.setIndexedTypes(Long.class, A.class);
>
>
> CacheConfiguration<AffinityKey<Long>, B0> b_cacheCfg = new
> CacheConfiguration<>(PREFIXTABLE+"B");
>
> b_cacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default.
> b_cacheCfg.setIndexedTypes(AffinityKey.class, B0.class);
>
> IgniteCache<Long, A> cache_a =
> ignite.getOrCreateCache(a_cacheCfg);
> IgniteCache<AffinityKey<Long>, B0> cache_b =
> ignite.getOrCreateCache(b_cacheCfg);
>
>
> A a = new A(0);
> a._b = new B0(0.2, 0.3);
> cache_a.put(1L, a);
> cache_b.put(new AffinityKey<Long>(1L), a._b);
>
> a = new A(1);
> a._b = new B10(1.2, 1.3);
> cache_a.put(2L, a);
> cache_b.put(new AffinityKey<Long>(2L), a._b);
>
> /* a = new A(2);
> a._b = new B11(2.2, 2.3);
> cache_a.put(3L, a);
> cache_b.put(new AffinityKey<Long>(3L), a._b);
>
> a = new A(3);
> a._b = new B2(3.2, 3.3);
> cache_a.put(4L, a);
> cache_b.put(new AffinityKey<Long>(4L), a._b);
> */
> A a0 = cache_a.get(1L);
>
> System.out.println(a0);
>
> } catch (Exception ex) {
> LOG.error("Got exception: {}", ex.getMessage(), ex);
> } finally {
> ignite.destroyCache(PREFIXTABLE+"A");
> ignite.destroyCache(PREFIXTABLE+"B");
>
> LOG.info("Caches dropped...");
>
> ignite.close();
> }
> }
>
> Here is what I get from sqlline.sh
> sqlline version 1.3.0
> 0: jdbc:ignite:thin://127.0.0.1/> !table
> +--------------------------------+--------------------------
> ------+--------------------------------+---------------------------------+
> | TABLE_CAT | TABLE_SCHEM |
> TABLE_NAME | TABLE_TYPE |
> +--------------------------------+--------------------------
> ------+--------------------------------+---------------------------------+
> | | A | A
> | TABLE |
> | | B | B0
> | TABLE |
> +--------------------------------+--------------------------
> ------+--------------------------------+---------------------------------+
> 0: jdbc:ignite:thin://127.0.0.1/> SELECT * FROM B;
> Error: Failed to parse query: SELECT * FROM B (state=42000,code=0)
> java.sql.SQLException: Failed to parse query: SELECT * FROM B
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(
> JdbcThinConnection.java:648)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute0(JdbcThinStatement.java:130)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute(JdbcThinStatement.java:299)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> 0: jdbc:ignite:thin://127.0.0.1/> SELECT * FROM B0;
> Error: Failed to parse query: SELECT * FROM B0 (state=42000,code=0)
> java.sql.SQLException: Failed to parse query: SELECT * FROM B0
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(
> JdbcThinConnection.java:648)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute0(JdbcThinStatement.java:130)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute(JdbcThinStatement.java:299)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> 0: jdbc:ignite:thin://127.0.0.1/> select * from A;
> Error: Failed to parse query: select * from A (state=42000,code=0)
> java.sql.SQLException: Failed to parse query: select * from A
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(
> JdbcThinConnection.java:648)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute0(JdbcThinStatement.java:130)
> at
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.
> execute(JdbcThinStatement.java:299)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> 0: jdbc:ignite:thin://127.0.0.1/>
>
> and from dbeaver I get the table, but if I ask for the data I see "SQL
> Error
> [50000]: javax.cache.CacheException:Failed to run map query remotely.
>
> thanks for your help
>
> w
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>