emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* Bug: babel sql: no results are returned if the first would be NULL [7.5]
@ 2011-04-05 20:03 Myles English
  0 siblings, 0 replies; only message in thread
From: Myles English @ 2011-04-05 20:03 UTC (permalink / raw)
  To: emacs-orgmode

Hello,

When returning results from a postgresql database, if the first value is
NULL then no results are returned.

Versions: orgmode git commit c01c2ad Fri Mar 18, postgresql 9.0.3.

This works as expected:

#+begin_src sql :engine postgresql :cmdline -d postgres
SELECT 1 as A, NULL as B;
#+end_src

#+results:
| a | b |
| 1 |   |

#+begin_src sql :engine postgresql :cmdline -d postgres
SELECT 1 as A, 1 as B;
#+end_src

#+results:
| a | b |
| 1 | 1 |

When the first value is NULL then only the column names are returned:

#+begin_src sql :engine postgresql :cmdline -d postgres
SELECT NULL as A,1 as B;
#+end_src

#+results:
| a | b |
|---+---|

whereas I would have expected this:

#+begin_example
| a | b |
|   | 1 |
#+end_example

A slightly unsatisfactory workaround using coalesce, is:

#+begin_src sql :engine postgresql :cmdline -d postgres
SELECT coalesce(NULL,'NULL') as A,1 as B;
#+end_src

#+results:
| a    | b |
| NULL | 1 |

Also, this may be unrelated, when trying to return tuples only, using
"-t", this happens:

#+begin_src sql :engine postgresql :cmdline -d postgres -t
SELECT NULL as A,1 as B;
#+end_src

#+results:
|   |   |   |   |   |
|---+---+---+---+---|
|   |   |   |   |   |

Thanks,

Myles

Emacs  : GNU Emacs 23.3.1 (x86_64-unknown-linux-gnu, GTK+ Version
2.22.1) of 2011-03-10 on bitzer.hoetzel.info
Package: Org-mode version 7.5

current state:
==============
(setq
 org-log-done 'time
 org-export-preprocess-before-backend-specifics-hook
'(org-inlinetask-export-handler) org-clock-in-switch-to-state
'bh/clock-in-to-next org-agenda-insert-diary-extract-time t
 org-export-latex-after-initial-vars-hook
'(org-beamer-after-initial-vars) org-todo-keyword-faces
'(("TODO" :foreground "red" :weight bold) ("NEXT" :foreground
"blue" :weight bold) ("WAITING" :foreground "yellow" :weight bold)
("DONE" :foreground "forest green" :weight bold) ("MAYBE" :foreground
"goldenrod" :weight bold) ("CANCELLED" :foreground "orangered" :weight
bold)) org-speed-command-hook '(org-speed-command-default-hook
org-babel-speed-command-hook) org-agenda-custom-commands '(("w" "Tasks
waiting on something" tags "waiting/!" ((org-use-tag-inheritance nil)
(org-agenda-todo-ignore-scheduled nil)
(org-agenda-todo-ignore-deadlines nil)
(org-agenda-todo-ignore-with-date nil) (org-agenda-overriding-header
"Waiting tasks")) ) ("r" "Refile New Notes and Tasks" tags
"LEVEL=2+refile" ((org-agenda-todo-ignore-with-date nil)
(org-agenda-todo-ignore-deadlines nil)
(org-agenda-todo-ignore-scheduled nil) (org-agenda-overriding-header
"Refilable tasks")) )
			      ("N" "Notes" tags
"note" ((org-agenda-overriding-header "Notes"))) ("n" "Next" tags-todo
"-WAITING-CANCELLED/!NEXT" ((org-agenda-overriding-header "Next
Tasks"))) ("u" "Unscheduled Next" tags-todo
"-WAITING-CANCELLED-SCHEDULED={.}/!NEXT" ((org-agenda-overriding-header
"Unscheduled NEXT items: "))) ("p" "Projects" tags-todo
			       "LEVEL=2-refile|LEVEL=1+refile/!-DONE-CANCELLED-WAITING-MAYBE"
			       ((org-agenda-skip-function (quote
bh/skip-non-projects)) (org-agenda-overriding-header "Projects"))
			       )
			      ("o" "Other (Non-Project) tasks" tags-todo
			       "LEVEL=2-refile|LEVEL=1+refile/!-DONE-CANCELLED-WAITING-MAYBE"
			       ((org-agenda-skip-function (quote
bh/skip-projects)) (org-agenda-overriding-header "Other non-project
tasks")) )
			      ("A" "Archivable tasks" tags
"LEVEL=2-refile/DONE|CANCELLED" ((org-agenda-overriding-header "Tasks
to Archive"))) ("h" "Habits" tags-todo "STYLE=\"habit\""
			       ((org-agenda-todo-ignore-with-date nil)
(org-agenda-todo-ignore-scheduled nil)
(org-agenda-todo-ignore-deadlines nil) (org-agenda-overriding-header
"Habits")) ) ("#" "Stuck Projects" tags-todo
"LEVEL=2-refile|LEVEL=1+refile/!-DONE-CANCELLED" ((org-agenda-skip-function
(quote bh/skip-non-stuck-projects)) (org-agenda-overriding-header
"Stuck projects")) )
			      ("c" "Select default clocking task" tags
"LEVEL=2-refile" ((org-agenda-skip-function
				 (quote (org-agenda-skip-subtree-if
(quote notregexp) "^\\*\\* Organisation")))
(org-agenda-overriding-header "Set default clocking task with C-u C-u
I")) ) )
 org-agenda-files '("~/org/gtd.org" "~/org/phd.org" "~/org/rh.org"
"~/org/home.org" "~/org/notes/refile.org" "~/org/diary.org"
"~/org/thesisPlan/thesis_plan.org"
"~/docs/relKpaper/org/model_runs.org"
"~/org/papers/shm/linked_nodes.org" "~/org/papers/prov/prov.org")
org-agenda-include-diary t org-babel-load-languages '((R . t)
(emacs-lisp . t) (org . t) (sql . t) (python . t) (sh . t) (latex . t)
(ledger . t)) org-clock-into-drawer "CLOCK" org-metaup-hook
'(org-babel-load-in-session-maybe)
org-export-preprocess-after-blockquote-hook
'(org-special-blocks-make-special-cookies)
org-after-todo-state-change-hook '(org-clock-out-if-current)
org-babel-tangle-lang-exts '(("latex" . "tex") ("python" . "py")
("emacs-lisp" . "el")) org-clock-history-length 28
org-todo-state-tags-triggers '(("CANCELLED" ("cancelled" . t))
("WAITING" ("waiting" . t)) ("MAYBE" ("waiting" . t)) (done
("waiting")) ("TODO" ("waiting") ("cancelled")) ("NEXT" ("waiting"))
("DONE" ("waiting") ("cancelled"))) outline-minor-mode-prefix "\x03\x0f"
org-export-html-after-blockquotes-hook
'(org-special-blocks-convert-html-special-cookies)
org-export-blocks-postblock-hook '(org-exp-res/src-name-cleanup)
org-habit-preceding-days 0 org-export-latex-format-toc-function
'org-export-latex-format-toc-default org-stuck-projects
'("+project/-MAYBE-DONE" ("TODO" "STARTED") nil "\\<IGNORE\\>")
org-export-latex-after-blockquotes-hook
'(org-special-blocks-convert-latex-special-cookies) org-clock-in-resume
t org-agenda-diary-file "~/org/diary.org" org-tab-first-hook
'(yas/org-very-safe-expand org-hide-block-toggle-maybe
org-src-native-tab-command-maybe org-babel-hide-result-toggle-maybe)
org-src-mode-hook '(org-src-babel-configure-edit-buffer
org-src-mode-configure-edit-buffer) org-confirm-shell-link-function
'yes-or-no-p org-export-first-hook
'(org-beamer-initialize-open-trackers) org-clock-persist 'history
org-todo-keywords '((sequence "TODO(t!)" "NEXT(n!)" "|" "DONE(d!/!)")
(sequence "WAITING(w@/!)" "MAYBE(m!)" "|" "CANCELLED(c@/!)"))
org-agenda-before-write-hook '(org-agenda-add-entry-text)
org-default-notes-file "~/org//notes.org" org-directory "~/org/"
org-inlinetask-export nil org-babel-pre-tangle-hook '(save-buffer)
org-cycle-hook '(org-cycle-hide-archived-subtrees
org-cycle-hide-drawers org-cycle-show-empty-lines
org-optimize-window-after-visibility-change) org-export-latex-classes
'(("article"
"\\documentclass[12pt,a4paper]{article}\n\\usepackage[utf8]{inputenc}\n\\usepackage[T1]{fontenc}\n\\usepackage{graphicx}\n\\usepackage{longtable}\n\\usepackage{hyperref}" ("\\section{%s}" .
"\\section*{%s}") ("\\subsection{%s}" . "\\subsection*{%s}")
("\\subsubsection{%s}" . "\\subsubsection*{%s}") ("\\paragraph{%s}" .
"\\paragraph*{%s}") ("\\subparagraph{%s}" . "\\subparagraph*{%s}"))
("report"
"\\documentclass[11pt]{report}\n\\usepackage[utf8]{inputenc}\n\\usepackage[T1]{fontenc}\n\\usepackage{graphicx}\n\\usepackage{longtable}\n\\usepackage{hyperref}" ("\\part{%s}" .
"\\part*{%s}") ("\\chapter{%s}" . "\\chapter*{%s}") ("\\section{%s}" .
"\\section*{%s}") ("\\subsection{%s}" . "\\subsection*{%s}")
("\\subsubsection{%s}" . "\\subsubsection*{%s}")) ("book"
			     "\\documentclass[11pt]{book}\n\\usepackage[utf8]{inputenc}\n\\usepackage[T1]{fontenc}\n\\usepackage{graphicx}\n\\usepackage{longtable}\n\\usepackage{hyperref}"
			     ("\\part{%s}" . "\\part*{%s}")
("\\chapter{%s}" . "\\chapter*{%s}") ("\\section{%s}" .
"\\section*{%s}") ("\\subsection{%s}" . "\\subsection*{%s}")
("\\subsubsection{%s}" . "\\subsubsection*{%s}")) )
 org-publish-project-alist '(("orgfiles" :base-directory
"~/org/notes/html/" :recursive t :base-extension
"org" :publishing-directory
"~/docs/html/currentResearch/" :publishing-function
org-publish-org-to-html :headline-levels 3 :auto-preamble
t :auto-postamble t) ("images1" :base-directory
"~/org/RR/1D_horiz/img/" :recursive nil :base-extension
"png" :publishing-directory
"~/docs/html/currentResearch/1D_horiz/img/" :publishing-function
org-publish-attachment) ("images2" :base-directory
"~/org/RR/richards_M/img/" :recursive nil :base-extension
"png" :publishing-directory
"~/docs/html/currentResearch/richards_M/img/" :publishing-function
org-publish-attachment) ("images3" :base-directory
"~/org/RR/flux/img/" :recursive nil :base-extension
"png" :publishing-directory
"~/docs/html/currentResearch/flux/img/" :publishing-function
org-publish-attachment) ("images4" :base-directory
"~/org/RR/1D_horiz_dev/img/" :recursive nil :base-extension
"png" :publishing-directory
"~/docs/html/currentResearch/1D_horiz_dev/img/" :publishing-function
org-publish-attachment) ("images5" :base-directory
"~/org/RR/linkbm/img/" :recursive nil :base-extension
"png" :publishing-directory
"~/docs/html/currentResearch/linkbm/img/" :publishing-function
org-publish-attachment) ("website" :components ("orgfiles" "images1"
"images2" "images3" "images4" "images5")) )
org-export-preprocess-before-normalizing-links-hook
'(org-remove-file-link-modifiers) org-mode-hook '((lambda nil (if
(member "REFTEX" org-todo-keywords-1) (org-mode-article-modes)))
(lambda nil (make-variable-buffer-local (quote yas/trigger-key)) (setq
yas/trigger-key [tab]) (add-to-list (quote org-tab-first-hook) (quote
yas/org-very-safe-expand)) (define-key yas/keymap [tab] (quote
yas/next-field))) (lambda nil (flyspell-mode 1)) org-clock-load
turn-on-font-lock #[nil "\300\301\302\303\304$\207" [org-add-hook
change-major-mode-hook org-show-block-all append local] 5] #[nil
"\300\301\302\303\304$\207" [org-add-hook change-major-mode-hook
org-babel-show-result-all append local] 5] org-babel-result-hide-spec
org-babel-hide-all-hashes) org-clock-out-remove-zero-time-clocks t
org-font-lock-hook '(org-inlinetask-fontify) org-global-properties
'(("Effort_ALL" . "0:10 0:30 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00"))
org-refile-targets '((org-agenda-files :maxlevel . 5) (nil :maxlevel .
5) ("~/org/notes/allnotes.org" :maxlevel . 5)) org-ctrl-c-ctrl-c-hook
'(org-babel-hash-at-point org-babel-execute-safely-maybe)
org-confirm-elisp-link-function 'yes-or-no-p
org-refile-use-outline-path 'file org-agenda-mode-hook '((lambda nil
(hl-line-mode 1))) org-export-interblocks '((lob
org-babel-exp-lob-one-liners) (src org-babel-exp-inline-src-blocks))
org-clock-out-hook '(bh/clock-out-maybe)
org-refile-allow-creating-parent-nodes 'confirm org-occur-hook
'(org-first-headline-recenter) org-from-is-user-regexp nil
org-habit-following-days 0 org-table-use-standard-references '(quote
from) org-drawers '("PROPERTIES" "LOGBOOK" "CLOCK")
org-export-preprocess-before-selecting-backend-code-hook
'(org-beamer-select-beamer-code) org-confirm-babel-evaluate nil
org-modules '(org-bbdb org-bibtex org-docview org-gnus org-info
org-jsinfo org-habit org-inlinetask org-irc org-mew org-mhe org-rmail
org-special-blocks org-vm org-wl org-w3m) org-columns-default-format
"%80ITEM(Task) %10Effort(Effort){:} %10CLOCKSUM"
org-clock-report-include-clocking-task t org-export-latex-final-hook
'(org-beamer-amend-header org-beamer-fix-toc
org-beamer-auto-fragile-frames
org-beamer-place-default-actions-for-lists) org-metadown-hook
'(org-babel-pop-to-session-maybe) org-export-blocks '((src
org-babel-exp-src-block nil) (comment org-export-blocks-format-comment
t) (ditaa org-export-blocks-format-ditaa nil) (dot
org-export-blocks-format-dot nil)) )

^ permalink raw reply	[flat|nested] only message in thread

only message in thread, other threads:[~2011-04-05 20:02 UTC | newest]

Thread overview: (only message) (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2011-04-05 20:03 Bug: babel sql: no results are returned if the first would be NULL [7.5] Myles English

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).