Hi,
Firstly, thanks for the great tool that H2 is. I would like to report
what looks like a bug in H2.
The following query works but when compose it within a nested expression
(e.g. with <viewname> as (...) <some additional query>, shown highlighted
in yellow below), I get a NullPointer expression. Will it be possible to
take a look. The query relies on two function aliases defined in the
attached script.The attached script also has the insert statements required
to reproduce the issue. [*Warning! *the script has "drop all objects" at
the beginning to clean up the database at the beginning]
select "companyId","name","age"
from flatten('name,age,companyId', $$select xmlreadtable(s.payroll,
'//Employee[age>30]', 'name', './name', 'age', './age' ), s.id
from company s
$$) ;
companyId
<http://localhost:8085/query.do?jsessionid=1f2e2f79260a7aa01184bf3792df6492#>
name
<http://localhost:8085/query.do?jsessionid=1f2e2f79260a7aa01184bf3792df6492#>
age
<http://localhost:8085/query.do?jsessionid=1f2e2f79260a7aa01184bf3792df6492#>
1 Lisa 35
1 Tom 40
2 Rupert 37
2 Anna 35
2 Darren 40(5 rows, 19 ms)
with xmltable as (
select "companyId","name","age"
from flatten('name,age,companyId', $$select xmlreadtable(s.payroll,
'//Employee[age>30]', 'name', './name', 'age', './age' ), s.id
from company s
$$))
select * from xmltable;
View "(
SELECT
""companyId"",
""name"",
""age""
FROM PUBLIC.FLATTEN('name,age,companyId', STRINGDECODE('select
xmlreadtable(s.payroll, ''//Employee[age>30]'', ''name'', ''./name'',
''age'', ''./age'' ), s.id \r\n from company s\r\n '))
/* function */
)" is invalid: "General error: ""java.lang.NullPointerException""; SQL
statement:
select xmlreadtable(s.payroll, '//Employee[age>30]', 'name', './name',
'age', './age' ), s.id
from company s
[50000-197]"; SQL statement:
with xmltable as (
select "companyId","name","age"
from flatten('name,age,companyId', $$select xmlreadtable(s.payroll,
'//Employee[age>30]', 'name', './name', 'age', './age' ), s.id
from company s
$$))
select * from xmltable [90109-197]
<http://localhost:8085/query.do?jsessionid=1f2e2f79260a7aa01184bf3792df6492#>
90109/90109 (Help)
<http://h2database.com/javadoc/org/h2/api/ErrorCode.html#c90109>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
-- Start with a blank slate
drop all objects;
-- DROP ALIAS XMLREADTABLE;
CREATE ALIAS XMLREADTABLE AS $$
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.h2.value.ValueResultSet;
import org.h2.tools.Csv;
import java.io.StringReader;
import org.h2.message.DbException;
import org.h2.tools.SimpleResultSet;
import java.io.*;
import java.lang.*;
import java.util.*;
import org.h2.util.JdbcUtils;
import org.xml.sax.InputSource;
@CODE
SimpleResultSet xmlreadtable(String xml, String path, String ...cols) throws Exception {
try
{
StringBuilder debug = new StringBuilder();
javax.xml.parsers.DocumentBuilderFactory factory = javax.xml.parsers.DocumentBuilderFactory.newInstance();
javax.xml.parsers.DocumentBuilder builder = factory.newDocumentBuilder();
InputSource is = new InputSource(new StringReader(xml));
org.w3c.dom.Document doc = builder.parse(is);
// Create XPathFactory object
javax.xml.xpath.XPathFactory xpathFactory = javax.xml.xpath.XPathFactory.newInstance();
// Create XPath object
javax.xml.xpath.XPath xpath = xpathFactory.newXPath();
javax.xml.xpath.XPathExpression expr = xpath.compile(path);
org.w3c.dom.NodeList nodes = (org.w3c.dom.NodeList) expr.evaluate(doc, javax.xml.xpath.XPathConstants.NODESET);
ArrayList<javax.xml.xpath.XPathExpression> xpaths = new ArrayList<javax.xml.xpath.XPathExpression>();
int idx = 0;
int columnCount = 0;
SimpleResultSet simple = new SimpleResultSet();
simple.setAutoClose(false);
for (String col : cols)
{
if (idx % 2 == 0)
{
debug.append("\ncol="+col);
simple.addColumn(col, 12, 4000, 0);
++columnCount;
}
else
{
javax.xml.xpath.XPath xpath1 = xpathFactory.newXPath();
javax.xml.xpath.XPathExpression colexpr = xpath1.compile(col);
xpaths.add(colexpr);
}
idx = (idx+1);
}
String result = new String();
for (int i = 0; i < nodes.getLength(); i++)
{
Object[] rowList = new Object[columnCount];
int colidx = 0;
debug.append("\nbegin_row");
for(javax.xml.xpath.XPathExpression xpr : xpaths)
{
org.w3c.dom.NodeList inodes = (org.w3c.dom.NodeList) xpr.evaluate(nodes.item(i).cloneNode(true), javax.xml.xpath.XPathConstants.NODESET);
if (inodes.getLength() != 0)
{
rowList[colidx] = inodes.item(0).getTextContent();
}
else
{
rowList[colidx] = new String();
}
debug.append("\nvalue="+rowList[colidx]);
++colidx;
}
simple.addRow(rowList);
debug.append("\nend_row");
}
return simple;
//return debug;
}
catch (Exception e) {
throw e;
}
}
$$;
-- drop alias flatten
CREATE ALIAS FLATTEN AS $$
import java.sql.*;
import org.h2.value.ValueResultSet;
import org.h2.tools.Csv;
import java.io.StringReader;
import org.h2.message.DbException;
import org.h2.tools.SimpleResultSet;
import java.io.*;
import java.lang.*;
import java.util.*;
import org.h2.util.JdbcUtils;
import org.xml.sax.InputSource;
@CODE
SimpleResultSet flatten(Connection conn, String columns, String sql) throws SQLException {
ResultSet rs = conn.createStatement().executeQuery(sql);
SimpleResultSet simple = new SimpleResultSet();
StringBuilder debug = new StringBuilder();
String [] colnames = columns.split(",");
for (String col : colnames)
{
simple.addColumn(col, 12, 40000, 0);
}
try {
// Load the result-set into a SimpleResultSet
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
simple.setAutoClose(false);
for (int i = 0; i < columnCount; i++) {
String name = meta.getColumnLabel(i + 1);
int sqlType = meta.getColumnType(i + 1);
int precision = meta.getPrecision(i + 1);
int scale = meta.getScale(i + 1);
simple.addColumn(name, sqlType, precision, scale);
debug.append("\ncolname="+name+" sqltype="+sqlType+" precision="+precision);
}
for (int i = 0; rs.next(); i++) {
ArrayList<ArrayList<Object>> newrows = new ArrayList<ArrayList<Object>>();
flattenRow(rs, newrows, 0, columnCount, debug);
for (ArrayList<Object> newrow : newrows)
{
Object[] rowArr = new Object[newrow.size()];
newrow.toArray(rowArr);
for (Object val : rowArr) { debug.append("\nval="+val) ;}
simple.addRow(rowArr);
debug.append("\nend row");
}
}
//rs.close();
return simple;
} catch (Exception e)
{
throw e;
//return e.getMessage() + " debug = " + debug;
}
}
static ArrayList<ArrayList<Object>> deepcopy(ArrayList<ArrayList<Object>> rows)
{
ArrayList<ArrayList<Object>> result = new ArrayList<ArrayList<Object>>();
for(ArrayList<Object> row : rows)
{
result.add((ArrayList<Object>)row.clone());
}
return result;
}
static void flattenRow(ResultSet rs, ArrayList<ArrayList<Object>> newrows, int j, int columnCount, StringBuilder debug) throws SQLException
{
if (j >= columnCount)
{
return;
}
if (newrows.size() == 0)
{
newrows.add(new ArrayList<Object>());
}
Object obj = rs.getObject(j + 1);
debug.append("obj="+obj) ;
if (obj instanceof org.h2.tools.SimpleResultSet)
{
SimpleResultSet srs = (SimpleResultSet) obj;
srs.setAutoClose(false);
debug.append("\nabout to get metadata");
ResultSetMetaData srsmeta = srs.getMetaData();
debug.append("\ngot metadata");
int srscolumnCount = srsmeta.getColumnCount();
debug.append("\nsrs column count="+srscolumnCount);
boolean firstRow = true;
ArrayList<ArrayList<Object>> preexistingRowsMasterCopy = deepcopy(newrows);
while(srs.next())
{
if (firstRow)
{
for (int k = 0; k < srscolumnCount; ++k)
{
for (ArrayList<Object> row : newrows)
{
row.add(srs.getObject(k+1));
}
}
}
else
{
ArrayList<ArrayList<Object>> newadditionalrows = deepcopy(preexistingRowsMasterCopy);
for (int k = 0; k < srscolumnCount; ++k)
{
for (ArrayList<Object> additionalrow : newadditionalrows)
{
additionalrow.add(srs.getObject(k+1));
}
}
for (ArrayList<Object> additionalrow : newadditionalrows)
{
newrows.add((ArrayList<Object>)additionalrow);
}
}
firstRow = false;
}
}
else if (obj instanceof Clob)
{
Clob clob = (Clob)obj;
String clobString = clob.getSubString (1, (int) clob.length ());
for (ArrayList<Object> row : newrows)
{
row.add(clobString);
}
}
else
{
for (ArrayList<Object> row : newrows)
{
row.add(obj == null? null: obj.toString());
}
}
flattenRow(rs, newrows, j+1, columnCount,debug);
}
$$;
create table company
(
id NUMBER,
payroll CLOB
);
insert into company(id,payroll) values (1, '<Employees>
<Employee id="1">
<age>29</age>
<name>Pankaj</name>
<gender>Male</gender>
<role>Java Developer</role>
</Employee>
<Employee id="2">
<age>35</age>
<name>Lisa</name>
<gender>Female</gender>
<role>CEO</role>
</Employee>
<Employee id="3">
<age>40</age>
<name>Tom</name>
<gender>Male</gender>
<role>Manager</role>
</Employee>
<Employee id="4">
<age>25</age>
<name>Meghan</name>
<gender>Female</gender>
<role>Manager</role>
</Employee>
</Employees>
');
insert into company(id,payroll) values (2, '
<Employees>
<Employee id="1">
<age>37</age>
<name>Rupert</name>
<gender>Male</gender>
<role>Java Developer</role>
</Employee>
<Employee id="2">
<age>35</age>
<name>Anna</name>
<gender>Female</gender>
<role>CEO</role>
</Employee>
<Employee id="3">
<age>40</age>
<name>Darren</name>
<gender>Male</gender>
<role>Front End Developer</role>
</Employee>
<Employee id="4">
<age>25</age>
<name>Kimberley</name>
<gender>Female</gender>
<role>Customer Support</role>
</Employee>
</Employees>
');
select xmlreadtable(s.payroll, '//Employee', 'name', './name', 'age', './age' ), s.id
from company s;
select "companyId","name","age"
from flatten('name,age,companyId', $$select xmlreadtable(s.payroll, '//Employee', 'name', './name', 'age', './age' ), s.id
from company s
$$) ;
select "companyId","name","age"
from flatten('name,age,companyId', $$select xmlreadtable(s.payroll, '//Employee[age>30]', 'name', './name', 'age', './age' ), s.id
from company s
$$) ;
with xmltable as (
select "companyId","name","age"
from flatten('name,age,companyId', $$select xmlreadtable(s.payroll, '//Employee[age>30]', 'name', './name', 'age', './age' ), s.id
from company s
$$))
select * from xmltable;
