[MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

Markus Gritsch

25 Nov 2007 25 Nov '07

9:20 p.m.

Hi, I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB. SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and" The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. Markus

Show replies by date

Niels Nes

25 Nov 25 Nov

9:29 p.m.

New subject: [MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

On Sun, Nov 25, 2007 at 10:20:25PM +0100, Markus Gritsch wrote:

...

Hi,

I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB.

SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and"

The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. I guess its a default casting problem. As I expect the following to work: select * from test where ((true) and (id=100))

Niels

...

Markus

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

Markus Gritsch

9:32 p.m.

New subject: [MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

On Nov 25, 2007 10:29 PM, Niels Nes wrote:

...

On Sun, Nov 25, 2007 at 10:20:25PM +0100, Markus Gritsch wrote:

...
Hi,

I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB.

SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and"

The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. I guess its a default casting problem. As I expect the following to work: select * from test where ((true) and (id=100))

No, this yields: TypeException:user.s0_2[15]:'calc.max' undefined in: _23:any := calc.max(_19:bit, _22:int) TypeException:user.s0_2[16]:'calc.min' undefined in: _24:any := calc.min(_19:bit, _22:int) TypeException:user.s0_2[17]:'algebra.uselect' undefined in: _25:any := algebra.uselect(_18:bat[:oid,:bit], _23:any, _24:any, _17:bit, _17:bit) TypeException:user.s0_2[19]:'algebra.markT' undefined in: _28:any := algebra.markT(_25:any, _26:oid) TypeException:user.s0_2[20]:'bat.reverse' undefined in: _29:any := bat.reverse(_28:any) TypeException:user.s0_2[21]:'algebra.join' undefined in: _30:any := algebra.join(_29:any, _12:bat[:oid,:int]) TypeException:user.s0_2[28]:'algebra.join' undefined in: _40:any := algebra.join(_29:any, _39:bat[:oid,:str]) TypeException:user.s0_2[35]:'algebra.join' undefined in: _51:any := algebra.join(_29:any, _50:bat[:oid,:lng]) TypeException:user.s0_2[42]:'algebra.join' undefined in: _59:any := algebra.join(_29:any, _58:bat[:oid,:lng]) TypeException:user.s0_2[49]:'algebra.join' undefined in: _67:any := algebra.join(_29:any, _66:bat[:oid,:lng]) TypeException:user.s0_2[56]:'algebra.join' undefined in: _77:any := algebra.join(_29:any, _76:bat[:oid,:lng]) TypeException:user.s0_2[63]:'algebra.join' undefined in: _88:any := algebra.join(_29:any, _87:bat[:oid,:lng]) TypeException:user.s0_2[70]:'algebra.join' undefined in: _96:any := algebra.join(_29:any, _95:bat[:oid,:bit]) TypeException:user.s0_2[77]:'algebra.join' undefined in: _106:any := algebra.join(_29:any, _105:bat[:oid,:timestamp]) TypeException:user.s0_2[84]:'algebra.join' undefined in: _114:any := algebra.join(_29:any, _113:bat[:oid,:int]) Markus

Niels Nes

9:35 p.m.

New subject: [MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

On Sun, Nov 25, 2007 at 10:32:56PM +0100, Markus Gritsch wrote:

...

On Nov 25, 2007 10:29 PM, Niels Nes wrote:

...
On Sun, Nov 25, 2007 at 10:20:25PM +0100, Markus Gritsch wrote:

...
Hi,

I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB.

SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and"

The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. I guess its a default casting problem. As I expect the following to work: select * from test where ((true) and (id=100))

No, this yields:

But thats a completely different problem. Could you send the complete bug ie includeing schema for test and full query which gives this problem. Niels

...

TypeException:user.s0_2[15]:'calc.max' undefined in: _23:any := calc.max(_19:bit, _22:int) TypeException:user.s0_2[16]:'calc.min' undefined in: _24:any := calc.min(_19:bit, _22:int) TypeException:user.s0_2[17]:'algebra.uselect' undefined in: _25:any := algebra.uselect(_18:bat[:oid,:bit], _23:any, _24:any, _17:bit, _17:bit) TypeException:user.s0_2[19]:'algebra.markT' undefined in: _28:any := algebra.markT(_25:any, _26:oid) TypeException:user.s0_2[20]:'bat.reverse' undefined in: _29:any := bat.reverse(_28:any) TypeException:user.s0_2[21]:'algebra.join' undefined in: _30:any := algebra.join(_29:any, _12:bat[:oid,:int]) TypeException:user.s0_2[28]:'algebra.join' undefined in: _40:any := algebra.join(_29:any, _39:bat[:oid,:str]) TypeException:user.s0_2[35]:'algebra.join' undefined in: _51:any := algebra.join(_29:any, _50:bat[:oid,:lng]) TypeException:user.s0_2[42]:'algebra.join' undefined in: _59:any := algebra.join(_29:any, _58:bat[:oid,:lng]) TypeException:user.s0_2[49]:'algebra.join' undefined in: _67:any := algebra.join(_29:any, _66:bat[:oid,:lng]) TypeException:user.s0_2[56]:'algebra.join' undefined in: _77:any := algebra.join(_29:any, _76:bat[:oid,:lng]) TypeException:user.s0_2[63]:'algebra.join' undefined in: _88:any := algebra.join(_29:any, _87:bat[:oid,:lng]) TypeException:user.s0_2[70]:'algebra.join' undefined in: _96:any := algebra.join(_29:any, _95:bat[:oid,:bit]) TypeException:user.s0_2[77]:'algebra.join' undefined in: _106:any := algebra.join(_29:any, _105:bat[:oid,:timestamp]) TypeException:user.s0_2[84]:'algebra.join' undefined in: _114:any := algebra.join(_29:any, _113:bat[:oid,:int])

Markus

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Markus Gritsch

9:44 p.m.

New subject: [MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

On Nov 25, 2007 10:35 PM, Niels Nes wrote:

...

On Sun, Nov 25, 2007 at 10:32:56PM +0100, Markus Gritsch wrote:

...
On Nov 25, 2007 10:29 PM, Niels Nes wrote:

...
On Sun, Nov 25, 2007 at 10:20:25PM +0100, Markus Gritsch wrote:

...
Hi,

I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB.

SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and"

The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. I guess its a default casting problem. As I expect the following to work: select * from test where ((true) and (id=100))

No, this yields:

But thats a completely different problem. Could you send the complete bug ie includeing schema for test and full query which gives this problem.

sql>CREATE TABLE test (id INT); sql>INSERT INTO test VALUES (1); Rows affected 1 sql>SELECT * FROM test WHERE ((true) AND (id=1)); !TypeException:user.s1_4[14]:'calc.max' undefined in: _22:any := calc.max(_20:bit, _21:int) !TypeException:user.s1_4[15]:'calc.min' undefined in: _23:any := calc.min(_20:bit, _21:int) !TypeException:user.s1_4[16]:'algebra.uselect' undefined in: _24:any := algebra.uselect(_19:bat[:oid,:bit], _22:any, _23:any, _18:bit, _18:bit) !TypeException:user.s1_4[18]:'algebra.markT' undefined in: _27:any := algebra.markT(_24:any, _25:oid) !TypeException:user.s1_4[19]:'bat.reverse' undefined in: _28:any := bat.reverse(_27:any) !TypeException:user.s1_4[20]:'algebra.join' undefined in: _29:any := algebra.join(_28:any, _13:bat[:oid,:int]) +-------+ | id | +=======+ | 18645 | +-------+ sql>DROP TABLE test; sql>

Markus Gritsch

10:16 p.m.

New subject: [MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

On Nov 25, 2007 10:44 PM, Markus Gritsch wrote:

...

On Nov 25, 2007 10:35 PM, Niels Nes wrote:

...
On Sun, Nov 25, 2007 at 10:32:56PM +0100, Markus Gritsch wrote:

...
On Nov 25, 2007 10:29 PM, Niels Nes wrote:

...
On Sun, Nov 25, 2007 at 10:20:25PM +0100, Markus Gritsch wrote:

...
Hi,

I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB.

SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and"

The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. I guess its a default casting problem. As I expect the following to work: select * from test where ((true) and (id=100))

No, this yields:

But thats a completely different problem. Could you send the complete bug ie includeing schema for test and full query which gives this problem.

sql>CREATE TABLE test (id INT); sql>INSERT INTO test VALUES (1); Rows affected 1 sql>SELECT * FROM test WHERE ((true) AND (id=1)); !TypeException:user.s1_4[14]:'calc.max' undefined in: _22:any := calc.max(_20:bit, _21:int) !TypeException:user.s1_4[15]:'calc.min' undefined in: _23:any := calc.min(_20:bit, _21:int) !TypeException:user.s1_4[16]:'algebra.uselect' undefined in: _24:any := algebra.uselect(_19:bat[:oid,:bit], _22:any, _23:any, _18:bit, _18:bit) !TypeException:user.s1_4[18]:'algebra.markT' undefined in: _27:any := algebra.markT(_24:any, _25:oid) !TypeException:user.s1_4[19]:'bat.reverse' undefined in: _28:any := bat.reverse(_27:any) !TypeException:user.s1_4[20]:'algebra.join' undefined in: _29:any := algebra.join(_28:any, _13:bat[:oid,:int]) +-------+ | id | +=======+ | 18645 | +-------+ sql>DROP TABLE test; sql>

And the server console prints = TODO: select_count(const)

Niels Nes

26 Nov 26 Nov

7:05 a.m.

New subject: [MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

On Sun, Nov 25, 2007 at 11:16:57PM +0100, Markus Gritsch wrote:

...

On Nov 25, 2007 10:44 PM, Markus Gritsch wrote:

...
On Nov 25, 2007 10:35 PM, Niels Nes wrote:

...
On Sun, Nov 25, 2007 at 10:32:56PM +0100, Markus Gritsch wrote:

...
On Nov 25, 2007 10:29 PM, Niels Nes wrote:

...
On Sun, Nov 25, 2007 at 10:20:25PM +0100, Markus Gritsch wrote:

...
Hi,

I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB.

SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and"

The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. I guess its a default casting problem. As I expect the following to work: select * from test where ((true) and (id=100))

No, this yields:

But thats a completely different problem. Could you send the complete bug ie includeing schema for test and full query which gives this problem.

sql>CREATE TABLE test (id INT); sql>INSERT INTO test VALUES (1); Rows affected 1 sql>SELECT * FROM test WHERE ((true) AND (id=1)); !TypeException:user.s1_4[14]:'calc.max' undefined in: _22:any := calc.max(_20:bit, _21:int) !TypeException:user.s1_4[15]:'calc.min' undefined in: _23:any := calc.min(_20:bit, _21:int) !TypeException:user.s1_4[16]:'algebra.uselect' undefined in: _24:any := algebra.uselect(_19:bat[:oid,:bit], _22:any, _23:any, _18:bit, _18:bit) !TypeException:user.s1_4[18]:'algebra.markT' undefined in: _27:any := algebra.markT(_24:any, _25:oid) !TypeException:user.s1_4[19]:'bat.reverse' undefined in: _28:any := bat.reverse(_27:any) !TypeException:user.s1_4[20]:'algebra.join' undefined in: _29:any := algebra.join(_28:any, _13:bat[:oid,:int]) +-------+ | id | +=======+ | 18645 | +-------+ sql>DROP TABLE test; sql>

And the server console prints

= TODO: select_count(const) This warning is meant for me only and is harmless. I thought you were using a released version, if this is through we somehow build wrong for the releases (ie with asserts on).

Niels

...

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Stefan Manegold

7:09 a.m.

New subject: [MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

On Mon, Nov 26, 2007 at 08:05:22AM +0100, Niels Nes wrote:

...

...
= TODO: select_count(const) This warning is meant for me only and is harmless. I thought you were using a released version, if this is through we somehow build wrong for the releases (ie with asserts on).

Stefan Manegold

7:19 a.m.

New subject: [MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

On Mon, Nov 26, 2007 at 08:09:09AM +0100, Stefan Manegold wrote:

...

On Mon, Nov 26, 2007 at 08:05:22AM +0100, Niels Nes wrote:

...
...
= TODO: select_count(const) This warning is meant for me only and is harmless. I thought you were using a released version, if this is through we somehow build wrong for the releases (ie with asserts on).

possibly, I/we forgot to switch the default in the release branch for Windows --- I'l check ...

Indeed, for Windows, the default is to build with assertions enabled; hence, unless Sjoerd built the installers explicitly with `nmake NDEBUG=1`, the Windows distribution has assertions enabled (in contrast to the Linux RPMs as well as any default build from the released source tarballs). Sorry for the inconvenience! Stefan -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

Sjoerd Mullender

3:23 p.m.

New subject: [MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB

Markus Gritsch wrote:

...

On Nov 25, 2007 10:44 PM, Markus Gritsch wrote:

...
On Nov 25, 2007 10:35 PM, Niels Nes wrote:

...
On Sun, Nov 25, 2007 at 10:32:56PM +0100, Markus Gritsch wrote:

...
On Nov 25, 2007 10:29 PM, Niels Nes wrote:

...
On Sun, Nov 25, 2007 at 10:20:25PM +0100, Markus Gritsch wrote:

...
Hi,

I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB.

SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and"

The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. I guess its a default casting problem. As I expect the following to work: select * from test where ((true) and (id=100)) No, this yields: But thats a completely different problem. Could you send the complete bug ie includeing schema for test and full query which gives this problem. sql>CREATE TABLE test (id INT); sql>INSERT INTO test VALUES (1); Rows affected 1 sql>SELECT * FROM test WHERE ((true) AND (id=1)); !TypeException:user.s1_4[14]:'calc.max' undefined in: _22:any := calc.max(_20:bit, _21:int) !TypeException:user.s1_4[15]:'calc.min' undefined in: _23:any := calc.min(_20:bit, _21:int) !TypeException:user.s1_4[16]:'algebra.uselect' undefined in: _24:any := algebra.uselect(_19:bat[:oid,:bit], _22:any, _23:any, _18:bit, _18:bit) !TypeException:user.s1_4[18]:'algebra.markT' undefined in: _27:any := algebra.markT(_24:any, _25:oid) !TypeException:user.s1_4[19]:'bat.reverse' undefined in: _28:any := bat.reverse(_27:any) !TypeException:user.s1_4[20]:'algebra.join' undefined in: _29:any := algebra.join(_28:any, _13:bat[:oid,:int]) +-------+ | id | +=======+ | 18645 | +-------+ sql>DROP TABLE test; sql>

And the server console prints

= TODO: select_count(const)

I have uploaded a new Windows installer to SourceForge that was compiled from the same sources but with debugging disabled. That version should not produce messages like this. -- Sjoerd Mullender

6242

Age (days ago)

6243

Last active (days ago)

List overview

Download

9 comments

4 participants

participants (4)

Markus Gritsch
Niels Nes
Sjoerd Mullender
Stefan Manegold