Bug 46198

Summary:	SQL: NULL not accepted in place of column name in SELECT statement
Product:	LibreOffice	Reporter:	Jason S <jmsachs>
Component:	Base	Assignee:	Lionel Elie Mamane <lionel>
Status:	RESOLVED FIXED
Severity:	normal	CC:	LibreOffice, lionel
Priority:	medium
Version:	unspecified
Hardware:	All
OS:	All
Whiteboard:	target:3.6.0 target:3.5.1
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	46207, 46206

Description Jason S 2012-02-16 15:41:12 UTC

http://stackoverflow.com/questions/4770466/sql-dialect-in-openoffice-base/9315951#9315951

I'm familiar with SQL in SQLite and MySQL, but OpenOffice Base seems to be either very crippled, or I don't understand how to execute raw SQL.

I want to do (effectively) this:

INSERT INTO t2 SELECT NULL as id, t.foo, t.bar, '' as baz, 0 as quux 
  FROM MyTable t

All I can do is

SELECT t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t

because the NULL seems to give Base confusion, as does the INSERT INTO T2 SELECT ... syntax.

Can anyone suggest how I need to fix this?

Comment 1 Lionel Elie Mamane 2012-02-16 19:45:02 UTC

Indeed, NULL is not accepted by the LibreOffice SQL parser in place of a column name in a SELECT statement. That's a bug. I'll try to get it fixed for 3.5.1, else for 3.5.2.

In the meantime, I think this syntax should work:

INSERT INTO t2 (foo, bar, baz, quux) SELECT t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t

Where "(foo, bar, baz, quux)" refers to column names in the t2 table (in case they differ from the names in the SELECT statement).

Comment 2 Lionel Elie Mamane 2012-02-16 20:51:31 UTC

(In reply to comment #1)

> In the meantime, I think this syntax should work:
> 
> INSERT INTO t2 (foo, bar, baz, quux) SELECT t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t

Ah no, "INSERT INTO (...) SELECT" is not supported at all. I had missed that you have *two* problems in my original reading. Cloned the bug for the second issue.

Comment 3 Not Assigned 2012-02-17 01:29:50 UTC

Lionel Elie Mamane commited a patch related to this issue to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f3ebfc4c78da67f72befa3bde55dde4d77d5e3a

fdo#46198: NULL is a perfectly fine general_value

Comment 4 Drew Jensen 2012-02-18 11:06:26 UTC

EMPTY is the string recognized by the the built in parser - good to see it finally also recognizes NULL for those back ends that support it.

Comment 5 Not Assigned 2012-02-21 06:18:47 UTC

Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3a61c9f1c51893b242ccf5bb20163b72721a2bf0&g=libreoffice-3-5

fdo#46198: NULL is a perfectly fine general_value


It will be available in LibreOffice 3.5.1.

Comment 6 Rainer Bielefeld Retired 2012-03-24 09:52:43 UTC

Fix is in 3.5, 3.6, there will be no 3.4.7, so I closed tis one. Please feel free to reopen if I did wrong.