"IN" clause in JOIN

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

"IN" clause in JOIN

Dan Kinsley

Hi guys,

I am currently migrating to LucidDB from MySQL and think I may have found a bug.

 

Consider the following query:

SELECT

*

FROM

(VALUES(1),(2),(3) ) a

INNER JOIN (VALUES(1),(2),(3),(4) ) b ON a.EXPR$0 = b.EXPR$0

WHERE

b.EXPR$0 IN(1,2)

 

Now if you move the IN part into the INNER JOIN like so:

 

SELECT

*

FROM

(VALUES(1),(2),(3) ) a

LEFT JOIN (VALUES(1),(2),(3),(4) ) b ON a.EXPR$0 = b.EXPR$0 AND b.EXPR$0 IN(1,2)

 

I received the error:

Error Executing Query: java.lang.NullPointerException: null 


The Trace.log file shows:

SEVERE: Internal error: while converting `A`.`EXPR$0` = `B`.`EXPR$0` AND `B`.`EXPR$0` IN (1, 2)

 

I am running LucidDB 0.9.3. Has anyone else experienced this issue? Also sorry if this has already been addressed.

 

Thanks,
Dan Kinsley

 

ps – Not a real issue but is there a way to alias the column names returned from VALUES?

 


------------------------------------------------------------------------------
EditLive Enterprise is the world's most technically advanced content
authoring tool. Experience the power of Track Changes, Inline Image
Editing and ensure content is compliant with Accessibility Checking.
http://p.sf.net/sfu/ephox-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: "IN" clause in JOIN

Julian Hyde
You've definitely found a bug. Can you please log it? I can't think of an obvious workaround without looking at the code.
 
To alias columns, use the syntax 'AS t (c1, c2, ...)'. Viz:
 
SELECT *
FROM (VALUES 1, 2, 3) AS a (c1)
JOIN (VALUES 1, 2, 3, 4) AS b (c1)
 ON a.c1 = b.c1
 AND b.c1 IN (1, 2)
 
This is standard SQL syntax, but not many vendors support it yet.
 
Julian

From: Dan Kinsley [mailto:[hidden email]]
Sent: Monday, June 20, 2011 1:26 PM
To: [hidden email]
Subject: [luciddb-users] "IN" clause in JOIN

Hi guys,

I am currently migrating to LucidDB from MySQL and think I may have found a bug.

 

Consider the following query:

SELECT

*

FROM

(VALUES(1),(2),(3) ) a

INNER JOIN (VALUES(1),(2),(3),(4) ) b ON a.EXPR$0 = b.EXPR$0

WHERE

b.EXPR$0 IN(1,2)

 

Now if you move the IN part into the INNER JOIN like so:

 

SELECT

*

FROM

(VALUES(1),(2),(3) ) a

LEFT JOIN (VALUES(1),(2),(3),(4) ) b ON a.EXPR$0 = b.EXPR$0 AND b.EXPR$0 IN(1,2)

 

I received the error:

Error Executing Query: java.lang.NullPointerException: null 


The Trace.log file shows:

SEVERE: Internal error: while converting `A`.`EXPR$0` = `B`.`EXPR$0` AND `B`.`EXPR$0` IN (1, 2)

 

I am running LucidDB 0.9.3. Has anyone else experienced this issue? Also sorry if this has already been addressed.

 

Thanks,
Dan Kinsley

 

ps – Not a real issue but is there a way to alias the column names returned from VALUES?

 


------------------------------------------------------------------------------
EditLive Enterprise is the world's most technically advanced content
authoring tool. Experience the power of Track Changes, Inline Image
Editing and ensure content is compliant with Accessibility Checking.
http://p.sf.net/sfu/ephox-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: "IN" clause in JOIN

Dan Kinsley

Julian,

Thanks for the reply, I have created LDB-243.

 

While we are on the issue of IN clauses, perhaps someone could answer a followup question…

In MySQL, for reports that had multivalued parameters I would assume an empty parameter meant no filter, so I would use the following SQL:

WHERE CONCAT(${myParam}) IS NULL OR myColumn IN(${myParam})

 

I am struggling to find an equivalent functionality in LucidDB. I very well could be missing something obvious, but would appreciate any help anyone could provide with this.

 

Thanks again,

Dan

 

 

From: Julian Hyde [mailto:[hidden email]]
Sent: Monday, June 20, 2011 5:40 PM
To: 'Mailing list for users of LucidDB'
Subject: Re: [luciddb-users] "IN" clause in JOIN

 

You've definitely found a bug. Can you please log it? I can't think of an obvious workaround without looking at the code.

 

To alias columns, use the syntax 'AS t (c1, c2, ...)'. Viz:

 

SELECT *

FROM (VALUES 1, 2, 3) AS a (c1)

JOIN (VALUES 1, 2, 3, 4) AS b (c1)

 ON a.c1 = b.c1

 AND b.c1 IN (1, 2)

 

This is standard SQL syntax, but not many vendors support it yet.

 

Julian


From: Dan Kinsley [mailto:[hidden email]]
Sent: Monday, June 20, 2011 1:26 PM
To: [hidden email]
Subject: [luciddb-users] "IN" clause in JOIN

Hi guys,

I am currently migrating to LucidDB from MySQL and think I may have found a bug.

 

Consider the following query:

SELECT

*

FROM

(VALUES(1),(2),(3) ) a

INNER JOIN (VALUES(1),(2),(3),(4) ) b ON a.EXPR$0 = b.EXPR$0

WHERE

b.EXPR$0 IN(1,2)

 

Now if you move the IN part into the INNER JOIN like so:

 

SELECT

*

FROM

(VALUES(1),(2),(3) ) a

LEFT JOIN (VALUES(1),(2),(3),(4) ) b ON a.EXPR$0 = b.EXPR$0 AND b.EXPR$0 IN(1,2)

 

I received the error:

Error Executing Query: java.lang.NullPointerException: null 


The Trace.log file shows:

SEVERE: Internal error: while converting `A`.`EXPR$0` = `B`.`EXPR$0` AND `B`.`EXPR$0` IN (1, 2)

 

I am running LucidDB 0.9.3. Has anyone else experienced this issue? Also sorry if this has already been addressed.

 

Thanks,
Dan Kinsley

 

ps – Not a real issue but is there a way to alias the column names returned from VALUES?

 


------------------------------------------------------------------------------
EditLive Enterprise is the world's most technically advanced content
authoring tool. Experience the power of Track Changes, Inline Image
Editing and ensure content is compliant with Accessibility Checking.
http://p.sf.net/sfu/ephox-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: "IN" clause in JOIN

Julian Hyde
LucidDB doesn't support substituting strings into queries. Oracle used to call these 'lexical parameters', and they are dangerous because they allow SQL injection. I don't know what MySQL calls them, or whether you are expanding ${myParam} manually in your client. Due to the SQL injection risk, I don't recommend using them.
 
Also be warned that the SQL-standard behavior of null values in an IN clause (especially a NOT IN clause) is not intuitive, due to 3-value logic.
 
For your example, try
 
WHERE myColumn IS NOT DISTINCT FROM ?1
 
"IS NOT DISTINCT FROM" is similar to "=", except that it equates null values.
 
Julian


From: Dan Kinsley [mailto:[hidden email]]
Sent: Tuesday, June 21, 2011 6:03 AM
To: Mailing list for users of LucidDB
Subject: Re: [luciddb-users] "IN" clause in JOIN

Julian,

Thanks for the reply, I have created LDB-243.

 

While we are on the issue of IN clauses, perhaps someone could answer a followup question…

In MySQL, for reports that had multivalued parameters I would assume an empty parameter meant no filter, so I would use the following SQL:

WHERE CONCAT(${myParam}) IS NULL OR myColumn IN(${myParam})

 

I am struggling to find an equivalent functionality in LucidDB. I very well could be missing something obvious, but would appreciate any help anyone could provide with this.

 

Thanks again,

Dan

 

 

From: Julian Hyde [mailto:[hidden email]]
Sent: Monday, June 20, 2011 5:40 PM
To: 'Mailing list for users of LucidDB'
Subject: Re: [luciddb-users] "IN" clause in JOIN

 

You've definitely found a bug. Can you please log it? I can't think of an obvious workaround without looking at the code.

 

To alias columns, use the syntax 'AS t (c1, c2, ...)'. Viz:

 

SELECT *

FROM (VALUES 1, 2, 3) AS a (c1)

JOIN (VALUES 1, 2, 3, 4) AS b (c1)

 ON a.c1 = b.c1

 AND b.c1 IN (1, 2)

 

This is standard SQL syntax, but not many vendors support it yet.

 

Julian


From: Dan Kinsley [mailto:[hidden email]]
Sent: Monday, June 20, 2011 1:26 PM
To: [hidden email]
Subject: [luciddb-users] "IN" clause in JOIN

Hi guys,

I am currently migrating to LucidDB from MySQL and think I may have found a bug.

 

Consider the following query:

SELECT

*

FROM

(VALUES(1),(2),(3) ) a

INNER JOIN (VALUES(1),(2),(3),(4) ) b ON a.EXPR$0 = b.EXPR$0

WHERE

b.EXPR$0 IN(1,2)

 

Now if you move the IN part into the INNER JOIN like so:

 

SELECT

*

FROM

(VALUES(1),(2),(3) ) a

LEFT JOIN (VALUES(1),(2),(3),(4) ) b ON a.EXPR$0 = b.EXPR$0 AND b.EXPR$0 IN(1,2)

 

I received the error:

Error Executing Query: java.lang.NullPointerException: null 


The Trace.log file shows:

SEVERE: Internal error: while converting `A`.`EXPR$0` = `B`.`EXPR$0` AND `B`.`EXPR$0` IN (1, 2)

 

I am running LucidDB 0.9.3. Has anyone else experienced this issue? Also sorry if this has already been addressed.

 

Thanks,
Dan Kinsley

 

ps – Not a real issue but is there a way to alias the column names returned from VALUES?

 


------------------------------------------------------------------------------
EditLive Enterprise is the world's most technically advanced content
authoring tool. Experience the power of Track Changes, Inline Image
Editing and ensure content is compliant with Accessibility Checking.
http://p.sf.net/sfu/ephox-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...